Filter in WHERE Clause versus Filter in ON clause

Sometimes SQL developers ask me if there is a difference between putting the Filter in the WHERE Clause and putting the Filter in the ON Clause and if yes, what the difference is. To explain it the best I have stolen a little example (Source at the end):

CREATE TABLE department (
  department_id   INT UNSIGNED NOT NULL
, department_name VARCHAR(14)
, location        VARCHAR(13)
, PRIMARY KEY (department_id)
);

INSERT INTO department (department_id, department_name, location)
VALUES
  (10, 'ACCOUNTING', 'NEW YORK')
, (20, 'RESEARCH',   'DALLAS')
, (30, 'SALES',      'CHICAGO')
, (40, 'OPERATIONS', 'BOSTON')
;

CREATE TABLE employee (
  employee_id   INT UNSIGNED NOT NULL
, employee_name VARCHAR(10)
, job           VARCHAR(9)
, manager_id    INT
, hire_date     DATE
, salary        NUMERIC(7, 2)
, commission    NUMERIC(7, 2)
, department_id INT UNSIGNED NOT NULL
, PRIMARY KEY (employee_id)
);

INSERT INTO employee (employee_id, employee_name, job, manager_id, hire_date, salary, commission, department_id)
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)
;

INNER JOIN

In the INNER JOIN it makes no difference if you are putting the Filter in the WHERE or the ON Clause:

SELECT e.employee_name, e.job, d.department_name, d.location
  FROM employee AS e
  JOIN department AS d ON e.department_id = d.department_id AND d.department_name = 'ACCOUNTING'
;
+---------------+-----------+-----------------+----------+
| employee_name | job       | department_name | location |
+---------------+-----------+-----------------+----------+
| CLARK         | MANAGER   | ACCOUNTING      | NEW YORK |
| KING          | PRESIDENT | ACCOUNTING      | NEW YORK |
+---------------+-----------+-----------------+----------+

SELECT e.employee_name, e.job, d.department_name, d.location
  FROM employee AS e
  JOIN department AS d ON e.department_id = d.department_id
 WHERE d.department_name = 'ACCOUNTING'
;
+---------------+-----------+-----------------+----------+
| employee_name | job       | department_name | location |
+---------------+-----------+-----------------+----------+
| CLARK         | MANAGER   | ACCOUNTING      | NEW YORK |
| KING          | PRESIDENT | ACCOUNTING      | NEW YORK |
+---------------+-----------+-----------------+----------+

I personally prefer the latter one because then it is clearly stated what your are filtering for…

OUTER JOIN

In the LEFT and RIGHT OUTER JOIN it makes clearly a difference if you are putting the Filter in the WHERE or the ON Clause:

SELECT e.employee_name, e.job, d.department_name, d.location
  FROM employee AS e
  LEFT JOIN department AS d ON e.department_id = d.department_id AND d.department_name = 'ACCOUNTING'
;
+---------------+-----------+-----------------+----------+
| employee_name | job       | department_name | location |
+---------------+-----------+-----------------+----------+
| JONES         | MANAGER   | NULL            | NULL     |
| BLAKE         | MANAGER   | NULL            | NULL     |
| CLARK         | MANAGER   | ACCOUNTING      | NEW YORK |
| SCOTT         | ANALYST   | NULL            | NULL     |
| KING          | PRESIDENT | ACCOUNTING      | NEW YORK |
| FORD          | ANALYST   | NULL            | NULL     |
+---------------+-----------+-----------------+----------+

SELECT e.employee_name, e.job, d.department_name, d.location
  FROM employee AS e
  LEFT JOIN department AS d ON e.department_id = d.department_id
 WHERE d.department_name = 'ACCOUNTING'
;
+---------------+-----------+-----------------+----------+
| employee_name | job       | department_name | location |
+---------------+-----------+-----------------+----------+
| CLARK         | MANAGER   | ACCOUNTING      | NEW YORK |
| KING          | PRESIDENT | ACCOUNTING      | NEW YORK |
+---------------+-----------+-----------------+----------+

With OUTER JOINs a condition in the JOIN means to filter rows out of the second (department) table before joining. A condition in the WHERE means to filter rows out of the final result after joining.

Filter in WHERE clause is like an INNER JOIN.

To better understand the first query I have rewritten it a bit:

SELECT e.employee_name, e.job, d.department_name, d.location
  FROM employee AS e
  LEFT JOIN (
             SELECT d.department_id, d.department_name, d.location
               FROM department AS d
              WHERE d.department_name = 'ACCOUNTING'
) AS d ON e.department_id = d.department_id
;
+---------------+-----------+-----------------+----------+
| employee_name | job       | department_name | location |
+---------------+-----------+-----------------+----------+
| JONES         | MANAGER   | NULL            | NULL     |
| BLAKE         | MANAGER   | NULL            | NULL     |
| CLARK         | MANAGER   | ACCOUNTING      | NEW YORK |
| SCOTT         | ANALYST   | NULL            | NULL     |
| KING          | PRESIDENT | ACCOUNTING      | NEW YORK |
| FORD          | ANALYST   | NULL            | NULL     |
+---------------+-----------+-----------------+----------+

I hope, this makes it a bit more clear…

Source

• Jacek Trociński: Using ON Versus WHERE Clauses to Combine and Filter Data in PostgreSQL Joins