FILTER CONDITIONS WITH ON CLAUSE VERSUS WHERE CLAUSE IN POSTGRESQL

In an SQL query, data can be filtered in the WHERE clause or the ON clause of a join.

Let’s consider an example to check these implementations and differences between the two in PostgreSQL.

To implement this scenario let’s consider two tables that can be joined together. In order to use the ON clause.

Table1: dept , Table2: emp

— Table1: dept
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY(deptno)
);

INSERT INTO dept
(deptno, dname, loc)
VALUES
(10, ‘ACCOUNTING’, ‘NEW YORK’),
(20, ‘RESEARCH’, ‘DALLAS’),
(30, ‘SALES’, ‘CHICAGO’),
(40, ‘OPERATIONS’, ‘BOSTON’);

select * from dept;

— Table2: emp

CREATE TABLE emp(
empno INT,
ename VARCHAR(10),
job VARCHAR(9),
mgr INT,
hiredate DATE,
sal NUMERIC(7,2),
comm NUMERIC(7,2),
deptno INT,
CONSTRAINT pk_emp PRIMARY KEY(empno),
CONSTRAINT fk_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno)
);

ALTER TABLE emp ADD CONSTRAINT fk_mgr FOREIGN KEY(mgr) REFERENCES emp(empno);

INSERT INTO emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES
(7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981–11–17’, 5000, NULL, 10),
(7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981–05–01’, 2850, NULL, 30),
(7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981–06–09’, 2450, NULL, 10),
(7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981–04–02’, 2975, NULL, 20),
(7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987–07–13’, 3000, NULL, 20),
(7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981–12–03’, 3000, NULL, 20);

select * from emp;

Using ON Versus WHERE Clauses in an Inner Join

Problem statement:

Write an SQL query to return data from emp and dept table only when there is a matching deptno in both tables. And result should contain those row only where dname is ACCOUNTING.

Solution:

In order to write SQL query we can have three approaches

Approach 1:- (Here by placing filter condition in the ON clause data is filtered first than applies join between the tables)

select * from dept d inner join emp e
on d.deptno=e.deptno
and d.dname=’ACCOUNTING’;

Approach 2:-

select * from dept d inner join emp e
on d.deptno=e.deptno
where d.dname=’ACCOUNTING’;

Approach 3:-

select * from dept d,emp e
where d.deptno=e.deptno
and d.dname=’ACCOUNTING’

So, all the approaches have the same result regardless of whether the filter and join are placed in the ON clause or the WHERE clause.

Note:- As a factor of performance, it makes no difference in the case of INNER JOIN whether the filter condition is placed in the ON clause or the WHERE clause in Postgresql.

Using ON Versus WHERE Clauses in a Left Join

Problem statement:- Write a SQL query to return data from emp and look up dept table only for emp that is in ACCOUNTING dept.User want to display all data from emp, deptname, and loc from dept.

Solution:- In order to write SQL query we can have two approaches

Approach 1:-(Here Before join filtering of data takes place)

SELECT emp.*, dept.dname, dept.loc
FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
AND dept.dname = ‘ACCOUNTING’;

Approach 2:-( After join data will be filtered)

SELECT emp.*, dept.dname, dept.loc
FROM emp LEFT JOIN dept ON emp.deptno = dept.deptno
WHERE dept.dname = ‘ACCOUNTING’;

So, In the case of left join, both approaches have a different result. Understanding the key business requirements decides whether to place filter conditions in the ON clause or the WHERE clause.

Conclusion

Using filter conditions with the ON clause versus the WHERE clause in the case of the left join will lead to different results.In the case of inner join does not impact query result