[Basic Grammar] SQL IN Operator

SQL

grace21110 2023. 10. 16. 19:16

The IN operator allows you to specify multiple values in a WHERE clause. It is a shortcut for multiple OR conditions.

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

Example

Return all the students from 'South Korea' and 'North Korea'

SELECT * FROM Students 
WHERE Country IN ('South Korea', 'North Korea');

When you use NOT IN operator, you return all records that are not any of the values in the list.

Example

Return all the students NOT from 'South Korea' and 'North Korea'

SELECT * FROM Restaurants 
WHERE CustomerID IN (SELECT CustomerID FROM Orders);

SELECT * FROM Students 
WHERE Country NOT IN ('South Korea', 'North Korea');

IN and NOT IN can also be used with a subquery in the WHERE clause.

Example

Return all brands that have an order in the orders table:

SELECT * FROM Brand
WHERE BrandID IN (SELECT BrandID FROM Orders);

Exercises

1. Write an SQL query to select all products from the "Products" table where the "Category" is either "Electronics" or "Appliances."

SELECT * FROM Products 
WHERE Category IN ('Electronics', 'Appliances');

2. Write an SQL query to return all employees from the "Employees" table who have NOT been assigned to any projects in the "Projects" table.

SELECT * FROM Employees 
WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM Projects);