SQL – IN, NOT IN operators

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.

SQL Syntax for AND, OR operators:

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

sql-table23

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:

sql-table24

Prev     Next





Share this:
Facebook Twitter Linkedin