Non-equi joins in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a non-equi join with range condition

Given two tables Products and Discounts, what is the output of the following query?

SELECT p.ProductID, p.Price, d.DiscountRate
FROM Products p
JOIN Discounts d ON p.Price BETWEEN d.MinPrice AND d.MaxPrice
ORDER BY p.ProductID;

Tables:
Products
ProductID | Price
1 | 50
2 | 150
3 | 250

Discounts
DiscountID | MinPrice | MaxPrice | DiscountRate
1 | 0 | 100 | 0.05
2 | 101 | 200 | 0.10
3 | 201 | 300 | 0.15

SQL

SELECT p.ProductID, p.Price, d.DiscountRate
FROM Products p
JOIN Discounts d ON p.Price BETWEEN d.MinPrice AND d.MaxPrice
ORDER BY p.ProductID;

A [{"ProductID":1,"Price":50,"DiscountRate":0.05},{"ProductID":2,"Price":150,"DiscountRate":0.15},{"ProductID":3,"Price":250,"DiscountRate":0.10}]

B [{"ProductID":1,"Price":50,"DiscountRate":0.05},{"ProductID":2,"Price":150,"DiscountRate":0.10},{"ProductID":3,"Price":250,"DiscountRate":0.15}]

C [{"ProductID":1,"Price":50,"DiscountRate":0.15},{"ProductID":2,"Price":150,"DiscountRate":0.05},{"ProductID":3,"Price":250,"DiscountRate":0.10}]

D [{"ProductID":1,"Price":50,"DiscountRate":0.10},{"ProductID":2,"Price":150,"DiscountRate":0.15},{"ProductID":3,"Price":250,"DiscountRate":0.05}]

💡 Hint

Think about how BETWEEN works and matches the price ranges in Discounts.

The join condition matches each product price to the discount range it falls into. Product 1 price 50 is between 0 and 100, so discount 0.05 applies. Product 2 price 150 is between 101 and 200, so discount 0.10 applies. Product 3 price 250 is between 201 and 300, so discount 0.15 applies.

📝 Syntax

intermediate

Identify the syntax error in a non-equi join query

Which option contains a syntax error in this non-equi join query?

SELECT e.EmployeeID, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.Salary > d.MinSalary AND e.Salary < d.MaxSalary;

SQL

SELECT e.EmployeeID, d.DepartmentName
FROM Employees e
JOIN Departments d ON e.Salary > d.MinSalary AND e.Salary < d.MaxSalary;

A No syntax error, query is valid

B Missing ON keyword before join condition

C Using AND in join condition causes syntax error

D Using > and < operators in join condition is invalid

💡 Hint

Check if using AND and comparison operators in JOIN ON clause is allowed.

SQL allows complex conditions with AND and comparison operators in JOIN ON clauses. The query is syntactically correct.

❓ optimization

advanced

Optimizing a non-equi join with large tables

You have two large tables, Orders and ShippingZones. You want to join orders to shipping zones based on order weight falling within zone weight limits:

SELECT o.OrderID, sz.ZoneName
FROM Orders o
JOIN ShippingZones sz ON o.Weight >= sz.MinWeight AND o.Weight < sz.MaxWeight;

Which optimization technique will improve query performance the most?

SQL

SELECT o.OrderID, sz.ZoneName
FROM Orders o
JOIN ShippingZones sz ON o.Weight >= sz.MinWeight AND o.Weight < sz.MaxWeight;

A Create indexes on Orders.Weight and ShippingZones.MinWeight, ShippingZones.MaxWeight

B Rewrite join as a CROSS JOIN with WHERE filtering

C Add a computed column in Orders for weight range and join on equality

D Use UNION ALL to split ShippingZones into multiple queries

💡 Hint

Think about how indexes help with range queries in joins.

Indexes on the columns used in the join condition help the database quickly find matching rows for the range conditions, improving performance.

🔧 Debug

advanced

Debugging unexpected results in a non-equi join

A query joins Employees and SalaryGrades on salary ranges:

SELECT e.EmployeeID, e.Salary, sg.Grade
FROM Employees e
JOIN SalaryGrades sg ON e.Salary >= sg.MinSalary AND e.Salary <= sg.MaxSalary;

Some employees appear multiple times with different grades. What is the most likely cause?

SQL

SELECT e.EmployeeID, e.Salary, sg.Grade
FROM Employees e
JOIN SalaryGrades sg ON e.Salary >= sg.MinSalary AND e.Salary <= sg.MaxSalary;

A Missing GROUP BY clause to aggregate results

B Employees table has duplicate EmployeeID values

C JOIN condition uses >= and <= instead of > and <

D Salary ranges in SalaryGrades overlap causing multiple matches

💡 Hint

Check if salary ranges in SalaryGrades overlap.

If salary ranges overlap, an employee's salary can match multiple grades, causing multiple rows per employee.

🧠 Conceptual

expert

Understanding non-equi join behavior with NULL values

Consider the query:

SELECT a.ID, b.Category
FROM TableA a
JOIN TableB b ON a.Value > b.LowerBound AND a.Value < b.UpperBound;

If some rows in TableB have NULL in LowerBound or UpperBound, what will happen to those rows in the join result?

SQL

SELECT a.ID, b.Category
FROM TableA a
JOIN TableB b ON a.Value > b.LowerBound AND a.Value < b.UpperBound;

A Rows with NULL bounds will cause the query to fail with an error

B Rows with NULL bounds will match all rows in TableA

C Rows with NULL bounds in TableB will never match any row in TableA

D Rows with NULL bounds will match only rows in TableA where a.Value is NULL

💡 Hint

Remember how NULL behaves in comparison operations in SQL.

Any comparison with NULL results in UNKNOWN, so the join condition fails and those rows do not match.

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