Friday, July 26, 2019

mysql - LEFT JOIN or INNER JOIN to find items from one table that are in second table

I have a quick question on JOINS.


If I wanted to find how many items from table1 that are used in table2, would I use a INNER JOIN or LEFT JOIN (se below).


An INNER JOIN would show me where 'ID' is in both tables so should represent when ID from table1 is used in table2, but then listing all table2 where ID is the same as table1 ID (LEFT JOIN) should bring back the same?


But the results are different:


INNER JOIN brings back 252,222
LEFT JOIN brings back 258,637


PS: table2 is a child of table1, so table2 IDs can only be from table1 (table1 a list of products, table2 a list of selected products) so looking for all products from table1 that are seleted so in table2 (been selected)


SELECT DISTINCT
t1.name, t1.details
FROM
table1 AS t1
INNER JOIN
table2 AS t2 ON t1.id = t2.t1_id
SELECT DISTINCT
t1.name, t1.details
FROM
table1 AS t1
LEFT JOIN
table2 AS t2 ON t1.id = t2.t1_id

Which would br the correct SQL, I'm guessing INNER JOIN.

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...