SQL IN

 

SQL IN


Summary: in this tutorial, you will learn how to use the SQL IN operator to compare values in a column against a set of values.

Introduction to SQL IN Operator

The IN the operator is a logical operator that allows you to compare a value against a set of values. The IN operator returns true if the value is within the set of values. Otherwise, it returns false or unknown.

The following illustrates the syntax of the IN operator:

expression IN (value1,value2,...)

The IN operator is often used in the  WHERE clause of the SELECTUPDATE, and DELETE statements. It is also used extensively in subqueries.

The condition that uses the IN the operator can be rewritten using one or more OR operators as follows:

expression = value1 OR expression = value2 OR ...

To negate the result of the IN operator, you use the NOT operator:

expression NOT IN (value1, value2,...)

The NOT IN the operator returns true if the expression does not match any value in the list, otherwise, it returns false.

Similarly, you can rewrite the NOT IN operator by using the AND operators as shown below:

expression != value1 AND expression != value2 AND...

Notice that if any value in the list (value1,value2,...) is null, the IN the operator returns no rows.

SQL IN examples

We will use the  employees the table in the sample database to demonstrate the functionality of the  IN operator.

employees_table

To find all employees whose job’s id is either 8, 9, or 10, you use the IN operator as follows:

SELECT employee_id, first_name, last_name, job_id FROM employees WHERE job_id IN (8, 9, 10) ORDER BY job_id;
SQL IN operator example

To query an employee whose job’s id is neither 7, 8, nor 9, you use the NOT IN operator as the following statement:

SELECT employee_id, first_name, last_name, job_id FROM employees WHERE job_id NOT IN (7, 8, 9) ORDER BY job_id;
SQL NOT IN operator example

SQL IN with subquery examples

subquery is a query nested inside another query. The following query finds department id of the Marketing and Sales departments:

SELECT department_id FROM departments WHERE department_name = 'Marketing' OR department_name = 'Sales'
SQL IN subquery example

The query returns a list of two departments. You can supply this list to the IN operator to find all employees who work in the Marketing and Sales departments.

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id IN (2, 8);

To make it more simple, you can use the  subquery technique that nests the first query inside the second query:

SELECT employee_id, first_name, last_name, salary FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Marketing' OR department_name = 'Sales')

In this tutorial, you have learned how to use the SQL  IN operator to test whether a value is within a list of values or subquery.

Reactions

Post a Comment

0 Comments