SQL List of products not included in any transaction-CodePudding

I have two tables Transactions_Products(tref,prod_id) and Products(prod_id,prod_name,price). How to find product_id of Products which is not in any of Transactions_Products.

I have tried using NOT IN but it didn't work as expected

CodePudding user response：

I would use exists here:

SELECT p.*
FROM Products p
WHERE NOT EXISTS (
    SELECT 1
    FROM Transactions_Products tp
    WHERE tp.prod_id = p.prod_id
);

CodePudding user response：

You could beside the option NOT EXISTS (see Tim's answer) also use a LEFT JOIN and select the products which can't be found in the transactions table (which means Transactions_Products.prod_id is null):

SELECT p.prod_id
FROM products p
LEFT JOIN Transactions_Products tp 
ON p.prod_id = tp.prod_id     
WHERE tp.prod_id IS NULL;

This query will likely be slower than NOT EXISTS when the tables have millions of rows (there have been analysis that prove this), otherwise, it shouldn't make a noticeable difference.

Many people prefer using JOIN for such purposes because they think it's more readable. But this is of course kind of taste.

So I advice you to choose the option you like more, unless the size of your table requires the fastest query, in this case NOT EXISTS should always be prefered.