-1

I have a request which looks like this :

SELECT A.*
FROM A
  INNER JOIN B
    ON A.id_B = B.id
  INNER JOIN C
    ON B.id_C = C.id

A.id_B and B.id_C are foreign keys NOT NULL

For me the INNER JOIN are totally useless and just slows down the query but a colleague told me that in certain specific case it removes some rows from A. He could not show me any of those cases and I could not find any either.

I wanted to know if the INNER JOIN really are useful or if they can be removed.

4
CREATE TABLE a (id INT, id_b INT);
CREATE TABLE b (id INT, id_c INT);
CREATE TABLE c (id INT);
INSERT INTO c VALUES (1), (1), (2), (3), (3), (4), (6);
INSERT INTO b VALUES (1,1), (1,1), (2,2), (2,2), (3,3), (4,4), (5,5);
INSERT INTO a VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7);
SELECT a.*
FROM a
  INNER JOIN b
    ON a.id_b = b.id
  INNER JOIN c
    ON b.id_c = c.id
id | id_b
-: | ---:
 1 |    1
 1 |    1
 1 |    1
 1 |    1
 2 |    2
 2 |    2
 3 |    3
 3 |    3
 4 |    4

db<>fiddle here

  • Row with value 1 is returned 4 times (2 matches in table b and 2 matches in table c gives 4 combinations).

  • Row with value 2 is returned 2 times (2 matches in table b).

  • Row with value 3 is returned 2 times (2 matches in table c).

  • Row with value 4 is returned once (no duplicates in joining chain).

  • Row with value 5 is not returned because there is no match in table c.

  • Row with value 6 is not returned because there is no match in table b.

  • Row with value 7 is not returned because there is no match in tables b and c.

You may test the fiddle - remove table c joining (or both b and c joinings), look how the output changes, and think why so...

| improve this answer | |
0

The only way that they would remove rows from the result set is if the foreign key relationships were broken.

Question: Are they actually enforced by foreign keys?

Was that [once] the intention but, for whatever reason, it was never implemented? (Sadly, it happens)

If these really are "proper" foreign keys then yes, the joins are redundant.

If in doubt, check it for yourself:

SELECT * 
FROM A
LEFT JOIN B ON A.id_B = B.id
LEFT JOIN C ON B.id_C = C.id

WHERE B.id IS NULL 
   OR C.id IS NULL 

If you get zero rows back, then everything is as it should be and the joins can be safely removed.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.