NOT IN condition in SQL NOT IN condition in SQL sql sql

NOT IN condition in SQL

You can't use IN with more than one column but you can usually achieve the same effect using EXISTS:

SELECT *FROM tbl1WHERE NOT EXISTS(    SELECT *    FROM tbl2    WHERE tbl2.col1 = tbl1.col1        AND tbl2.col2 = tbl1.col2)

General syntax:

where col not in (items)

Items can be

  • a list of items -- (4,5,3,5,2) or ('243','3','cdds') or any other datatype.

  • Or a select statement (select hatefulthings from table)

Addition 6 years later

All major platforms support tuples with NOT IN, for example

SELECT *FROM empoyeeWHERE (empID, @date) NOT IN   (SELECT empID, vacationDay   FROM empVacation  )

In this example we select everything from the employee table where the tuple of employee id and date are not in a table containing vacation days.

Your question is a bit unclear. Is this what you need?


This will show you all records where the search phrase ("Smith") is in neither the first_name nor the last_name column.