What is the difference between using a filter condition in a WHERE clause and a JOIN condition
Say I have the following Join query with any 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 a similar query with a WHERE clause
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 the above query is the use of E.sal> 100 in the JOIN and WHERE clauses.
In what case would the query above look different? or are both always the same?
source to share
From a INNER JOIN
performance standpoint or definition of a result set, there is no difference. There is a difference with OUTER JOIN
s.
The proposal WHERE
filters the results FROM
after being evaluated FROM
. So, consider these two questions:
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;
First filtering the table EMP
to get matching IDs. The second does not filter the table EMP
. Why not? Well, the definition LEFT JOIN
says that all rows must be in the first table, regardless of whether a ON
matching record is found 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 one turns RIGHT JOIN
into INNER JOIN
. What for? Because for non-matching lines, E.sal
is NULL
and is not executed WHERE
. The second version stores all departments, even those that do not have matching employees. Note: I (and others) prefer left outer joins over right outer joins in terms of logic. The logic for the left outer join is simple: keep all rows in the first table.
FULL OUTER JOIN
combines these two situations.
source to share