DATA with BARAA

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:

Customers
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

Find all customers whose ID is equal to one of the values 1, 2 or 5

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.

.
Share it !