Issue
I am trying to get all products and category name if category is still active with below left join script. But I cannot get production information if category is not active. What’s wrong with this left join script?
select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
where c.isactive = 1 or c.isactive is null
Reason
To find out what’s the issue of this script have, we need to understand order of a SQL query. In the script above we used SELECT, FROM, LEFT JOIN and WHERE
- FROM and JOIN: The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried
- WHERE: Once we have the total working set of data, the WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.
- SELECT: Any expressions in the SELECT part of the query are finally computed.
The WHERE clause is execution after the ON clause. which means the predicates in the ON clause are applied to the table before the join, WHERE clause is semantically applied to the result of the join.
So below script will get all the data from product table and will keep categories data as null if cannot find category.
select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
WHERE clause is applied to the JOIN result which will remove products result if category missing.
where c.isactive = 1 or c.isactive is null
Solution
if we want to get all product information even if categories is missing or is not active, we need to changed the WHERE clause to ON clause as below:
select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
and (c.isactive = 1 or c.isactive is null )