Understanding the 'BETWEEN' Keyword in SQL

Introduction

In this presentation, we focus on the SQL 'BETWEEN' keyword, building upon previous knowledge of the 'IN' keyword in SQL.

Purpose of 'BETWEEN'

• Simplifies the retrieval of values within a specified range.
• Mimics English-like statements, making SQL queries easy to write and understand.

Functionality

• 'BETWEEN' is used with the 'AND' keyword to specify ranges.
• Supports various data types, including numbers, text, and dates.
• Includes both starting and ending values in the range.

Syntax

SELECT column1, column2, ..., columnN
FROM table_name
WHERE column BETWEEN value1 AND value2;

Example

SELECT * FROM student
WHERE age BETWEEN 10 AND 20;

• Retrieves student records with ages from 10 to 20, including both 10 and 20.

Usage Scenarios

1. Numeric Range:

   • Example:

     SELECT * FROM employee
     WHERE salary BETWEEN 40000 AND 50000;
     

   • Includes salaries from 40,000 to 50,000.

2. Logical 'AND' Combinations:

   • Allows combining multiple conditions.
   • Example:

     SELECT * FROM employee
     WHERE salary BETWEEN 40000 AND 50000
     AND department = 'HR';
     

3. Negating with 'NOT BETWEEN':

   • Example:

     SELECT * FROM employee
     WHERE salary NOT BETWEEN 40000 AND 50000;
     

4. String Range:

   • Example for text:

     SELECT * FROM employee
     WHERE city BETWEEN 'Ahmedabad' AND 'Chennai';
     

   • Retrieves cities alphabetically between Ahmedabad and Chennai, inclusive.

Ordering Results

• Use 'ORDER BY' to sort results.
• Example:

  SELECT * FROM employee
  WHERE city BETWEEN 'Ahmedabad' AND 'Chennai'
  ORDER BY city ASC;
  

This results in cities sorted in ascending order.

Conclusion

• 'BETWEEN' is a powerful, intuitive SQL keyword for selecting data within inclusive ranges across various data types.
• It integrates seamlessly with 'AND' for additional logical conditions and supports sorting operations with 'ORDER BY'.

Thank you for watching.