Tutorials

SQL SELECT


SQL SELECT IN


IN operator in sql is used with WHERE clause to specify multiple values from a particular column. 

All the rows from the table will be fetched if the column mentioned in WHERE has values defined in IN operator.

 

IN operator can be useful where we have to use multiple OR operators.

 

Syntax for IN operator

SELECT [*|columns]
FROM table_name 
WHERE column_name IN (value1, value2,...,valueN)

Example of IN operator

 

For demonstration purpose we will use tih_Courses table.

tih_Courses
Course_ID Course_Name Professor Mode_of_Training
001 .Net Mukesh Tiwari Online and Offline
002 Java Rajneesh Shukla Online and Offline
003 ANSI SQL Rajneesh Shukla Online
004 Basics of Computer Shweta  Online
005 Blogging Tutorial Puja  Online
006 JSP Alok Pandey Online and Offline
007 Servlet Mukesh Tiwari Online and Offline

From the above table we want to fetch all the data whose Course_ID is any one of them {001, 003,005, 007}

SELECT FROM tih_Courses
WHERE Course_ID IN (001, 003, 005, 007);

On executing the above query successfully, we will get:

tih_Courses
Course_ID Course_Name Professor Mode_of_Training
001 .Net Mukesh Tiwari Online and Offline
003 ANSI SQL Rajneesh Shukla Online
005 Blogging Tutorial Puja  Online
007 Servlet Mukesh Tiwari Online and Offline

Since we used * with SELECT so all the columns are returned. If you want only particular columns then write below query.

SELECT Course_ID, Course_Name 
FROM tih_Courses
WHERE Course_ID IN (001, 003, 005, 007);

On executing the above query successfully, we will get:

tih_Courses
Course_ID Course_Name
001 .Net
003 ANSI SQL
005 Blogging Tutorial
007 Servlet

Similarly we can apply IN operator on different columns to fetch the value required.

IN can be used as an alternative to queries where multiple OR operator is required.

For example. in above example, if we used OR with WHERE then:

WHERE Course_ID =001 OR Course_ID = 003 OR Course_ID = 005 OR Course_ID = 007;

As the number of OR gets increasing the query becomes cumbersome. 

So IN is better in such cases.

 



Please Share this page
Views : 39