SQL Basics
Querying Data
Filtering Data
Joining Tables
SQL Functions
Modifying Data
Defining Data
SQL IN
In this tutorial, you will learn how to check if a value is in a set of values using the SQL operator IN within the WHERE clause.
| AND | Return True if both conditions are True |
| OR | Return True if one of conditions is True |
| NOT | Reverse the result of any Boolean operator |
| IN | Return True if a value is in a set of values |
| BETWEEN | Return True if a value falls within a specific range |
| LIKE | Return True if a value matches a pattern |
The IN is one of the logical operators in SQL that allows you to specify a list of values that you’d like to include in the results.
The IN operator returns TRUE if the value is in a set of values or FALSE otherwise
- IN returns values that match values in a list.
- This list is either hardcoded or generated by a subquery.
- IN is a shortcut for multiple OR conditions.
Syntax
The basic syntax of the IN clause to check if a value exists within a set of values can be given with:
SELECT column_names
FROM table_name
WHERE column_name IN (value1,value2,...)
Technically, you get the same results if you replace the above syntax with the (=) and OR operators.
SELECT column_names
FROM table_name
WHERE column_name = value1 OR column_name = value2 OR ...
Examples
To understand the IN statement in a better way, let’s look at the following customers table in our tutorial database:
| customer_id | first_name | last_name | country | score |
|---|---|---|---|---|
| 1 | Maria | Cramer | Germany | 350 |
| 2 | John | Steel | USA | 900 |
| 3 | Georg | Pipps | UK | 750 |
| 4 | Martin | Müller | Germany | 500 |
| 5 | Peter | Franken | USA | NULL |
Now, let’s check out some examples that demonstrate how it actually works.
We have the following task be to solve using SQL statements
The following SQL statement will returns all customers from customers table and order the result by the score column in ascending order.
You can have the same the result set by skipping the ASC, because it is the default option in ORDER BY.
SELECT *
FROM customers
WHERE customer_id = 1 OR customer_id = 2 OR customer_id = 5
SELECT *
FROM customers
WHERE customer_id IN (1,2,5)
After executing the above query, you’ll get the result set something like this:
As you can see the output contains everything the whole customers tables including all rows and columns.
Similarly, you can use the DESC option to perform a sorting in descending order. The following statement will orders the result set by the numeric salary column in descending order.
Once you specify multiple columns after ORDER BY, the Database will sort the result by the first column, then the new ordered list will be sorted again by next column.
