CODEWITHSUNDEEP
mysqlsqlselectjoin
Ethan Allen
Ethan Allen

Reputation: 14835

How do I SELECT the opposite (or what is NOT IN) a set of results?

I have the following MySQL query:

SELECT dgc.name, dgc.id, dgc.has_special 
FROM keyword k 
JOIN d_gift_card dgc ON k.gc_id = dgc.id 
WHERE keyword IN ('red') 
GROUP BY k.gc_id 
HAVING COUNT(DISTINCT k.keyword) = 1

It returns to me a list of unique id's that have the keyword 'red' attached with it.

What I am trying to do is get a list of unique id's that do not have the keyword 'red' attached with it. Basically the opposite of what the query is doing.

I've tried this query using NOT IN but it's bringing back all results, included the ones that have 'red' associated with them. What is the proper way to this?

SELECT dgc.name, dgc.id, dgc.has_special 
FROM keyword k 
JOIN d_gift_card dgc ON k.gc_id = dgc.id 
WHERE keyword NOT IN ('red') 
GROUP BY k.gc_id

Upvotes: 0

Views: 212

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I would simply do this by moving the condition to the having clause:

SELECT dgc.name, dgc.id, dgc.has_special 
FROM keyword k JOIN
     d_gift_card dgc
     ON k.gc_id = dgc.id 
GROUP BY dgc.name, dgc.id, dgc.has_special  
HAVING SUM( keyword IN ('red') ) = 0;

Note that the GROUP BY clause matches the unaggregated SELECT columns. This is a best practice -- and required by the SQL standard in most cases.

The expression in the HAVING clause counts the number of rows that are 'red'. The = 0 says there are none.

The most efficient way to write the query is probably:

SELECT dgc.name, dgc.id, dgc.has_special 
FROM d_gift_card dgc
WHERE NOT EXISTS (SELECT 1
                  FROM keyword k         
                  WHERE k.gc_id = dgc.id AND k.keyword IN ('red')
                 );

This can take advantage of an index on keyword(gc_id, keyword), and doesn't require any aggregation at all.

Upvotes: 1

cninicu
cninicu

Reputation: 75

You can use MINUS. Something like this: 

SELECT dgc.name, dgc.id, dgc.has_special 
FROM keyword k 
JOIN d_gift_card dgc ON k.gc_id = dgc.id 

MINUS

SELECT dgc.name, dgc.id, dgc.has_special 
FROM keyword k 
JOIN d_gift_card dgc ON k.gc_id = dgc.id 
WHERE keyword NOT IN ('red') 
GROUP BY k.gc_id

Upvotes: -1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

One option would be to use conditional aggregation and assert that red never occurs:

SELECT dgc.name, dgc.id, dgc.has_special 
FROM d_gift_card dgc
INNER JOIN
(
    SELECT gc_id
    FROM keyword
    GROUP BY gc_id
    HAVING SUM(CASE WHEN keyword = 'red' THEN 1 ELSE 0 END) = 0
) k
    ON k.gc_id = dgc.id;

The logical problem with putting the check on the keyword in the WHERE clause is that applies to individual records, not entire id groups. So it would leave non matching records even if an overall group had positive matches as well.

Upvotes: 1

Related Questions