LEFT OUTER JOIN behaves like INNER JOIN

Question

I have 3 tables: T1, T2, T3

I need to have all rows from T1 in result and if there are matching records in T2 and T3, then output them also.

So my query looks like

SELECT *
FROM T1 
LEFT JOIN T2
  ON T1.Id = T2.Id
INNER JOIN T3
  ON T2.SecondId = T3.Id

But in result, I receive only records that have some records in T3. Is it the same behavior as I would write

SELECT *
FROM T1 
LEFT JOIN T2
  ON T1.Id = T2.Id
WHERE T2.Value = 4

where LEFT JOIN behaves like INNER JOIN because of WHERE clause?

Yes, you got it. The condition needs to go in the `ON` clause. — Gordon Linoff, May 14 '20 at 13:44
An `outer join` returns rows from one side, left or right, that have no matching row(s) on the other side. The `on` clause defines the matching criteria and allows for NULLs. You can use a `where` clause, but you need to code it to correctly handle NULLs, e.g. `... where T2.Value is NULL or T2.Value = 4;` in order for it to work as intended. — HABO, May 14 '20 at 13:47
@GordonLinoff so I need to use LEFT JOIN twice to get what I want, right? — demo, May 14 '20 at 14:07
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 14 '20 at 23:12

score 0 · Accepted Answer · answered May 14 '20 at 14:10

The syntax for what you probably want is:

SELECT *
FROM T1 LEFT JOIN
     T2
     ON T1.Id = T2.Id LEFT JOIN
     T3
     ON T2.SecondId = T3.Id

Without the second LEFT JOIN, the INNER JOIN filters out non-matches. That is because T2.SecondId will be NULL for those non-matches. This is the right approach; checking explicitly for NULLs has some edge cases that don't work.

For the second query:

SELECT *
FROM T1 LEFT JOIN
     T2
     ON T1.Id = T2.Id AND T2.Value = 4

LEFT OUTER JOIN behaves like INNER JOIN

1 Answers1