Can we use NOT IN on multiple columns?

Question

I am creating a stored procedure (with Sql Server) involving a single table.

I'd like to apply the conditions in my WHERE clause to a pair of columns. i.e. I would like something like this:

CREATE PROCEDURE afficherCreationLuns (@start DATE, @end DATE)
                    AS
                    SELECT dateMin = MIN(t1.dateCollecte), displayName, label,     date = DATEADD(day, -1, MIN(dateCollecte)), capacityInKB = MIN(capacityInKB)
                    FROM Vsp 
                    WHERE dateCollecte > @start AND dateCollecte <= @end
                                    AND (label, displayName) NOT IN (SELECT label, displayName
                                                            FROM Vsp 
                                                            WHERE dateCollecte = @start)

                    GROUP BY displayName, label

After some research, I changed the code to use the EXISTS clause, which seemed better suit my needs. Here is what I have now:

CREATE PROCEDURE afficherCreationLuns (@start DATE, @end DATE)
                    AS
                    SELECT dateMin = MIN(t1.dateCollecte), t1.displayName, t1.label, date = DATEADD(day, -1, MIN(t1.dateCollecte)), capacityInKB = MIN(t1.capacityInKB)
                    FROM Vsp t1
                    WHERE EXISTS (SELECT t2.displayName, t2.label
                                    FROM Vsp t2
                                    WHERE t1.displayName = t2.displayName AND t1.label = t2.label AND t2.dateCollecte > @start AND t2.dateCollecte <= @end
                                    AND (t1.label NOT IN (SELECT t3.label
                                                            FROM Vsp t3
                                                            WHERE t3.dateCollecte = @start)
                                    OR t1.displayName NOT IN (SELECT t4.displayName
                                                                    FROM Vsp t4
                                                                    WHERE t4.dateCollecte = @start) ) )
                    GROUP BY t1.displayName, t1.label

But it didn't change anything.

score 2 · Accepted Answer · answered Jul 07 '14 at 15:18

You can replace NOT IN with NOT EXISTS, like this:

SELECT
    dateMin = MIN(t1.dateCollecte)
,   t1.displayName
,   t1.label
,   date = DATEADD(day, -1, MIN(t1.dateCollecte))
,   capacityInKB = MIN(t1.capacityInKB)
FROM Vsp t1
WHERE t1.dateCollecte > @start
  AND t1.dateCollecte <= @end
  AND NOT EXISTS (
    SELECT *
    FROM Vsp t2
    WHERE t2.dateCollecte = @start
      AND t2.label=t1.label
      AND t2.displayName=t1.displayName
  )
GROUP BY t1.displayName, t1.label

The idea is to run the inner select with the additional condition that matches label and displayName to the corresponding fields of the outer select, and drop records of the outer select where such a match can be found.

score 0 · Answer 2 · answered Jul 07 '14 at 15:19

0

You could concatenate the two columns together? Like so:

SELECT *
FROM [TABLE]
WHERE COLUMN1+' '+COLUMN2
    NOT IN (
        SELECT COLUMN1+' '+COLUMN2
            FROM [OTHER-TABLE])

Thanks.

answered Jul 07 '14 at 15:19

John Bell

2,350
1
14
23

This approach can produce unexpected results when concatenated results give you false aliasing. For example, you would get a match for pairs `{'quick', 'brown fox'}` and `{'quick brown', 'fox'}`. – Sergey Kalinichenko Jul 07 '14 at 15:23

score 0 · Answer 3 · answered Jul 07 '14 at 15:21

Try the Exists clause that way. I'm not sure about the way to refer to the main VSP table, but I think this where clause should do what you want.

WHERE NOT EXISTS (SELECT * FROM Vsp WHERE dateCollecte = @start AND label=Vsp.label AND displayName=Vsp.displayName)

Can we use NOT IN on multiple columns?

3 Answers3