CODEWITHSUNDEEP
mysqlsqlselectjoin
DaViDa
DaViDa

Reputation: 641

Sql comparing rows

I want to select everything from the product table where the name is not equal to a name existing in ordered_products. I tried this but it didnt work:

SELECT * FROM product WHERE naam IS NOT (SELECT naam FROM ordered_products)

Upvotes: 1

Views: 102

Answers (2)

Thangamani Palanisamy
Thangamani Palanisamy

Reputation: 5290

SELECT * FROM product WHERE naam NOT IN (SELECT naam FROM ordered_products)

OR

SELECT P.* 
FROM product P
LEFT JOIN  ordered_products O ON P.naam = O.naam

Hope this helps

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

You can use the NOT IN predicate like so:

SELECT * 
FROM product 
WHERE naam NOT IN (SELECT naam 
                   FROM ordered_products
                   WHERE naam IS NOT NULL);

But this is not safe, in case there is any NULL values of the naam coming from the other table, thats why I added WHERE naam IS NOT NULL to the subquery. Better off, use JOIN like so:

SELECT p.* 
FROM product
LEFT JOIN ordered_products o ON p.naam = o.naam
WHERE p.naam IS NULL;

Upvotes: 5

Related Questions