Postgresql JOIN..ON vs WHERE clause

💻 Tech

In PostgreSQL, the result can change if you use either a JOIN..ON or the WHERE clause.

Suppose we have these tables:

# employee table
| id  | name  | department_id |
| --- | ----- | ------------- |
| 1   | bob   | 1             |
| 2   | alice | 2             |
| 3   | marv  | 3             |
| 4   | dan   | null          |

# department table
| id  | name  | category |
| --- | ----- | -------- |
| 1   | dept1 | tech     |
| 2   | dept2 | tech     |
| 3   | dept2 | hr       |

The goal is to retrieve all department employees regardless if they have a department or not and to display the department name if the category is ‘tech’.

Let’s use query 1 with WHERE clause:

-- Query 1
SELECT emp.name, COALESCE(dep.name,'none') dept_name
FROM employee emp
LEFT JOIN department dep
ON emp.department_id = dep.id
WHERE dep.category = 'tech';

# Query 1 output
| name  | dept_name |
| ----- | --------- |
| bob   | dept1     |
| alice | dept2     |

The output is not what’s expected as it’s only showing the two employees under the department in ‘tech’ category. What happens first is the LEFT JOIN returns all rows and then the WHERE clause filters the rows that don’t match the condition, resulting to only two rows.

Let’s now use query 2 with JOIN..ON clause:

SELECT emp.name, coalesce(dep.name,'none') dept_name
FROM employee emp
LEFT JOIN department dep
ON emp.department_id = dep.id
AND dep.category = 'tech';

# Query 2 output
| name  | dept_name |
| ----- | --------- |
| bob   | dept1     |
| alice | dept2     |
| marv  | none      |
| dan   | none      |

With the JOIN..ON clause, the result is as expected. It doesn’t filter the rows like what the WHERE clause does. In addition, it joins only the rows that match the department id and category but also includes the rows that don’t match the condition.

For your reference, here’s the DB Fiddle: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/12147

But if that’s not accessible anymore, here’s the SQL script:

CREATE TABLE department (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(255) NOT NULL
);

CREATE TABLE employee (
 id INT PRIMARY KEY,
 name VARCHAR(255) NOT NULL,
 department_id INT,
 FOREIGN KEY (department_id) REFERENCES department(id)
);

INSERT INTO department(id,name,category) VALUES
(1,'dept1','tech'),
(2,'dept2','tech'),
(3,'dept2','hr');

INSERT INTO employee(id, name, department_id) VALUES
(1,'bob',1),
(2,'alice',2),
(3,'marv',3),
(4, 'dan', NULL);