I have four tables - A,B,C,D. Each table has 1 column: ID.
Data:
Table A = 1,2,3,4 Table B = 1,2,4,5 Table C = 2,3,4,5 Table D = 1,3,5,7 I need help in understanding the output of this SQL query:
select d.*, c.*, b.*,a.* from d left join c on d.id = c.id right join b on b.id = c.id full outer join a on a.id = b.id; I am very clear till the left join, but after that when the subsequent joins are applied, I do not understand how the result changes.
As per @Pieter's answer, we can work systematically through this:
Taking just the first LEFT JOIN:
SELECT D.ID AS D, c.ID AS C from d left join c on d.id = c.id All of Ds rows are returned. NULLS are present for failed joins on C:
D C 1 NULL 3 3 5 5 7 NULL Then, adding the right join to B:
SELECT D.ID AS D, c.ID AS C, b.ID AS B from d left join c on d.id = c.id right join b on b.id = c.id All of Bs rows are returned, with both C and D being NULL where the join fails. Only 5 is common to D, C and B.
D C B NULL NULL 1 NULL NULL 2 NULL NULL 4 5 5 5 Finally, the FULL OUTER JOIN back to A will add missing rows from either side of the JOIN.
This means the '3' from A not present in B is added back, with NULLs for the other columns
D C B A NULL NULL 1 1 NULL NULL 2 2 NULL NULL 4 4 5 5 5 NULL NULL NULL NULL 3 
Imagine it as a SQL stack machine. Push tables onto the stack as they are encountered, left-to-right, in the FROM clause and perform the join on the two top-most tables as ON clauses are encountered. The result of each join is pushed onto the stack also as it is generated.
