CODEWITHSUNDEEP
mysql
Julien Arcin
Julien Arcin

Reputation: 11

Can we use IN () AND NOT IN () with MySQL?

I want my SQL request to take some rows but skip another ones using a IN () AND NOT IN (). But this doesn't seem to work properly. Do anybody have an idea why ?

Thanks !

SELECT *
FROM   `prospect`
WHERE  id IN (SELECT idprospect
              FROM   envoi_prospect
              WHERE  idenvoi IN ( '1', '2', '3', '4',
                                  '6', '7', '10', '12' )
       AND id NOT IN (SELECT idprospect
                      FROM   envoi_prospect
                      WHERE  idenvoi IN ( '8', '9', '11', '13' ))
ORDER  BY `date` DESC
LIMIT  0, 30

Upvotes: 1

Views: 72

Answers (2)

Pankaj Jawale
Pankaj Jawale

Reputation: 96

You miss placed the Brackets thats it:

SELECT *
FROM   `prospect`
WHERE  id IN (SELECT idprospect
              FROM   envoi_prospect
              WHERE  idenvoi IN ( '1', '2', '3', '4',
                                  '6', '7', '10', '12' ))
       AND id NOT IN (SELECT idprospect
                      FROM   envoi_prospect
                      WHERE  idenvoi IN ( '8', '9', '11', '13' ))
ORDER  BY `date` DESC
LIMIT  0, 30

You can rewrite the query as :

    SELECT *
        FROM   `prospect`
        WHERE  id IN (SELECT idprospect
                      FROM   envoi_prospect
                      WHERE  idenvoi IN ( '1', '2', '3', '4',
                                          '6', '7', '10', '12' ) And
                             idenvoi NOT IN ( '8', '9', '11', '13' ) )
    ORDER  BY `date` DESC
    LIMIT  0, 30

Upvotes: 1

Dan
Dan

Reputation: 10680

While the SQL syntax certainly allows it, it does not really make sense to apply an IN filter and a NOT IN filter at the same time.

Consider a table containing the following records:

ColA
1
2
3
4

If you write a query like this:

SELECT * FROM MyTable 
WHERE
    ColA IN (1, 2) AND
    ColA NOT IN (4)

The output will be:

ColA
1
2

The IN statement filters your query to only include the rows where ColA is 1 or 2. Thus, in this case, the NOT IN query is completely unnecessary. Now, if you write a query like this instead:

SELECT * FROM MyTable
WHERE
    ColA IN (1, 2, 3) AND
    ColA NOT IN (3, 4)

The output will be the same as above. Why? Well, since you have an AND operator between the IN and the NOT IN statements, both must evaluate to TRUE, for a record to be outputtet. Now, for the 3rd row in the table, ColA IN (1, 2, 3) evaluates to TRUE, but ColA NOT IN (3, 4) evaluates to FALSE, and thus the 3rd row is not outputted.

Upvotes: 2

Related Questions