CODEWITHSUNDEEP
sqljoin
Sukhhhh
Sukhhhh

Reputation: 1039

Left outer joins with join condition

At times, the following statement is confusing, at least to me, though I have been working with joins for a long time.

The part which is confusing here is, considering second left join 

SELECT * FROM table1  
    LEFT JOIN table2 ON table1.id=table2.id  
    LEFT JOIN table3 ON table2.id=table3.id;

  1. Which will be considered as left table - either table1 or table2 (table2 is involved in join condition).

  2. If I recall correctly, left join result involves matched results from join condition and unmatched results from left table. If left table is table1 (from point 1.), what will be unmatched rows from table1, as it isn't involved in the join condition.

Upvotes: 2

Views: 309

Answers (2)

rsbarro
rsbarro

Reputation: 27339

The query will return all of the rows that are in Table1. It will join the rows from Table2 only where the id values are equal between Table1 and Table2. It will also join the rows from Table3 where the id values are equal between the result of the join of (Table1 AND Table2) and Table3. So if the first join between Table1 and Table2 has produced a NULL result (no equivalent id value in Table2), then the second join will also produce a NULL result. For example (using SQL Server):

DECLARE @Table1 TABLE([ID] INT, [Value] VARCHAR(20))
INSERT INTO @Table1 VALUES(1, 'Table 1 ID 1')
INSERT INTO @Table1 VALUES(2, 'Table 1 ID 2')
INSERT INTO @Table1 VALUES(3, 'Table 1 ID 3')

DECLARE @Table2 TABLE([ID] INT, [Value] VARCHAR(20))
INSERT INTO @Table2 VALUES(1, 'Table 2 ID 1')
INSERT INTO @Table2 VALUES(3, 'Table 2 ID 3')
INSERT INTO @Table2 VALUES(5, 'Table 2 ID 5')

DECLARE @Table3 TABLE([ID] INT, [Value] VARCHAR(20))
INSERT INTO @Table3 VALUES(2, 'Table 3 ID 2')
INSERT INTO @Table3 VALUES(3, 'Table 3 ID 3')
INSERT INTO @Table3 VALUES(5, 'Table 3 ID 5')

SELECT * 
FROM @Table1 T1
LEFT JOIN @Table2 T2 ON T1.ID = T2.ID
LEFT JOIN @Table3 T3 ON T2.ID = T3.ID

This query will produce the following results:

ID  Value          ID   Value           ID    Value
-----------------------------------------------------------
1   Table 1 ID 1   1    Table 2 ID 1    NULL  NULL
2   Table 1 ID 2   NULL NULL            NULL  NULL
3   Table 1 ID 3   3    Table 2 ID 3    3     Table 3 ID 3

To answer your question, Table1 is involved on the second join because the second join will only contain rows from Table2 that were able to be joined with Table1. In other words, the second join isn't Table2 to Table3, it's the result of the left join of Table1 and Table2 to Table3. So in my example, even though Table2 and Table3 both have records with an ID of 5, those records are not included in the result set because Table1 does not have a record with an ID of 5.

Upvotes: 1

Pencho Ilchev
Pencho Ilchev

Reputation: 3241

  1. The result of joining table1 and table2 is the left table in the second join.
  2. If you have id 1 in table1 and no id 1 in table2 but table3 contains id 1 then the row for id 1 will be like:

table1.id  table2.id  table3.id
1          NULL       NULL

Joins are computed in the order of declaration. This means that you are joining table3 to the result of table1 and table2.

Upvotes: 2

Related Questions