what is the difference between using filter condition in WHERE clause and JOIN condition

Question

Say i have following Join query with any of join type (INNER/LEFT/RIGHT/FULL)

SELECT E.id, D.name, E.sal from EMP E
INNER/LEFT/RIGHT/FULL JOIN DEP D
ON E.id = D.id
AND E.sal > 100

Also, I have similar query with WHERE condition

SELECT E.id, D.name, E.sal from EMP E
INNER/LEFT/RIGHT/FULL JOIN DEP D
ON E.id = D.id
WHERE E.sal > 100

The only difference in above query is using E.sal > 100 in JOIN and WHERE Conditions.

In which case above query will result differently ? or both are same always ?

@GordonLinoff : Thanks. Could you also please answer this question ? — logan, May 31 '15 at 16:19
http://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause — Ivan Sivak, May 31 '15 at 16:26

Gordon Linoff · Accepted Answer · 2015-05-31T16:33:46.550

With an INNER JOIN, there is no difference, either in terms of performance or the definition of the result set. There is a difference with OUTER JOINs.

The WHERE clause filters the results of the FROM after the FROM is evaluated. So, consider these two queries:

SELECT E.id, D.name, E.sal
from EMP E LEFT JOIN
     DEP D
     ON E.id = D.id
WHERE E.sal > 100;

SELECT E.id, D.name, E.sal
from EMP E LEFT JOIN
     DEP D
     ON E.id = D.id AND E.sal > 100;

The first filters the EMP table to get the appropriate ids. The second does not filter the EMP table. Why not? Well, the definition of LEFT JOIN says to take all rows in the first table, regardless of whether the ON finds a matching record in the second table. So, there is no filtering.

Now, consider this version:

SELECT E.id, D.name, E.sal
from EMP E RIGHT JOIN
     DEP D
     ON E.id = D.id
WHERE E.sal > 100;

SELECT E.id, D.name, E.sal
from EMP E RIGHT JOIN
     DEP D
     ON E.id = D.id AND E.sal > 100;

The first turns the RIGHT JOIN to an INNER JOIN. Why? Because for non-matching rows, E.sal is NULL and fails the WHERE. The second version keeps all departments, even those that have no employees matching the condition. Note: I (and others) much prefer left outer joins to right outer joins in terms of following the logic. The logic for a left outer join is simple: keep all rows in the first table.

The FULL OUTER JOIN combines these two situations.

Here's a fiddle showing examples of all: http://www.sqlfiddle.com/#!6/a3572/1/7 I was thinking 'deposits' instead of 'departments' so used people's names, but the example still shows when they're different/same. — Hart CO, May 31 '15 at 16:28

what is the difference between using filter condition in WHERE clause and JOIN condition

1 Answers1