The IN operator in SQL allows you to specify multiple values in a WHERE clause. It is used to compare a value to a set of values returned by a subquery or to a list of values specified in the IN clause. It returns true if the value matches one of the values in the list and false if it does not.
Syntax of SQL IN operator
The syntax for using the IN operator is as follows:
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, ...);
In the above syntax,
- column_name(s) is the name of the column(s) that you want to retrieve data from.
- table_name is the name of the table that you want to retrieve data from.
- column_name is the name of the column that you want to compare the values in the IN clause with.
- value1, value2, … are the values that you want to compare with the column_name in the IN clause.
SQL IN operator with subquery
The IN operator can also be used with a subquery. The subquery returns a set of values that are used in the IN clause. The syntax for using the IN operator with a subquery is as follows:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT column_name
FROM table_name
WHERE condition);
In the above syntax,
- column_name(s) is the name of the column(s) that you want to retrieve data from.
- table_name is the name of the table that you want to retrieve data from.
- column_name is the name of the column that you want to compare the values in the IN clause with.
- SELECT column_name is the subquery that returns a set of values to be used in the IN clause.
- condition is the condition that you want to apply in the subquery to retrieve data from the table.
Example of SQL IN operator
Consider the following “employees” table:
| ID | Name | Age | City |
|---|---|---|---|
| 1 | John Doe | 32 | New York |
| 2 | Jane Doe | 28 | London |
| 3 | Jim Smith | 42 | Paris |
| 4 | Sophie Johnson | 24 | Berlin |
Suppose you want to retrieve the names and ages of all employees whose city is either New York or Paris. You can use the following SQL statement with the IN operator:
SELECT Name, Age
FROM employees
WHERE City IN ('New York', 'Paris');
The result of the above SQL statement will be:
| Name | Age |
|---|---|
| John Doe | 32 |
| Jim Smith | 42 |
As you can see, the above SQL statement retrieves only the names and ages of employees who live in either New York or Paris, as specified in the IN clause.
Example of SQL IN operator with subquery
Suppose you want to retrieve the names and ages of all employees who live in a city that has the letter “o” in its name. You can use the following SQL statement with the IN operator and a subquery:
SELECT Name, Age
FROM employees
WHERE City IN (SELECT City
FROM employees
WHERE City LIKE '%o%');
The result of the above SQL statement will be:
| Name | Age |
|---|---|
| John Doe | 32 |
| Jane Doe | 28 |
| Jim Smith | 42 |
As you can see, the above SQL statement retrieves only the names and ages of employees who live in a city that has the letter “o” in its name, as specified in the subquery.
Conclusion
The IN operator in SQL is a useful tool for comparing a value to a set of values or to a list of values. It can be used with a subquery to retrieve data based on the results of another query. Understanding the IN operator and how to use it is an essential part of working with SQL databases.
