MySQL - Leveraging Inner Join on Between Condition

In this article, we will illustrate the application of INNER JOIN using the BETWEEN condition through a practical real-world scenario.

Sample Data to Demonstrate Inner Join on Between Condition

You can use this DB Fiddle to follow along and practice inner join on between condition

Let's assume you have the following AgeCategory table and Customer tables:

AgeCategory table:

CategoryID	CategoryName	AgeLowThreshold	AgeHighThreshold
1	Child	0	12
2	Teen	13	19
3	Young Adult	20	35
4	Adult	36	60
5	Senior	61	150

Customer table:

CustomerID	FirstName	LastName	DateOfBirth
1	Alice	Johnson	2011-08-07
2	Bob	Smith	2008-08-07
3	Charlie	Williams	1998-08-07
4	David	Brown	1983-08-07
5	Eva	Jones	1953-08-07

Inner Join on Between Condition

The following SQL code snippet demonstrates how to calculate the age of customers and categorize them into age groups using the WITH clause, and then perform an INNER JOIN on the age category:

1. A Common Table Expression (CTE) named CustomerAge is defined. This CTE calculates the age of customers based on their DateOfBirth using the TIMESTAMPDIFF function. It also subtracts 1 from the age if the customer's birthday hasn't occurred yet this year. The resulting CTE includes columns: CustomerID, FirstName, LastName, DateOfBirth, and Age.

2. A SELECT statement follows, where data is retrieved from the CustomerAge CTE and joined with the AgeCategory table.

3. The SELECT statement fetches columns from the CTE (ca) and the AgeCategory table (ac): CustomerID, FirstName, LastName, DateOfBirth, CategoryName, and Age.

4. An INNER JOIN is performed between the CustomerAge CTE and the AgeCategory table. The join condition uses the BETWEEN keyword to match the customer's age (Age from the CTE) with the age thresholds in the AgeCategory table (AgeLowThreshold and AgeHighThreshold).

In summary, this query generates a report that presents customers' basic information, their calculated age, and the corresponding age category they fall into based on the defined age thresholds in the AgeCategory table. The WITH clause streamlines the calculation of age, making it more readable and reusable throughout the query. The INNER JOIN connects the age data with the predefined age categories, allowing for a comprehensive view of customer demographics.

INNER JOIN on BETWEEN Condition to match the customer's age with the age thresholds

WITH CustomerAge AS (
    SELECT
        CustomerID,
        FirstName,
        LastName,
        DateOfBirth,
        TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) - CASE
            WHEN DATE_ADD(DateOfBirth, INTERVAL TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) YEAR) > CURDATE()
                THEN 1
            ELSE 0
        END AS Age
    FROM Customer
)
SELECT
    ca.CustomerID,
    ca.FirstName,
    ca.LastName,
    ca.DateOfBirth,
    ac.CategoryName,
    ca.Age
FROM CustomerAge ca
INNER JOIN AgeCategory ac ON ca.Age BETWEEN ac.AgeLowThreshold AND ac.AgeHighThreshold;

CustomerID	FirstName	LastName	DateOfBirth	CategoryName	Age
1	Alice	Johnson	2011-08-07	Child	12
2	Bob	Smith	2008-08-07	Teen	15
3	Charlie	Williams	1998-08-07	Young Adult	25
4	David	Brown	1983-08-07	Adult	40
5	Eva	Jones	1953-08-07	Senior	70