SQL > SQL Commands > In

In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the WHERE clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns. The syntax for using the IN keyword is as follows:

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 characters. If there is only one value inside the parenthesis, this commend is equivalent to,

WHERE "column_name" = 'value1'

For example, we may wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,

Table Store_Information

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

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

Exercises

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

Table Users

First_NameLast_NameBirth_DateGenderJoin_Date
SophieLeeJan-05-1960FApr-05-2015
RichardBrownJan-07-1975MApr-05-2015
JamalSantoOct-08-1983MApr-09-2015
CaseyHealySep-20-1969MApr-09-2015
JillWilkesNov-20-1979FApr-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




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