-1

I have a question about using three joins between 3 tables. Changing the type of JOIN between second and third tables, the result changes and I don't understand why.

TABLE1 NAME AND AGE

NAME AGE
BILL 20
JOHN 25
MARTHA 34
STEVE 40
EMILY 30

TABLE2 NAME AND ID

NAME ID
BILL 001S
JOHN 002B
MARTHA 001Z

TABLE3 ID AND HOBBIES

ID HOBBIES
001S FOOTBALL
002B MUSIC
001Z VOLLEYBALL

EXAMPLE1

SELECT NAME, HOBBIES
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.NAME = TABLE2.NAME
LEFT JOIN TABLE3 ON TABLE2.ID = TABLE3.ID

EXAMPLE2

SELECT NAME, HOBBIES
FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.NAME = TABLE2.NAME
INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.ID

I want all the NAMES, no matter if they have HOBBIES or not. And this only happens in EXAMPLE1, in EXAMPLE2 the query returns the NAMES with HOBBIES, but I can't understand why it's not working with EXAMPLE2. The only difference is the INNER and that does not affect the relationship between table 1 and 2.

Where am I going wrong?

philipxy
  • 14,867
  • 6
  • 39
  • 83
JJBB
  • 1
  • 1
  • *I want all the NAMES, no matter if they have HOBBIES or not.* If so then `names` column must be a base. For to collect all names you must use a subquery which will gather all names from tables 1 and 2 (use UNION). Then join all 3 tables to this subquery with LEFT JOIN. – Akina Nov 28 '22 at 18:50
  • [What is the difference between "INNER JOIN" and "OUTER JOIN"??](https://stackoverflow.com/a/46091641/3404097) – philipxy Nov 30 '22 at 11:50
  • [Is it true that using INNER JOIN after any OUTER JOIN will essentially invalidate the effects of OUTER JOIN?](https://stackoverflow.com/a/55111083/3404097) – philipxy Nov 30 '22 at 11:52
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug 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. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Nov 30 '22 at 11:53
  • Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] – philipxy Nov 30 '22 at 11:54

1 Answers1

0

Since you used INNER JOIN in your latter join:

INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.ID

This condition requires that the row include a non-NULL value for TABLE2.ID. This rules out any case where the join to TABLE2 produced no matches.

You can solve this by doing an outer join to the pairing of TABLE2 and TABLE3:

SELECT TABLE1.NAME, HOBBIES
FROM TABLE1
LEFT JOIN (TABLE2 INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.ID)
   ON TABLE1.NAME = TABLE2.NAME;

You can also solve it by using RIGHT OUTER JOIN:

SELECT TABLE1.NAME, HOBBIES
FROM TABLE2 INNER JOIN TABLE3 ON TABLE2.ID = TABLE3.ID
RIGHT OUTER JOIN TABLE1 ON TABLE1.NAME = TABLE2.NAME;
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for your answer, but I still don't understand why INNER JOIN constrains LEFT JOIN between TABLE 1 and TABLE2, shouldn't they be independent? I mean, you have to solve the first JOIN first and then the second one, right? – JJBB Nov 28 '22 at 19:44
  • Yes, but the second join requires that `TABLE2.ID` is not null. – Bill Karwin Nov 28 '22 at 20:14