SQL IN Operator With Example

SQL IN Operator With Example

IN Operator in SQL with example

Syntax

The basic syntax of IN Operation is given below.

SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);

SELECT column_name(s) FROM table_name WHERE column_name IN (SELECT STATEMENT);

How to use IN Operator in SQL?

To understand the IN Operator in SQL we will consider an example of an Employee table suppose in employee table if we want the employee details that the city is (Lucknow, Kanpur, Jaunpur) then by using the IN Operator we can achieve the same.

Example of IN Operator

SELECT * FROM Employee WHERE City IN ('Lucknow', 'Kanpur', 'Jaunpur');

Explanation of Example

The above SQL Statement will select all the employee names belonging to the city ('Lucknow', 'Kanpur', 'Jaunpur').

IN Operator With NOT

Example of IN Operator with NOT

SELECT * FROM Employee WHERE City IN ('Lucknow', 'Kanpur', 'Jaunpur');

Explanation of Example

The above SQL Statement will not select all the employee names belonging to the city ('Lucknow', 'Kanpur', 'Jaunpur').

IN Operator with Sub-query

Example of IN Operator with sub-Query

Users can use the subquery with the IN operator that returns records from a single column. The subquery cannot include more than one column in the SELECT column list.

SELECT EmpId, FirstName, LastName, DeptId FROM Employee WHERE DeptId IN (SELECT DeptId from Department WHERE DeptId > 2);

Explanation of Example

In the above query, the sub-query SELECT DeptId from Department WHERE DeptId > 2 returns two DeptId, 3 and 4. So, now the query would be like SELECT EmpId, FirstName, LastName, Salary FROM Employee WHERE DeptId in (3, 4);.

Hope !!! The above tutorial on " IN Operator in SQL "Is helpful for you...

Team,

QA acharya.