Let's imagine 2 tables:

1. Customers:

   cust_id
   1000000001
   1000000002
   1000000003
   1000000004
   1000000005

2. 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:

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:

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?

CodePudding user response：

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