Why does OUTER JOIN need an equality rule?

Question

Let's imagine 2 tables:

Customers:

cust_id

1000000001

1000000002

1000000003

1000000004

1000000005
Orders:

cust_id order_num

1000000001 20005

1000000003 20006

1000000005 20008

1000000001 20009

The following code works pretty clear to me. It compares 2 tables based on the equality of cust_id columns and joins those tables.

SELECT Customers.cust_id, Orders.order_num
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

The result is obvious:

cust_id	order_num
1000000001	20005
1000000003	20006
1000000005	20008
1000000001	20009

But, if I use OUTER JOIN method with the same rule:

SELECT Customers.cust_id, Orders.order_num
FROM Customers
FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

I will get:

cust_id	order_num
1000000001	20005
1000000001	20009
1000000002	NULL
1000000003	20006
1000000004	NULL
1000000005	20008

This result is clear to me, I know what FULL OUTER JOIN does, but I find the fact that this method needs the equality rule quite ambiguous, because the code returns some rows from the first table that are not in the second table. So, why do we need this rule, if it is just ignored? Can we use different condition, e.g. != to avoid a confusion?

I feel like you *don't* know what a `FULL OUTER JOIN` does. A `FULL OUTER JOIN` returns all the rows from *both* tables, however, it still requires an `ON` clause to define the relationship when the rows *should* be matched; `NULL`will simply be returned in the appropriate columns when no relationship is found. — Thom A, May 31 '22 at 16:11
See https://stackoverflow.com/questions/38549/what-is-the-difference-between-inner-join-and-outer-join/27458534#27458534 — Martin Smith, May 31 '22 at 16:12
*"Can we use different condition, e.g. != to avoid a confusion?"* `ON Customers.cust_id != Orders.cust_id;` would give an entirely different result (and likely the same result whether you used an `INNER` or `OUTER` `JOIN`) — Thom A, May 31 '22 at 16:15
@Larnu Thanks for the answer. I understand that `FULL OUTER JOIN` returns all the rows from both tables, I just can't understand why an `ON` clause is needed in that case. I mean is the clause just ignored by SQL? — , May 31 '22 at 16:47
No, the clause isn't ignored by the data engine; it can clearly be seen it's not in the above. What results are you *actually* expecting here, as it's unclear. — Thom A, May 31 '22 at 16:49
@866812 The "OUTER PRODUCT" in math (e.g., vectors, matrices) is more aligned with a cross join than an outer join. A "CROSS PRODUCT" of two vectors is another vector. An "INNER PRODUCT" reduces rank (e.g., the inner product of two vectors is a scalar). I accept the terminology and try not to think "physics" in T-SQL. Do you come from a math or physics background? — Randy in Marin, May 31 '22 at 20:29
It's not clear what you are asking or what the problem is. What do "the equality rule" & "needs the equality rule" & "ambiguous" mean, and what do they have to do with your "because"? [mre] By rule do you mean, an ON condition/predicate? Do you understand that by selecting only some columns not \* you are not seeing the actual results of the joins, but only part? [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/a/46091641/3404097) PS Please clarify via edits, not comments. — philipxy, May 31 '22 at 21:31
A non-natural join doesn't return any rows from the input tables. It returns rows with subrows from the input tables. Thinking/saying things like "returns all the rows from both tables" is unclear, unhelpful & misleading. Force yourself to remember & say things fully & precisely. (Still sloppy but reasonable for a comment:) Cross join (also comma) is every pairing of a row from the left table & a row from the right table; inner join on c is cross join where c; left/right/full join on c is inner join on c union all with unmatched rows from the left/right/both tables extended by nulls. — philipxy, May 31 '22 at 22:17
@RandyinMarin Vector products have nothing to do with relational & SQL products. You might as well mention agricultural products. Appearance in both contexts of terms like "cross", "product" & "rank" is irrelevant. Relational product has something to do with Cartesian product. Although calling relational product Cartesian is an abuse of language, as is calling SQL relational. (Despite the ubiquity.) — philipxy, May 31 '22 at 22:46
@philipxy Exactly. If the poster has a background like I do, I can understand the terminology confusion. I was just curious if this is the case. — Randy in Marin, Jun 01 '22 at 15:36

Randy in Marin · Accepted Answer · 2022-06-01T15:44:23.427

You are confusing a cross join with a inner/outer(left,right,full) joins. A cross join will match each row with every other row without a condition. The inner/outer joins match on those satisfying the condition in the ON clause. The primary difference among the inner and outer joins is how a non-match is handled. The non-match concept does not apply to a cross join.

Try running these queries as an example. Note the last couple of queries using a full outer join. The ON clause with 1=1 simulates the cross join. The ON clause with 1=0 returns one record from each table.

BTW, I added a ORDER BY in the ON clause with 1=1 because the order was not that of the cross join. (Wanted it easy to compare.) Even without the ORDER BY clause, the cost of this trick is about 100x using a cross join for this very small example.

set nocount on

print 'Inner join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
inner join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id

print 'Left outer join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
left outer join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id

print 'Right outer join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
right outer join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id

print 'Full outer join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON t1.id = t2.id

print 'Cross join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
cross join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)

print 'Full outer join with everything matching anything - a fake cross join:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON 1 = 1
ORDER BY t1.id, t2.id

print 'Full outer join with no matches ever:'

select * 
from (
    values (1,'A'),(2,'B'),(3,'C'),(25,'Y')
) t1 (id, val)
full outer join (
    values (1, 'A'),(2,'B'),(3,'C'),(26,'Z')
) t2 (id, val)
ON 1 = 0

Inner join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
2           B    2           B
3           C    3           C

Left outer join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
2           B    2           B
3           C    3           C
25          Y    NULL        NULL

Right outer join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
2           B    2           B
3           C    3           C
NULL        NULL 26          Z

Full outer join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
2           B    2           B
3           C    3           C
NULL        NULL 26          Z
25          Y    NULL        NULL

Cross join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
1           A    2           B
1           A    3           C
1           A    26          Z
2           B    1           A
2           B    2           B
2           B    3           C
2           B    26          Z
3           C    1           A
3           C    2           B
3           C    3           C
3           C    26          Z
25          Y    1           A
25          Y    2           B
25          Y    3           C
25          Y    26          Z

Full outer join with everything matching anything - a fake cross join:

id          val  id          val
----------- ---- ----------- ----
1           A    1           A
1           A    2           B
1           A    3           C
1           A    26          Z
2           B    1           A
2           B    2           B
2           B    3           C
2           B    26          Z
3           C    1           A
3           C    2           B
3           C    3           C
3           C    26          Z
25          Y    1           A
25          Y    2           B
25          Y    3           C
25          Y    26          Z

Full outer join with no matches ever:

id          val  id          val
----------- ---- ----------- ----
1           A    NULL        NULL
2           B    NULL        NULL
3           C    NULL        NULL
25          Y    NULL        NULL
NULL        NULL 1           A
NULL        NULL 2           B
NULL        NULL 3           C
NULL        NULL 26          Z

This does not clearly say exactly what the joins do. It unclearly says some things about what they do. Writings like this are why the asker doesn't understand joins. They are unhelpful & misleading. (See my comments on the question & my comments & answers on the linked pages.) PS By inner/left/right/outer do you intend inner/left/right/full? — philipxy, May 31 '22 at 22:23
The focus is on the original question, not completeness. The examples might help for some. — Randy in Marin, Jun 01 '22 at 16:04

Why does OUTER JOIN need an equality rule?

1 Answers1