0

I know it's a fundamental question but again why something like this happening is hard for me to grasp. Let's say we have 3 tables (A,B,C) and they are all has 1-1 relationships. But table B does not contain all values which table A contains. So between them there has to be a left join. So why does below code turns all joins into an inner join and the only inner join exists does not work only between B and C tables? Could you explain it to me?

SELECT A.*
FROM A
LEFT OUTER JOIN B ON B.ID = A.ID
INNER JOIN C ON C.ID = B.ID

This question is not about what outer joins or inner joins can or can not do with values in our tables. It is about while they were used together how sql engine works on them ? Maybe What is the order of execution in here? This is what i was trying to understand. Thank you.

ismetguzelgun
  • 1,090
  • 8
  • 16
  • Why shouldn't it? If you don't tell us, how are we supposed to answer other than by "because that's how SQL works"? PS Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy Oct 10 '19 at 00:05
  • Possible duplicate of [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) – philipxy Oct 10 '19 at 00:05
  • Even this "because that's how SQL works" answer would have helped. Thank you for your consideration. – ismetguzelgun Oct 10 '19 at 08:27
  • All you need to do is *read a definition of the operators you are calling*, you don't need anyone else to do that, you just have to *make the effort*, and that falls under "research effort" that should be made before considering posting a question. Also "What is the order of execution in here?" is an easily found faq. It is obvioulsy an easily found faq. And obviously one should not write expressions without first finding out what they mean. All you had to do was google it. See my next comments for more of my standard comments. Good luck. – philipxy Oct 10 '19 at 08:44
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS/product & DDL, which includes constraints & indexes & tabular formatted initialization. – philipxy Oct 10 '19 at 08:44
  • "What is the order of execution in here?" is a faq. Before considering posting please always google your 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. See [ask] & the voting arrow mouseover texts. – philipxy Oct 10 '19 at 08:45

4 Answers4

2

The issue here is to do with understanding the order in which the JOINs are processed.

The LEFT OUTER JOIN is executed first and will return all results from A as expected, and those matching results from B (with NULL values where the join fails).

Following this, the INNER JOIN to C is then executed against the combined results from the first LEFT OUTER JOIN. As there are no matches joining the non-existing matches from B to C, these are excluded from the results, giving you the cut down result.

A typical way of catering for this circumstance is to use a second LEFT OUTER JOIN in place of the INNER JOIN:

SELECT *
  FROM A
    LEFT OUTER JOIN B ON B.ID = A.ID
    LEFT OUTER JOIN C ON C.ID = B.ID

This will still return everything from A and only matching records from B and C.

If the intention is to return only results in B that have a corresponding entry in C then you can modify the way your query works to something like this untested SQL:

SELECT *
  FROM A
    LEFT OUTER JOIN (
                    SELECT B.ID
                      FROM B
                       INNER JOIN C ON C.ID = B.ID
                    ) btmp ON btmp.ID = A.ID

This will only return results in B that match a row in C before joining it to A.

Martin
  • 16,093
  • 1
  • 29
  • 48
  • 1
    Thank you Mr. Martin. I did go to a solution like you said while i was writing script. Then again i needed to know what was the problem that pushed me to write my script this way. – ismetguzelgun Oct 09 '19 at 12:33
2

Simply put, if no match was found in table B then this:

INNER JOIN C ON C.ID = B.ID

would mean:

INNER JOIN C ON C.ID = NULL

And the join condition fails.

You can add parenthesis to get the expected result:

SELECT A.*
FROM A
LEFT OUTER JOIN (B INNER JOIN C ON B.ID = C.ID) ON A.ID = B.ID
Salman A
  • 262,204
  • 82
  • 430
  • 521
1

A left outer join B means return all from A, even those that have no match in B, right? So you would get every row from A, with a NULL for the ones that have no corresponding in B and with values for those who do have a match in B.

This set is then joined (inner joined) with C. This means only the matching ones between (A left join B) and C and the join condition is on a column of B => only the ones that actually had a matching value with A in the first place will show up. The others are NULL.

This article is an excellent visual explanation of joins in SQL.

Rigerta
  • 3,959
  • 15
  • 26
  • Thank you for your answer Rigerta. Although i have been a huge fan of codinghorror blog i realised now that did not read that article. – ismetguzelgun Oct 09 '19 at 12:34
  • @philipxy i am really grateful for what you are trying to achieve in here. And thank you Mr. Although i am a veteran reader of stackoverflow, seems like i am neither a good contributor nor a good student. I will pay regard to your concerns about how i posted. – ismetguzelgun Oct 10 '19 at 09:21
0

Compare these two queries (first query might look incorrect but it's correct one!)

SELECT A.* FROM A 
    LEFT OUTER JOIN B  
        INNER JOIN C ON C.ID = B.ID
    ON B.ID = A.ID

vs

SELECT * FROM A 
    LEFT OUTER JOIN B ON B.ID = A.ID 
    INNER JOIN C ON C.ID = B.ID

In second query you are asking: do left join b to a and do inner join of c to the result of previous step.

The last inner join will omit every null record

But the first query is different It's asking: Do inner join C to B and do left join of the result to A

In this case all records from A non-corresponding to the inner join result will be included into result.

Alexey Vlasov
  • 355
  • 2
  • 5