Non-equi joins in SQL - Step-by-Step Execution

Concept Flow - Non-equi joins

Start with two tables

↓

Apply join condition

↓

Check if condition is non-equality (>, <, >=, <=, !=)

Yes↓

Match rows based on condition

↓

Return combined rows

↓

End

Non-equi joins combine rows from two tables based on conditions other than equality, like greater than or less than.

Execution Sample

SQL

SELECT A.id, B.value
FROM A
JOIN B ON A.score > B.threshold;

This query joins tables A and B where A's score is greater than B's threshold.

Execution Table

Step	Row from A	Row from B	Condition (A.score > B.threshold)	Join Result
1	{id:1, score:50}	{threshold:30, value:'X'}	50 > 30 = True	{id:1, value:'X'}
2	{id:1, score:50}	{threshold:60, value:'Y'}	50 > 60 = False	No
3	{id:2, score:70}	{threshold:30, value:'X'}	70 > 30 = True	{id:2, value:'X'}
4	{id:2, score:70}	{threshold:60, value:'Y'}	70 > 60 = True	{id:2, value:'Y'}
5	{id:3, score:20}	{threshold:30, value:'X'}	20 > 30 = False	No
6	{id:3, score:20}	{threshold:60, value:'Y'}	20 > 60 = False	No
7	End of rows			Query complete

💡 All rows from A compared with all rows from B; join ends when all pairs checked.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	Final
Current A row	None	{id:1, score:50}	{id:1, score:50}	{id:2, score:70}	{id:2, score:70}	{id:3, score:20}	{id:3, score:20}	None
Current B row	None	{threshold:30, value:'X'}	{threshold:60, value:'Y'}	{threshold:30, value:'X'}	{threshold:60, value:'Y'}	{threshold:30, value:'X'}	{threshold:60, value:'Y'}	None
Join Result	Empty	{id:1, value:'X'}	No	{id:2, value:'X'}	{id:2, value:'Y'}	No	No	Final result set

Key Moments - 2 Insights

Why do some row pairs not appear in the join result?

Because the join condition (A.score > B.threshold) is false for those pairs, so they are excluded as shown in execution_table rows 2, 5, and 6.

Is this join the same as an equi-join?

No, equi-joins use equality (=) conditions, but here we use a greater-than (>) condition, which is why some pairs match differently, as seen in the condition column.

Visual Quiz - 3 Questions

Look at the execution table, what is the join result at step 4?

A {id:2, value:'X'}

B No

C {id:2, value:'Y'}

D {id:1, value:'X'}

💡 Hint

Check the 'Join Result' column in row 4 of the execution_table.

At which step does the condition first evaluate to false?

A Step 3

B Step 2

C Step 5

D Step 6

💡 Hint

Look at the 'Condition' column in the execution_table to find the first 'False'.

If A.score was always less than B.threshold, what would the join result be?

A Empty result set

B Only rows where scores are equal

C All rows from A joined with all rows from B

D Only rows where scores are greater

💡 Hint

Refer to the variable_tracker and execution_table where condition is false and no rows join.

Concept Snapshot

Non-equi joins use conditions other than '=' to join tables.
Syntax example: JOIN ... ON A.col > B.col
Returns rows where condition is true.
Useful for ranges, inequalities, and complex matching.
Unlike equi-joins, matches depend on >, <, >=, <=, != conditions.

Full Transcript

Non-equi joins combine rows from two tables based on conditions that are not equality. For example, joining where one column is greater than another. The process starts by taking each row from the first table and comparing it to each row in the second table. If the condition like A.score > B.threshold is true, the rows are joined and included in the result. If false, they are skipped. This is different from equi-joins which only join rows with equal values. The example query joins tables A and B where A's score is greater than B's threshold. Step by step, each pair is checked, and only those pairs meeting the condition appear in the output. This method is useful when you want to join based on ranges or inequalities rather than exact matches.