Let’s consider a toy example. There is a table employees and table tasks, where each task is assigned to one employee. One employee can have multiple tasks.
I want to query employees filtering them by some columns in their tasks. I group the employees to display each one only once. The query would be something like this:
SELECT * FROM employees emp JOIN tasks tsk on emp.id = tsk.assigned_emp WHERE tsk.deadline = today GROUP BY employees
This works fine, but now let’s say that I’d like to select the employees that don’t have any tasks which deadline is due today. My first try was:
tsk.deadline != today
but then is finds employees with at least one task that is not due today. How to write a query to include all possible tasks for a given employee?
Advertisement
Answer
You want the LEFT JOIN ... IS NULL
pattern for this.
SELECT * FROM employees emp LEFT JOIN tasks tsk ON emp.id = tsk.assigned_emp AND tsk.deadline = today WHERE tsk.deadline IS NULL GROUP BY employees
JOIN
removes rows from its first table that don’t match your ON condition. By contrast, LEFT JOIN
leaves them in place putting NULL values into the result columns from the second table.