SQL – IN, BETWEEN

SQL – IN, BETWEEN

IN –

If you can recollect how the OR operator worked in SQL, the IN operator is similar to it. However, the IN operator allows you to query based on multiple values at the same time instead of one at a time used in the OR operator.

SYNTAX –

SELECT FROM table_name WHERE column_name IN (value1, value2value3 …);



Illustration –  Consider the below student table in the database 

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1001 Brigitta Dillon blue 15 ninth
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth
1004 Sampson Hansy white 10 fourth

Query 1 –  Fetch the records from the student table whose color is either red or white.

                    SELECT * FROM student WHERE color IN (‘red’,’white’);

The same query can be written using the OR condition as –

                    SELECT * FROM student WHERE color =’red’ OR color =’white’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1003 Judy Windle red 12 sixth
1004 Sampson Hansy white 10 fourth

BETWEEN –

If you need to fetch records from a table based on the range of a column that is either numeric or text or dates, then you could use the BETWEEN operator to accomplish this. A query using the BETWEEN operator can be re-written using the >= and <= operators as well. The BETWEEN operator is inclusive of the begin and end values in the result set.

SYNTAX –

SELECT *  FROM table_name WHERE column_name BETWEEN value1 and value2;

Query 2 –  Fetch the records from the student table whose age is between 11 and 13.

                    SELECT * FROM student WHERE age BETWEEN 11 and 13;

The same query can be written using the OR condition as –

                    SELECT * FROM student WHERE age >=11 AND age <= 13;

student_id First_Name Last_Name Color Age Grade
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth

 

Leave A Reply

Your email address will not be published. Required fields are marked *