SQL > SQL Commands > In

The IN operator in SQL filters the result set based on a list of discrete values. The list of discrete values can be simply be listed out or is provided by a separate SELECT statement (this is called a subquery).

The IN operator is always used with the WHERE clause.

Syntax

Below is the syntax for the IN operator when the possible values are listed out directly.

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

The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or string characters. If there is only one value inside the parenthesis, this commend is equivalent to,

WHERE "column_name" = 'value1'

The syntax for the IN operator in a subquery construct is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ( [SELECT STATEMENT] );

In the above construct, the subquery statement must retrieve only one column, as the values returned from that column is what will be used to filter the results. If two or more columns are selected in the subquery, an error will result.

Please note that the IN operator cannot be used if the filtering criteria is a continuous range. For example, if we are looking for any value that is between 0 and 1, we cannot use the IN operator because it is not possible to list every possible value between 0 and 1.

Example

We use the following table for our example.

Table Store_Information

 Store_Name  Sales  Txn_Date 
 Los Angeles  1500  Jan-05-1999 
 San Diego  250  Jan-07-1999 
 San Francisco  300  Jan-08-1999 
 Boston  700  Jan-08-1999 

To select all records for the Los Angeles and the San Diego stores in Table Store_Information, we key in,

SELECT *
FROM Store_Information
WHERE Store_Name IN ('Los Angeles', 'San Diego');

Result:

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999

For an example of the IN operator used in conjunction with a separate SELECT statement, please see the Subquery section.

Exercises

For these exercises, assume we have a table called Users with the following data:

Table Users

 First_Name  Last_Name  Birth_Date  Gender  Join_Date 
 Sophie  Lee  Jan-05-1960  F  Apr-05-2015 
 Richard  Brown  Jan-07-1975  M  Apr-05-2015 
 Jamal  Santo  Oct-08-1983  M  Apr-09-2015 
 Casey  Healy  Sep-20-1969  M  Apr-09-2015 
 Jill  Wilkes  Nov-20-1979  F  Apr-15-2015 

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT Gender FROM Users IN ('M');
b) SELECT * FROM Users HAVING Gender IN ('M','F');
c) SELECT First_Name, Last_Name FROM Users WHERE Gender IN ('M','F');
d) SELECT DISTINCT First_Name, Last_Name WHERE Gender IN ('M','F');

2. How many records will be returned by the following query?
SELECT * FROM Users WHERE Join_Date IN ('Apr-05-2015','Apr-15-2015');

3. How many records will be returned by the following query?
SELET * FROM Users WHERE Gender IN ('M') AND Join_Date = 'Apr-15-2015');

Next: SQL BETWEEN

This page was last updated on June 19, 2023.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact