Left Join in Mysql?

Question

This is my first Table.

Then the second one is

Now I am trying to do Left join like this

SELECT t1.StackID FROM t1 LEFT JOIN t2 on t1.StackID=t2.StackID

Output

I am confused here, Is this the correct output? Is not this supposed to return only the 5 rows which is present in left side table.

Answer 1

To give yourself a little more understanding of how a left join works try this:

SELECT t1.StackID, t2.StackID FROM t1 LEFT JOIN t2 on t1.StackID=t2.StackID

basically a left join returns all the rows from the left table plus matching ones from the right.

Answer 2

There is a simple example of left join:-

SELECT * FROM a JOIN b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) 
WHERE b.key=d.key;

Answer 3

It's correct output. You are doing LEFT JOIN, so for every record in LEFT table DBMS will 'concatenate' the corresponding RIGHT table record(-s) (and NULL, if there's no corresponding record for the JOIN condition; also remember, that if there are more that 1 corresponding record - all will be joined - this issue is why you're getting not only 5 records from 1-st table).

The thing you're trying to achieve should be done by either DISTINCT modifier, i.e.

SELECT DISTINCT t1.StackID FROM t1 LEFT JOIN t2 ON t1.StackID=t2.StackID;

More about JOIN in SQL you can read here.

Answer 4

Yes, this is the expected output.

To get the distinct IDs, use DISTINCT -

SELECT DISTINCT t1.StackID 
FROM t1 
LEFT JOIN t2 
ON t1.StackID=t2.StackID

When you left joined your t2 table with t1 based on stackID, then the database will join every row of t2 with the every row of t1 which has the same stackID value. Since 1 appears in six rows in the t2 table as a stackID value, all of them are joined with the first row of t1. Similar thing is true for all other values.

Answer 5

These extra repeated values of StackId are coming from the join, just use DISTINCT to get those only 5 values:

SELECT DISTINCT t1.StackID 
FROM t1 
LEFT JOIN t2 on t1.StackID=t2.StackID;

• SQL Fiddle Demo