By KS1


2012-10-19 14:32:01 8 Comments

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.

3 comments

@adam 2012-10-19 14:49:33

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.

@Vikdor 2012-10-19 14:36:05

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.

@ppeterka 2012-10-19 14:34:49

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.

Related Questions

Sponsored Content

25 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

12 Answered Questions

[SOLVED] LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

12 Answered Questions

[SOLVED] SQL Inner-join with 3 tables?

  • 2012-04-17 16:45:29
  • Bob Sanders
  • 1246620 View
  • 310 Score
  • 12 Answer
  • Tags:   sql join inner-join

11 Answered Questions

[SOLVED] INNER JOIN ON vs WHERE clause

6 Answered Questions

[SOLVED] Difference between JOIN and INNER JOIN

13 Answered Questions

[SOLVED] How to join (merge) data frames (inner, outer, left, right)

9 Answered Questions

[SOLVED] What is the difference between Left, Right, Outer and Inner Joins?

  • 2009-01-15 19:07:31
  • MrM
  • 367836 View
  • 546 Score
  • 9 Answer
  • Tags:   sql database join

4 Answered Questions

[SOLVED] left join and group of inner join

Sponsored Content