One of the more useful tools in SQL especially for drawing up reports but by no means limited to this purpose, is the use of sub-queries and [NOT] EXISTS.

I commonly find IF NOT EXISTS used for INSERT statements to avoid overwriting existing data but this type of statement and use is very different.

You can determine if a value never occurs in a record set or you can very quickly and simply find out of a value does indeed occur.

To find if there are orphan products not assigned to a category:


SELECT DISTINCT `product` FROM `product_table` WHERE NOT EXISTS (SELECT DISTINCT `product` FROM `products_in_categories`);


EXISTS on the other hand avoids the need for a LEFT JOIN by providing a simple way to find an intersect between two tables.

To find if there is an attribute assigned to one or more products:


SELECT DISTINCT 'attributeType' FROM `attributes` WHERE EXISTS (SELECT * FROM `products` WHERE `products.attributeType` = `attributes.attributeType`);