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.
LEFT JOIN is not the same as INNER JOIN . With a LEFT JOIN , those rows are returned too, that are not present in the second table! If you want those records, that have their IDs in the second table too, use INNER JOIN .
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?
In this case, an INNER JOIN makes perfect sense as it would give records common to both table1 and table2.
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)
This requires you to enforce FOREIGN KEY constraint in TABLE2 that IDs can only be present after they are present in TABLE1. Once you do this, then it makes sense.
For left join if the result is not satisfied joined then the result is returned from table1 and table2 null of. In the case of inner join will return only results that satisfy the condition.
链接地址: http://www.djcxy.com/p/86266.html上一篇: 在Solr查询中使用inner Join