Is Cross Join a Synonym for Inner Join Without on Clause

Is CROSS JOIN a synonym for INNER JOIN without ON clause?

In all modern databases all these constructs are optimized to the same plan.

Some databases (like SQL Server) require an ON condition after the INNER JOIN, so your third query just won't parse there.

Visibility scope of the tables is in the JOIN order, so this query:

SELECT  *
FROM    s1
JOIN    s2
ON      s1.id IN (s2.id, s3.id)
CROSS JOIN
        s3

won't parse, while this one:

SELECT  *
FROM    s2
CROSS JOIN
        s3
JOIN    s1
ON      s1.id IN (s2.id, s3.id)

will.

How to join 2 tables without an ON clause

A join without condition is a cross join. A cross join repeats each row for the left hand table for each row in the right hand table:

FROM table_a a
CROSS JOIN table_b b

Note that in MySQL, cross join / join / inner join are identical. So you could write:

FROM table_a a
JOIN table_b b

As long as you omit the on clause, this will work as a cross join.

If you'd like to sum two columns from two tables, a cross join would not work because it repeats rows. You'd get highly inflated numbers. For sums, a better approach uses subqueries, per @sgeddes answer.

What is join in MySQL without anything in front?

Just read the documentation from MySQL. It says.

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

To answer your question: join is the same like a inner join

Why do CROSS JOIN conditions not work in the 'ON' clause, only the WHERE clause?

CROSS JOIN is the SQL operator to perform a full cartesian product between two tables. Since it is a cartesian product, it does not allow any condition during the operation, you can only restrict its result with some filtering operation (the WHERE condition).

JOIN (INNER and OUTER JOIN, that is) operators, are simply cartesian product together with the filtering operator expressed in the ON part of the operator (and in fact in the original syntax of SQL there was no JOIN operator, simply the “comma” notation to denote the product with the join condition expressed always in the WHERE part).

Examples:

"old" notation:

SELECT ...
FROM table1 t1, table2 t2
WHERE t1.attribute = t2.attribute

equivalent to the "modern" notation:

SELECT ...
FROM table1 t1 INNER JOIN table2 t2 ON t1.attribute = t2.attribute

while, for the cartesian product:

"old" notation:

SELECT ...
FROM table1 t1, table2 t2

equivalent to the "modern" notation:

SELECT ...
FROM table1 t1 CROSS JOIN table2 t2

In other words, a CROSS JOIN that require a condition is actually some kind of INNER JOIN.

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345.

The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

What is the difference between using a cross join and putting a comma between the two tables?

They return the same results because they are semantically identical. This:

select * 
  from A, B

...is (wince) ANSI-89 syntax. Without a WHERE clause to link the tables together, the result is a cartesian product. Which is exactly what alternative provides as well:

    select * 
      from A 
cross join B

...but the CROSS JOIN is ANSI-92 syntax.

About Performance

There's no performance difference between them.

Why Use ANSI-92?

The reason to use ANSI-92 syntax is for OUTER JOIN support (IE: LEFT, FULL, RIGHT)--ANSI-89 syntax doesn't have any, so many databases implemented their own (which doesn't port to any other databases). IE: Oracle's (+), SQL Server's =*

Why does OUTER JOIN need an equality rule?

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

---

Related Topics