FULL OUTER JOIN value condition

Question

I need to add value condition to the FULL OUTER JOIN.

I.e. I'm triyng to do this:

SELECT *
FROM Table1
FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5

But this script doesn't work. Actually it looks like the condition (Table2.Field2 > 5) was never applied at all.

The same issue appears for the RIGHT OUTER JOIN, so I think the reason is that when there is RIGHT or FULL join no value conditions applied to the right table in join.
Why is this happening? Is there a conceptual explanation of such behaviour?

And of course the main question is how to solve this issue.

Is there a way to solve this without using subqueries?

SELECT *
FROM Table1
FULL OUTER JOIN (SELECT * FROM Table2 WHERE Table2.Field2 > 5) AS t2 ON Table1.Field1 = t2.Field1

score 3 · Answer 1 · answered Jan 18 '12 at 09:06

3

What you want might be reformulated as:

 SELECT *
   FROM Table1
   LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5
  UNION ALL
 SELECT *
   FROM Table1
  RIGHT JOIN Table2 ON Table1.Field1 = Table2.Field1
  WHERE Table2.Field2 > 5
    AND Table1.Field1 IS NULL

but using subquery as suggested by yourself is IMO the best option.

answered Jan 18 '12 at 09:06

Benoit

76,634
23
210
236

Thanks for the suggestion but this is not a good solution for me. It will work you're right but this is even more complex then simple subquery. – SergeyT Jan 18 '12 at 09:09
2

@SergeyT: You asked without subqueries, I answer without subqueries. Your solution is probably the best option. Depending on the RDBMS you can factor it in a WITH clause. – Benoit Jan 18 '12 at 09:12

score 2 · Answer 2 · answered May 30 '16 at 04:06

This used to confuse me. Now I get it! The conditions after "on" (in your case:Table1.Field1 = Table2.Field1 AND Table2.Field2 > 5 ) tells the join operator which rows from the two tables are joined. This means when and only when row1 from table1 and row2 from tables satisfy both row1.field1 = row2.field2 and row2.field2 > 5 that row1 and row2 are joined. The rest rows are not joined.

So in full outer join, the result set would then be joined rows, non-joined rows from table1 and non-joined rows from table2. In right join, the result set would be joined rows, non-joined rows from table2. In either case, rows from table 2 with field2 <= 5 will be among the result sets of non-joined rows from table2.

This is why "Table2.Field2 > 5" is working properly in left join but not that "properly" in right or full join, but the value conditions do be doing their jobs right.

Lieven Keersmaekers · Accepted Answer · 2012-01-18T10:18:32.920

Pretty convulated but no subqueries

SELECT  Table1.*
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field1 ELSE NULL END
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field2 ELSE NULL END        
FROM    Table1
        FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE   COALESCE(Table2.Field2, 6) > 5
        OR Table1.Field1 = Table2.Field1

Test script

;WITH Table1 AS (
  SELECT * FROM (VALUES
    (1, 1)
    , (2, 2)
    , (5, 5)
    , (6, 6)
  ) AS Table1 (Field1, Field2)
)
, Table2 AS (
  SELECT * FROM (VALUES
    (1, 1)
    , (3, 3)
    , (4, 4)
    , (5, 5)
    , (7, 7)
  ) AS Table2 (Field1, Field2)
)
SELECT  Table1.*
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field1 ELSE NULL END
        , CASE WHEN Table2.Field2 > 5 THEN Table2.Field2 ELSE NULL END        
FROM    Table1
        FULL OUTER JOIN Table2 ON Table1.Field1 = Table2.Field1
WHERE   COALESCE(Table2.Field2, 6) > 5
        OR Table1.Field1 = Table2.Field1

Results

 Field1 Field2 Field1 Field2
 1      1      NULL   NULL
 5      5      NULL   NULL
 NULL   NULL   7      7
 6      6      NULL   NULL
 1      1      NULL   NULL
 2      2      NULL   NULL

Thanks, but it is still not the solution I was looking for. Though this approach saves query structure (critical for me because SQL statement is generating automatically), so it even more appropriate solution then previous one. Still a lot of extra code, so maybe I was have to implement subquery solution in the end. But this solution is good enough, so I think I can accept it as answer. — SergeyT, Jan 18 '12 at 10:35

FULL OUTER JOIN value condition

3 Answers3

Linked