In SQL | In Command In SQL - sql - sql tutorial - learn sql
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialDefine IN COMMAND?
Define IN COMMAND?
- The SQL IN condition (sometimes called the IN operator) allows you to easily test if an expression matches any value in a list of values.
- It is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.
COMMAND IN SQL
- 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
- 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'SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ( [SELECT STATEMENT] );
- 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 product_Information
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
| Los Angeles | 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 product_Information
WHERE product_Name IN ('Los Angeles', 'San Diego');
Result:
| Store_Name | Sales | Txn_Date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-1999 |
| San Diego | 250 | Jan-07-1999 |
- For an example of the IN operator used in conjunction with a separate SELECT statement, please see the Subquery section.
- For these exercises, assume we have a table called Users with the following data:
Table wikitechy 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 |
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialInterview questions for In command in SQL:
Interview questions for In command in SQL:
- 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');
- Answer: c)
- How many records will be returned by the following query?
SELECT * FROM Users WHERE Join_Date IN ('Apr-05-2015','Apr-15-2015');
- Answer: 3 records are returned. They are,
| 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 |
| Jill | Wilkes | Nov-20-1979 | F | Apr-15-2015 |
- How many records will be returned by the following query?
SELET * FROM Users WHERE Gender IN ('M') AND Join_Date = 'Apr-15-2015');
- Answer: 0 record is returned. There is no record that satisfies the WHERE condition.
SQL IN Example
