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 tutorial

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 tutorial

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

SQL IN Example

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to In SQL|In Command In SQL