I’m trying to Write a SELECT statement that returns the category_name column from the Categories table and returns one row for each category that has never been assigned to a product in the Products table. there are four categories in the categories table and only three have been used in the products table. How do i make it show the one that hasn’t been used? this is the code I’ve been trying, but it returns an empty result set.
select category_name from categories c natural join products p where not exists ( select category_name, category_id from categories c natural join products p);
Your query returns
NULL because the subquery returns at least one row. Hence something exists. Juergen’s answer shows how to do what you want with a
left join. Here is how you solve the problem using
select c.category_name from categories c where not exists (select 1 from products p where p.category_id = c.category_id);