You are currently viewing SQL 1.67 SQL BETWEEN Keyword

SQL 1.67 SQL BETWEEN Keyword

SQL BETWEEN Keyword Tutorial

The BETWEEN keyword in SQL is used to filter the result set within a certain range. This tutorial will guide you through understanding the BETWEEN keyword with practical examples and detailed explanations. By the end of this tutorial, you’ll be able to use BETWEEN to query ranges in your SQL databases effectively.

What is SQL BETWEEN Keyword?

The BETWEEN keyword is used in a WHERE clause to filter the result set within a specific range. The range can be numbers, text, or dates. The syntax for BETWEEN is:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here, value1 and value2 define the range. The BETWEEN operator is inclusive, meaning that it includes value1 and value2.

Example Scenarios

Let’s consider a table Employees with the following columns and data:

EmployeeIDFirstNameLastNameAgeSalaryHireDate
1JohnDoe25500002021-03-15
2JaneSmith30600002019-07-22
3EmilyJohnson45800002015-05-30
4MichaelBrown38750002017-10-10
5SarahDavis28550002020-01-19

1. Using BETWEEN with Numbers

Scenario: Retrieve all employees whose age is between 30 and 40.

SELECT FirstName, LastName, Age
FROM Employees
WHERE Age BETWEEN 30 AND 40;

Explanation:

  • SELECT FirstName, LastName, Age – This selects the columns FirstName, LastName, and Age from the Employees table.
  • FROM Employees – This specifies the table from which to retrieve the data.
  • WHERE Age BETWEEN 30 AND 40 – This filters the results to include only those employees whose age is between 30 and 40, inclusive.

Output:

FirstNameLastNameAge
JaneSmith30
MichaelBrown38

2. Using BETWEEN with Dates

Scenario: Retrieve all employees who were hired between ‘2017-01-01’ and ‘2020-12-31’.

SELECT FirstName, LastName, HireDate
FROM Employees
WHERE HireDate BETWEEN '2017-01-01' AND '2020-12-31';

Explanation:

  • SELECT FirstName, LastName, HireDate – This selects the columns FirstName, LastName, and HireDate from the Employees table.
  • FROM Employees – This specifies the table from which to retrieve the data.
  • WHERE HireDate BETWEEN '2017-01-01' AND '2020-12-31' – This filters the results to include only those employees who were hired between January 1, 2017, and December 31, 2020, inclusive.

Output:

FirstNameLastNameHireDate
MichaelBrown2017-10-10
SarahDavis2020-01-19

3. Using BETWEEN with Strings

Scenario: Retrieve all employees whose last names are alphabetically between ‘Davis’ and ‘Johnson’.

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'Davis' AND 'Johnson';

Explanation:

  • SELECT FirstName, LastName – This selects the columns FirstName and LastName from the Employees table.
  • FROM Employees – This specifies the table from which to retrieve the data.
  • WHERE LastName BETWEEN 'Davis' AND 'Johnson' – This filters the results to include only those employees whose last names fall alphabetically between ‘Davis’ and ‘Johnson’, inclusive.

Output:

FirstNameLastName
EmilyJohnson
SarahDavis

4. Using NOT BETWEEN

Scenario: Retrieve all employees whose salary is not between 55000 and 75000.

SELECT FirstName, LastName, Salary
FROM Employees
WHERE Salary NOT BETWEEN 55000 AND 75000;

Explanation:

  • SELECT FirstName, LastName, Salary – This selects the columns FirstName, LastName, and Salary from the Employees table.
  • FROM Employees – This specifies the table from which to retrieve the data.
  • WHERE Salary NOT BETWEEN 55000 AND 75000 – This filters the results to include only those employees whose salary is not between 55000 and 75000, inclusive.

Output:

FirstNameLastNameSalary
JohnDoe50000
EmilyJohnson80000

Step-by-Step Instructions

  1. Identify the Range Criteria:
  • Determine the column and the range you want to filter by. For example, if you’re filtering by age, decide the lower and upper bounds for the range.
  1. Write the Basic Query:
  • Start with a basic SELECT statement.
   SELECT * FROM Employees;
  1. Add the WHERE Clause with BETWEEN:
  • Incorporate the BETWEEN clause into the WHERE statement.
   SELECT * FROM Employees WHERE Age BETWEEN 30 AND 40;
  1. Specify the Columns to Select:
  • Modify the SELECT statement to include only the columns you need.
   SELECT FirstName, LastName, Age FROM Employees WHERE Age BETWEEN 30 AND 40;
  1. Run and Test the Query:
  • Execute the query and verify the results. Adjust the range if necessary.

Conclusion

The BETWEEN keyword is a powerful tool in SQL for filtering data within a range. Whether dealing with numbers, dates, or strings, BETWEEN provides a straightforward way to define these ranges. Remember that BETWEEN is inclusive, meaning it includes the start and end values specified.

Practice using BETWEEN with different data types and ranges to become more comfortable with its functionality. With these examples and explanations, you should be well on your way to mastering the use of the BETWEEN keyword in SQL.

Leave a Reply