CODEWITHSUNDEEP
sqlsql-servernotin
schikkamksu
schikkamksu

Reputation: 85

Using multiple NOT IN in SQL Server

I've two tables like this:

Table 1: City - primary key: ID_City:

| City      | ID_City | ID_State|
+-----------+---------+---------+
| Chennai   | 1       | 2       |
| Bengaluru | 2       | 1       |
| Lucknow   | 3       | 3       |
|   .       | .       | .       |
|   .       | .       | .       |

And I'm adding new cities to an existing database while checking for already existing cities using NOT IN. The @table looks like this:

Table 1: @table

| City      | ID_State|Zip_Code|
+-----------+---------+--------+
| Chennai   | 2       |00001   |
| Chennai   | 2       |00002   |
| Chennai   | 2       |00003   |
| Bengaluru | 1       |10011   |
| Bengaluru | 1       |10012   |
| Bengaluru | 1       |10013   |
| Lucknow   | 3       |20001   |
| Mysore    | 4       |50001   |
| Mysore    | 4       |50002   |
| Mysore    | 4       |50003   |
|   .       | .       | .      |

Expected result:

  1. The query should check for City that are already there and filter out those and should not add. (This is done using NOT IN)

  2. Also, it should not add multiple copies of same cities if there are multiple rows of same cities. For example: The query should add only one row of City- 'Mysore' and not three rows

Query I executed:

SELECT 
    City, id_state 
FROM 
    @table
WHERE 
    City NOT IN (SELECT City FROM City WHERE City IS NOT NULL)
    AND id_state NOT IN (SELECT id_state FROM City WHERE id_state IS NOT NULL)

Upvotes: 1

Views: 2112

Answers (4)

Dinesh
Dinesh

Reputation: 112

You can achieve this with NOT IN and NOT EXISTS

NOT IN:

SELECT DISTINCT t.City, t.id_state 
  FROM @table t 
 WHERE City NOT IN (SELECT c.City  FROM City WHERE c.city = t.city AND c.id_state = t.id_state)

NOT EXISTS:

SELECT DISTINCT t.City, t.id_state 
  FROM @table t 
 WHERE NOT EXISTS  (SELECT TOP 1 c.city  FROM City WHERE c.city = t.city AND c.id_state = t.id_state)

NOT EXISTS is better than NOT IN. Also there is no need multiple NOT IN.

Upvotes: 2

Alexander Volok
Alexander Volok

Reputation: 5940

Consider to use NOT EXISTS instead of NOT IN: 

SELECT DISTINCT City
,               id_state
FROM   @table t1
WHERE  NOT EXISTS (   SELECT City
                      FROM   City t2
                      WHERE  t1.City = t2.City
                             AND t1.id_state = t2.id_state 
                             );

Reasons:

  1. Multiple predicates can be defined in one clause, so need to have multiple NOT IN
  2. From a performance perspective it comes to a faster query plan than NOT IN.

Upvotes: 1

PSK
PSK

Reputation: 17943

One city name can belong to multiple states, so you need to check for both the combinations.

I feel following query should give you desired output.

SELECT DISTINCT City, id_state 
FROM @table T1
WHERE NOT EXISTS
(
 SELECT 1 FROM City C WHERE C.City = T1.City AND T.id_state = C.id_state  
)

Upvotes: 2

edu
edu

Reputation: 126

you can use a select distinct:

SELECT distinct City, id_state FROM @table WHERE City 
NOT IN (SELECT City FROM City WHERE City IS NOT Null) AND id_state NOT 
IN (SELECT id_state FROM City WHERE id_state IS NOT Null)

Upvotes: 1

Related Questions