Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
SQL IN and SQL BETWEEN
The IN and BETWEEN operators are used to filter query results based on a set of values or a range of values, respectively. They help simplify queries by allowing you to specify multiple values or ranges in a concise manner.
SQL IN
Purpose: The IN operator is used to filter results based on a set of values. It is often used in the WHERE clause to match a column’s value against a list of possible values.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
Example 1:
Find Employees with Names Starting with ‘A’
1 Find Employees in Specific Departments:
This query retrieves the employees who work in either the ‘Sales’ or ‘IT’ departments.
SELECT employee_id, first_name, department
FROM employees
WHERE department IN (‘Sales’, ‘IT’);
Output:
employee_id | first_name | department |
3 | Rajesh | Sales |
7 | Anil | IT |
9 | Priya | Sales |
2 Get Products with Certain IDs:
This query selects products with IDs 101, 203, and 305.
SELECT product_id, product_name
FROM products
WHERE product_id IN (101, 203, 305);
Output:
product_id | product_name |
101 | Sports Shoe |
203 | Running Shoes |
305 | Formal Shoes |
3 Find Customers from Specific Cities:
This query retrieves customers who live in ‘Mumbai’ or ‘Delhi’.
SELECT customer_id, name, city
FROM customers
WHERE city IN (‘Mumbai’, ‘Delhi’);
Output:
customer_id | name | city |
2 | Aarti | Mumbai |
5 | Ravi | Delhi |
4 Get Employees with Specific Job Titles:
This query finds employees with job titles ‘Manager’, ‘Analyst’, or ‘Developer’.
SELECT employee_id, job_title
FROM employees
WHERE job_title IN (‘Manager’, ‘Analyst’, ‘Developer’);
Output:
employee_id | job_title |
1 | Manager |
4 | Analyst |
8 | Developer |
SQL BETWEEN
Purpose: The BETWEEN operator is used to filter results within a specified range. It is often used for numeric, date, or time data types.
Syntax:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Examples:
1 Find Employees with Salaries in a Range:
This query retrieves employees whose salaries are between 30,000 and 50,000.
SELECT employee_id, first_name, salary
FROM employees
WHERE salary BETWEEN 30000 AND 50000;
Output:
employee_id | first_name | salary |
5 | Priya | 35000 |
7 | Anil | 45000 |
2 Get Products with Prices Within a Range:
This query selects products priced between 100 and 500.
SELECT product_id, product_name, price
FROM products
WHERE price BETWEEN 100 AND 500;
Output:
product_id | product_name | price |
102 | Smart Watch | 300 |
204 | Smart Phone | 450 |
3 Find Orders Placed Within a Date Range:
This query retrieves orders placed between ‘2023-01-01’ and ‘2023-12-31’.
SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;
Output:
order_id | order_date |
1001 | 2023-05-15 |
1002 | 2023-11-22 |
4 Get Employees with Experience Between 3 and 7 Years:
This query selects employees who have between 3 and 7 years of experience.
SELECT employee_id, first_name, experience
FROM employees
WHERE experience BETWEEN 3 AND 7;
Output:
employee_id | first_name | experience |
2 | Ravi | 4 |
6 | Aarti | 6 |
Tips to Remember
- Using IN for Multiple Values: The IN operator is useful when you need to match a column against multiple discrete values.
- Using BETWEEN for Ranges: The BETWEEN operator is effective for filtering data within a specific range, including both endpoints.
- Ordering with BETWEEN: The order of values in BETWEEN does not matter (i.e., value1 BETWEEN value2 AND value3 is equivalent to value2 BETWEEN value1 AND value3).
- Inclusive Range: BETWEEN is inclusive, meaning it includes the boundary values specified.
Power of IN and BETWEEN
- Simplify Queries: Both operators simplify query writing by reducing the complexity of multiple conditions.
- Improve Readability: They make queries more readable and easier to understand.
- Enhance Filtering: They enhance the ability to filter data based on a set of values or a range, facilitating more flexible data analysis.
Frequently Asked Questions
Q1: Can I use IN with subqueries?
A1: Yes, you can use IN with a subquery to filter results based on values returned by another query.
Example:
SELECT employee_id, first_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = ‘IT’);
Q2: Can I use BETWEEN with text data?
A2: Yes, BETWEEN can be used with text data, though it’s more common with numeric and date values. It works based on alphabetical order.
Q3: How do I exclude specific values when using IN?
A3: Use the NOT IN operator to exclude specific values.
Example:
SELECT employee_id, first_name
FROM employees
WHERE department NOT IN (‘Sales’, ‘IT’);
Q4: Can BETWEEN be used with time data types?
A4: Yes, BETWEEN can be used with time data types to filter results within a specific time range.
Example:
SELECT event_name, event_time
FROM events
WHERE event_time BETWEEN ’08:00:00′ AND ’18:00:00′;
Q5: Are IN and BETWEEN inclusive of boundary values?
A5: Yes, both operators are inclusive. IN includes the specified values, and BETWEEN includes both the lower and upper boundary values.
