CODEWITHSUNDEEP
sqlarrayssql-server
beginnerspirit
beginnerspirit

Reputation: 125

Use IN to compare Array of Values against a table of data

I want to compare an array of values against the the rows of a table and return only the rows in which the data are different.

Suppose I have myTable:

| ItemCode | ItemName | FrgnName |
|----------|----------|----------|
| CD1      | Apple    | Mela     |
| CD2      | Mirror   | Specchio |
| CD3      | Bag      | Borsa    |

Now using the SQL instruction IN I would like to compare the rows above against an array of values pasted from an excel file and so in theory I would have to write something like:

WHERE NOT IN (
    ARRAY[CD1, Apple, Mella],
    ARRAY[CD2, Miror, Specchio],
    ARRAY[CD3, Bag, Borsa]
)

The QUERY should return rows 1 and 2 "MELLA" and "MIROR" are in fact typos.

Upvotes: 0

Views: 648

Answers (1)

Uueerdo
Uueerdo

Reputation: 15971

You could use a VALUES expression to emulate a table of those arrays, like so:

... myTable AS t
LEFT JOIN (
   VALUES (1, 'CD1','Apple','Mella')
        , (1, 'CD2', 'Miror', 'Specchio')
        , (1, 'CD3', 'Bag', 'Borsa')
) AS v(rowPresence, a, b, c)
ON t.ItemCode = v.a AND t.ItemName = v.b AND t.FrgnName = v.c
WHERE v.rowPresence IS NULL

Technically, in your scenario, you can do without the "rowPresence" field I added, since none of the values in your arrays are NULL any would do; I basically added it to point to a more general case.

Upvotes: 3

Related Questions