CODEWITHSUNDEEP
mysqlsqlpostgresql
lcoq
lcoq

Reputation: 10726

SELECT with WHERE NOT IN condition without subquery

I wonder if this is possible to have the same results as the following request without using a subquery:

SELECT p1.id
FROM products p1
WHERE NOT p1.id IN (
  SELECT p2.id
  FROM products p2
  JOIN product_translations t
    ON t.product_id = p2.id
   AND t.locale = 'fr'
);

As you can see, a products row can have many product_translations rows (0..n).

The expected result must be only products that does not have product_translations with locale fr.

Upvotes: 1

Views: 5578

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

The join inside the sub-query is not needed. Your query thus equals:

SELECT id
FROM products
WHERE id NOT IN 
(
  SELECT product_id 
  FROM product_translations
  WHERE locale = 'fr'
);

Without a sub-query you would have to outer join the translation table and eliminate the matches:

SELECT p.id
FROM products p
LEFT JOIN product_translations pt ON pt.product_id = p.id AND pt.locale = 'fr'
WHERE pt.product_id IS NULL;

Edit: Just for completeness' sake here is the NOT EXISTS version (also needing a sub-query of course):

SELECT id
FROM products
WHERE NOT EXISTS
(
  SELECT *
  FROM product_translations
  WHERE product_id  = products.id
  AND locale = 'fr'
);

Upvotes: 6

Related Questions