Skip to content

IN

Example

SELECT *
FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

Output

+----+---------+-----------+---------+------------+
| ID | Name    |  Country  | City    | PostalCode |
+----+---------+-----------+---------+------------+
|  1 | Thomas  | Germany   | Berlin  | 10115      |
|  2 | Lauren  | France    | Paris   | 75001      |
|  3 | Sophie  | UK        | London  | W2 1HQ     |
+----+---------+-----------+---------+------------+

Explanation

The SQL IN keyword allows you to select records from a set of specific values. Here, it selects customers whose countries are either ‘Germany’, ‘France’ or ‘UK’.

Example

SELECT * FROM Employees
WHERE EmployeeID IN (1, 2, 3);

Output

| EmployeeID | Name     | Age |
|------------|----------|-----|
| 1          | John     | 28  |
| 2          | Jane     | 25  |
| 3          | Michael  | 32  |

Explanation

The IN operator is used in the WHERE clause to filter the records. In this example, it filters out the employees whose EmployeeID is either 1, 2, or 3. So, it basically selects records from the Employees table where the EmployeeID is in the list of specified values.

Example

SELECT * FROM Employees
WHERE DepartmentID IN (3, 4, 7);

Output

| EmployeeID | First Name | Last Name | DepartmentID |
|------------|------------|-----------|--------------|
|      1     |    John    |    Doe    |      3       |
|      4     |    Jane    |   Smith   |      4       |
|      7     |   Oliver   |   Queen   |      7       |

Explanation

The provided SQL statement retrieves all records from the ‘Employees’ table where the ‘DepartmentID’ is either 3, 4, or 7. The IN keyword in SQL is used to compare a value to a list of literal values that have been specified. In this case, it is comparing ‘DepartmentID’ to the list (3, 4, 7).

Example

SELECT *
FROM employees
WHERE department_id IN (10, 20, 30);

Output

EMPLOYEE_ID FIRST_NAME LAST_NAME   EMAIL       PHONE_NUMBER       HIRE_DATE JOB_ID       SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- ---------- -----------  ---------- ------------------ ---------- ---------- -------- ------------ ------------ -------------
200         Jennifer   Whalen      JWHALEN     515.123.4444       17-JUN-03 AD_ASST     4400                     101           10
201         Michael    Hartstein  MHARTSTE   515.123.5555       17-FEB-04 MK_MAN      13000                   100           20
202         Pat        Fay         PFAY       603.123.6666       17-AUG-05 MK_REP      6000                     201           20
114         Den        Raphaely    DRAPHEA   515.127.4561       07-DEC-02 PU_MAN      11000                   100           30
115         Alexander  Khoo        AKHOO      515.127.4562       18-MAY-03 PU_CLERK    3100                     114           30
116         Shelli     Baida       SBAIDA     515.127.4563       24-DEC-05 PU_CLERK    2900                     114           30
117         Sigal      Tobias      STOBIAS    515.127.4564       24-JUL-05 PU_CLERK    2800                     114           30
118         Guy        Himuro      GHIMURO    515.127.4565       15-NOV-04 PU_CLERK    2600                     114           30
119         Karen      Colmenares  KCOLMEN    515.127.4566       10-AUG-07 PU_CLERK    2500                     114           30

Explanation

The provided SQL query fetches a list of all employees who belong to departments with IDs 10, 20, and 30. If any employee’s department_id value is one of the provided values in the IN keyword list, that employee information is displayed in the output. If not, their information is ignored.

Example

SELECT * FROM Employees
WHERE Department IN ('Marketing', 'Sales');

Output

|EmployeeID | LastName  | FirstName | Department   |
|-----------|-----------|-----------|--------------|
|     3     |Johnson    |John       |Marketing     |
|     7     |Martinez   |Maria      |Marketing     |
|     5     |Smith      |Sarah      |Sales         |
|     8     |Lee        |Linda      |Sales         |

Explanation

The SQL query selects all fields for the records in the ‘Employees’ table where the ‘Department’ field is either ‘Marketing’ or ‘Sales’.