- 4 years ago
- Zaid Bin Khalid
- 2170 Views
-
3
In this session, you will learn how to use IN and BETWEEN operators with WHERE clause.
Working with Range and Membership Conditions.
In the previous session, we have learned how to combine multiple conditions using the AND and OR operators.
Most of the time this is not very useful and productive, for instance, if you have to find out the set of values or ranges that lies within ranges.
Then you can use the BETWEEN statement or you can also use the less than and greater than operators.
The IN Operator.
The IN operator work as a logical operator that is used to determine a unique value. And discover the value that either exists in a set or not.
IN Operator Syntax.
The primary IN operator syntax can be termed below as.
SELECT column_list FROM table_name WHERE column_name IN (value1, value1,...);
Let us assume, we have an employee table within our database that has the following records.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 1 | Ethan Hunt | 2001-05-01 | 5000 | 4 |
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
+--------+--------------+------------+--------+---------+
The BETWEEN Operator.
It’s common when you are working with numeric data. Sometimes, you want to select a row if the value in a column falls within a specific range.
You might utilize the BETWEEN operator while executing the query based on such conditions. Between Operator is a logical operator that enables you to specify a range to test. Such as:
SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;
On the range condition of our Employees Table, let us perform while building the queries
Define Numeric Ranges.
The stated below SQL statement will execute the specific employee data based upon salary from the employees table. So, it will display results of employees whose salary range falls within the range of 7000 and 9000.
SELECT * FROM employees WHERE salary BETWEEN 7000 AND 9000;
The execution result of the above query will display as.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
+--------+--------------+------------+--------+---------+
Define Date Ranges.
While using the BETWEEN operator with a date or time values, one might use the CAST ( ) function. This function explicitly converts the values to the desired data type to display the best results.
The mention below SQL statement will help you to find out only those employees from the employee’s table who hired between 1st January 2006 (“e.g.,” ‘2006-01-01’) and 31st December 2016 (“e.g.,” i.e., ‘2016-12-31’):
SELECT * FROM employees WHERE hire_date
BETWEEN CAST('2006-01-01' AS DATE) AND CAST('2016-12-31' AS DATE);
The execution result of the above query will display as.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
| 5 | Martin Blank | 2008-06-24 | 5600 | NULL |
+--------+--------------+------------+--------+---------+
Define String Ranges.
Now, if you want to apply the condition, you can easily use which help you to search out a string of ranges. The ranges of dates and numbers are the most common. The following SQL statement selects all the employees whose name begins with any of the letters between ‘O’ and ‘Z.’
SELECT * FROM employees WHERE emp_name BETWEEN 'O' AND 'Z';
The execution result of the above query will display as.
+--------+--------------+------------+--------+---------+
| emp_id | emp_name | hire_date | salary | dept_id |
+--------+--------------+------------+--------+---------+
| 2 | Tony Montana | 2002-07-15 | 6500 | 1 |
| 3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
| 4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
+--------+--------------+------------+--------+---------+
- 4 years ago
- Zaid Bin Khalid
- 2170 Views
-
3