Non-equi joins in SQL - Mini Project: Build & Apply

Using Non-equi Joins in SQL

📖 Scenario: You work for a retail company that wants to categorize products based on their price ranges. The company has a table of products with their prices and a separate table defining price categories with minimum and maximum price limits.Your task is to join these two tables to assign each product to the correct price category using a non-equi join.

🎯 Goal: Create a SQL query that uses a non-equi join to match each product with the correct price category based on its price.

📋 What You'll Learn

Create a products table with columns product_id, product_name, and price.

Create a price_categories table with columns category_id, category_name, min_price, and max_price.

Write a SQL query that joins products and price_categories using a non-equi join condition on price between min_price and max_price.

Select product_name and category_name in the final output.

💡 Why This Matters

🌍 Real World

Retail companies often categorize products by price ranges to help customers filter and compare items easily.

💼 Career

Understanding non-equi joins is important for data analysts and database developers who work with complex data relationships that are not simple equals.

1

Create the products table and insert data

Write SQL statements to create a table called products with columns product_id (integer), product_name (text), and price (integer). Then insert these exact rows: (1, 'Laptop', 1200), (2, 'Smartphone', 800), (3, 'Tablet', 400), (4, 'Headphones', 150).

SQL

-- Create the products table and insert data
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Create the price_categories table and insert data

Write SQL statements to create a table called price_categories with columns category_id (integer), category_name (text), min_price (integer), and max_price (integer). Then insert these exact rows: (1, 'Budget', 0, 300), (2, 'Midrange', 301, 900), (3, 'Premium', 901, 2000).

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

-- Create the price_categories table and insert data
-- Your code here

Hint

Define the price_categories table with the four columns and insert the given rows.

3

Write the non-equi join query

Write a SQL SELECT query that joins products and price_categories using a non-equi join condition where products.price is between price_categories.min_price and price_categories.max_price. Select product_name and category_name in the output.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

CREATE TABLE price_categories (
    category_id INTEGER,
    category_name TEXT,
    min_price INTEGER,
    max_price INTEGER
);

INSERT INTO price_categories (category_id, category_name, min_price, max_price) VALUES
(1, 'Budget', 0, 300),
(2, 'Midrange', 301, 900),
(3, 'Premium', 901, 2000);

-- Write the SELECT query with non-equi join below
-- Your code here

Hint

Use JOIN with the ON clause using the BETWEEN operator for the non-equi join.

4

Complete the query with ordering

Add an ORDER BY clause to the query to sort the results by product_name in ascending order.

SQL

CREATE TABLE products (
    product_id INTEGER,
    product_name TEXT,
    price INTEGER
);

INSERT INTO products (product_id, product_name, price) VALUES
(1, 'Laptop', 1200),
(2, 'Smartphone', 800),
(3, 'Tablet', 400),
(4, 'Headphones', 150);

CREATE TABLE price_categories (
    category_id INTEGER,
    category_name TEXT,
    min_price INTEGER,
    max_price INTEGER
);

INSERT INTO price_categories (category_id, category_name, min_price, max_price) VALUES
(1, 'Budget', 0, 300),
(2, 'Midrange', 301, 900),
(3, 'Premium', 901, 2000);

SELECT product_name, category_name
FROM products
JOIN price_categories ON products.price BETWEEN price_categories.min_price AND price_categories.max_price
-- Add ORDER BY clause below
-- Your code here

Hint

Use ORDER BY product_name ASC to sort the results alphabetically by product name.

Practice

1. What is a non-equi join in SQL?

easy

A. A join that uses conditions other than equality, like <, >, or BETWEEN.

B. A join that only matches rows with equal values in both tables.

C. A join that combines all rows from both tables regardless of condition.

D. A join that uses only the AND logical operator in the ON clause.

Solution

1. Step 1: Understand join conditions

   Equi joins use equality (=) to match rows. Non-equi joins use other operators like <, >, or BETWEEN.

2. Step 2: Identify non-equi join definition

   Since non-equi joins match rows based on inequalities or ranges, A join that uses conditions other than equality, like <, >, or BETWEEN. correctly describes this.

3. Final Answer:

   A join that uses conditions other than equality, like <, >, or BETWEEN. -> Option A

4. Quick Check:

   Non-equi join = condition other than = [OK]

Hint: Non-equi joins use <, >, or BETWEEN, not just = [OK]

Common Mistakes:

• Confusing non-equi join with equi join
• Thinking non-equi join matches all rows
• Assuming only AND operator defines non-equi join

2. Which of the following is the correct syntax for a non-equi join using BETWEEN?

easy

A. SELECT * FROM A JOIN B ON A.value IN BETWEEN B.min AND B.max;

