Prev Next
IN, NOT IN operators in SQL are used with SELECT, UPDATE and DELETE statements/queries to select, update and delete only particular records in a table those meet the condition given in WHERE clause and conditions given in IN, NOT IN operators. I.e. it filters records from a table as per the condition. Syntax for SQL IN & NOT IN operators are given below.
Syntax for AND, OR operators in SQL:
| Syntax for SQL IN operator | SELECT column_name1, column_name2, etc FROM table_name WHERE column_name1 IN (value1, value2, etc); |
| Syntax for SQL NOT IN operator | SELECT column_name1, column_name2, etc FROM table_name WHERE column_name1 NOT IN (value1, value2, etc); |
Where [condition] should be in the following format.
[column_name] [Operator] [Value];
Where,
[column_name] – Any one of the column names in the table.
[Operator] – Any one of the following (>, <, =, >=, <=, NOT, LIKE etc)
[Value] – User defined value.
Note:
- IN and NOT IN operators in where clause can be used in any order and in any combination as per the requirement.
Please consider the following table with few records as given below.
Table name (for example): student
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Example: How to use IN & NOT IN operators in a WHERE clause in SELECT queries:
SQL query:
SELECT Student_ID, Student_name, City, Age from student
WHERE Age in (25, 29) AND City NOT IN (‘Chennai’, ‘Delhi’);
SQL query Output:
