CODEWITHSUNDEEP
mysql
user1260928
user1260928

Reputation: 3439

mysql : issue with NOT IN query syntax

why does this query doesn't return any result (it should return results)

SELECT id FROM T_CLIENT where id 
    not in (select distinct client_id from T_VENTE);

whereas this one does.

SELECT *
FROM T_CLIENT LEFT JOIN T_VENTE ON T_VENTE.client_id=T_CLIENT.id
WHERE T_VENTE.client_id IS NULL;

Those two queries seems the same to me.

Upvotes: 0

Views: 26

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169524

The NOT IN query returns null. Instead use NOT EXISTS:

SELECT id FROM T_CLIENT c where NOT EXISTS  
    (select 1 from T_VENTE v where v.client_id = c.id);

Upvotes: 2

Related Questions