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.