Search code examples
sql-serverleft-joinright-join

SQL: result of left join left join left join left/right join

When you have 3 o more tables, for example, table1 [1a,1b], table2[2a,2b] and table3 [3a,3b]

If I have:

SELECT table1.id , table2.id, table3.id 

FROM table1
LEFT JOIN table2 on table1.id = table2.t1_id
LEFT/RIGHT JOIN table3 on table2.id = table3.t2_id

As result, first I'll have every table1.id

1a
1b

Then all the tuples where table2 matches table1, and the tuples from table1 that don't have a match in table2 are empty in table2.id.

1a   2a
1a   2b
1a
1b   2b

Asumming those are the matches*

Here tell me if I'm getting this wrong or not

AND THEN, what do I have with a Left join and with a right join with the table3? :/

EDIT: I forgot, 3a will match with 2a and 2b and 3b with neither.

Solution

  • It is called a FULL OUTER join in SQL Server and your result set would look like this

    1a   2a  3a
1a   2b  3a
1a
1b   2b  
         3b

    Edited to match your table 3 data.