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_idFirst_NameLast_NameColorAgeGrade
1000JohnMillerred15ninth
1001BrigittaDillonblue15ninth
1002PeterPippergreen13seventh
1003JudyWindlered12sixth
1004SampsonHansywhite10fourth

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_idFirst_NameLast_NameColorAgeGrade
1000JohnMillerred15ninth
1003JudyWindlered12sixth
1004SampsonHansywhite10fourth

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_idFirst_NameLast_NameColorAgeGrade
1002PeterPippergreen13seventh
1003JudyWindlered12sixth

 

Leave A Reply

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