what is the basic difference between INNER JOIN and LEFT JOIN?

Question

I am running the following two queries but both of them are giving the same output.

Query 1

SELECT * FROM EMP LEFT JOIN DEPT
ON EMP.EMP_ID = DEPT.EMP_ID

Query 2

SELECT * FROM EMP INNER JOIN DEPT
ON EMP.EMP_ID = DEPT.EMP_ID

THESE both queries give the same output i.e. all rows from both the tables. As far as I know left join should give only rows from the first table i.e. "EMP" and not from other table (DEPT).

thanks.

*"only rows from the first table"* = `SELECT * FROM EMP;` :-) — dnoeth, Sep 24 '15 at 06:46
Here you have a visual cheat sheet about sql joins http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg — Teis Lindemark, Sep 24 '15 at 06:48

score 1 · Answer 1 · answered Sep 24 '15 at 06:17

An INNER JOIN will only show the rows for which the join clause holds. A LEFT JOIN will also show the rows on the 'left' table for which the join clause doesn't hold, and will give null values for the columns of the joined table.

So when doing a left join, it will not decrease the number of records in the result set for the left table, whereas with an inner join it might. However, if the join clause gives at least 1 match in the right table for each row in the left table, the inner join and left join give an equivalent result.

what is the basic difference between INNER JOIN and LEFT JOIN?

1 Answers1