B. SELECT * FROM A JOIN B ON A.value = BETWEEN B.min AND B.max;

C. SELECT * FROM A JOIN B ON BETWEEN A.value AND B.min AND B.max;

D. SELECT * FROM A JOIN B ON A.value BETWEEN B.min AND B.max;

Solution

1. Step 1: Recall BETWEEN syntax

   BETWEEN is used as: column BETWEEN low AND high, without extra operators.

2. Step 2: Check each option

   SELECT * FROM A JOIN B ON A.value BETWEEN B.min AND B.max; uses correct syntax: A.value BETWEEN B.min AND B.max. Others misuse BETWEEN or add extra operators.

3. Final Answer:

   SELECT * FROM A JOIN B ON A.value BETWEEN B.min AND B.max; -> Option D

4. Quick Check:

   BETWEEN syntax = column BETWEEN low AND high [OK]

Hint: BETWEEN syntax: column BETWEEN low AND high, no extra operators [OK]

Common Mistakes:

• Adding = before BETWEEN
• Using IN BETWEEN instead of BETWEEN
• Placing BETWEEN incorrectly in ON clause

3. Given tables Products(product_id, price) and Discounts(min_price, max_price, discount_rate), what does this query return?

SELECT p.product_id, d.discount_rate
FROM Products p
JOIN Discounts d ON p.price >= d.min_price AND p.price < d.max_price;

medium

A. Only products with price exactly equal to min_price or max_price.

B. All products joined with all discounts regardless of price.

C. All products with their matching discount rate based on price ranges.

D. Syntax error due to invalid join condition.

Solution

1. Step 1: Analyze join condition

   The join matches products where price is between min_price (inclusive) and max_price (exclusive).

2. Step 2: Understand result

   This returns products with their discount rate if their price falls in the discount's price range.

3. Final Answer:

   All products with their matching discount rate based on price ranges. -> Option C

4. Quick Check:

   Non-equi join matches price ranges = All products with their matching discount rate based on price ranges. [OK]

Hint: Non-equi join matches ranges using >= and < [OK]

Common Mistakes:

• Thinking only exact matches are returned
• Assuming all products join with all discounts
• Believing the query has syntax errors

4. Identify the error in this non-equi join query:

SELECT e.name, s.salary_grade
FROM Employees e
JOIN SalaryGrades s ON e.salary => s.min_salary AND e.salary <= s.max_salary;

medium

A. The join condition should use OR instead of AND.

B. The operator => is invalid; it should be >=.

C. The table alias 's' is missing in the SELECT clause.

D. The query is missing a WHERE clause.

Solution

1. Step 1: Check operators in join condition

   The operator => is not valid SQL; the correct operator for 'greater than or equal' is >=.

2. Step 2: Verify other parts

   AND is correct to check salary between min and max. Aliases and WHERE clause are not errors here.

3. Final Answer:

   The operator => is invalid; it should be >=. -> Option B

4. Quick Check:

   Use >=, not => for greater or equal [OK]

Hint: Use >=, not =>, for greater or equal operator [OK]

Common Mistakes:

• Typing => instead of >=
• Replacing AND with OR incorrectly
• Confusing alias usage in SELECT

5. You have a table Scores(student_id, score) and a table Grades(grade, min_score, max_score). Write a query to assign each student their grade based on their score using a non-equi join.
Which query correctly implements this?

hard

A. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score;

B. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score <= g.min_score AND s.score >= g.max_score;

C. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score > g.min_score AND s.score <= g.max_score;

D. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score BETWEEN g.min_score AND g.max_score;

Solution

1. Step 1: Understand grading ranges

   Grades are assigned where score is between min_score (inclusive) and max_score (exclusive) to avoid overlap.

2. Step 2: Check each join condition

   SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score; uses s.score >= g.min_score AND s.score < g.max_score, correctly defining non-overlapping ranges.

3. Step 3: Verify other options

   SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score BETWEEN g.min_score AND g.max_score; includes max_score in BETWEEN (inclusive), which may cause overlap. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score > g.min_score AND s.score <= g.max_score; reverses inclusivity. SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score <= g.min_score AND s.score >= g.max_score; reverses logic incorrectly.

4. Final Answer:

   SELECT s.student_id, g.grade FROM Scores s JOIN Grades g ON s.score >= g.min_score AND s.score < g.max_score; -> Option A

5. Quick Check:

   Use >= min and < max for non-overlapping ranges [OK]

Hint: Use >= min_score and < max_score for clean grade ranges [OK]

Common Mistakes:

• Using BETWEEN which includes max_score causing overlap
• Swapping < and > operators
• Using incorrect inclusivity causing duplicate grades