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

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 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

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