Non-equi joins in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a non-equi join in SQL?

A non-equi join is a type of join where the join condition uses operators other than '=' such as <, >, <=, >=, or <>. It matches rows based on a range or inequality condition instead of exact equality.

beginner

Which SQL operators are commonly used in non-equi joins?

Operators like <, >, <=, >=, and <> (not equal) are commonly used in non-equi joins to compare columns with inequalities.

intermediate

Why might you use a non-equi join instead of an equi join?

You use a non-equi join when you want to match rows based on ranges or inequalities, such as finding which price range a product falls into or matching dates within a period, rather than exact matches.

intermediate

Example: How would you join two tables where one table's value is between two columns of another table?

You can write a join condition like: ON table1.value >= table2.range_start AND table1.value <= table2.range_end. This is a non-equi join using inequalities.

intermediate

Can non-equi joins be used with INNER JOIN, LEFT JOIN, or other join types?

Yes, non-equi join conditions can be used with INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN. The difference is only in the join condition, not the join type.

Which of the following is an example of a non-equi join condition?

A table1.value > table2.min_value

B table1.id = table2.id

C table1.name = table2.name

D table1.date = table2.date

Option A uses a greater than operator (>), which is a non-equi join condition. The others use equality (=).

What is a common use case for non-equi joins?

A Matching rows with exact IDs

B Matching rows based on ranges or intervals

C Combining tables without any condition

D Filtering rows with NULL values

Non-equi joins are used to match rows based on ranges or intervals, such as date ranges or numeric ranges.

Which SQL clause typically contains the non-equi join condition?

A WHERE

B GROUP BY

C ORDER BY

D ON

The ON clause of a JOIN contains the join condition, including non-equi conditions.

Can you use non-equi join conditions with LEFT JOIN?

A Only with INNER JOIN

B No

C Yes

D Only with CROSS JOIN

Non-equi join conditions can be used with any join type, including LEFT JOIN.

Which operator is NOT used in non-equi joins?

A =

B <

C >=

D <>

The '=' operator is used in equi joins, not non-equi joins.

Explain what a non-equi join is and give a simple example of when you might use it.

Think about joining tables where values fall between ranges instead of matching exactly.

Describe how non-equi joins differ from equi joins and why that difference matters.

Focus on the type of comparison used in the join condition.