What is the difference between CROSS JOIN and multiple tables in one FROM?
The first with the comma is an old style from the previous century.
The second with the CROSS JOIN is in newer ANSI JOIN syntax.
And those 2 queries will indeed give the same results.
They both link every record of table "a" against every record of table "b".
So if table "a" has 10 rows, and table "b" has 100 rows.
Then the result would be 10 * 100 = 1000 records.
But why does that first outdated style still exists in some DBMS?
Mostly for backward compatibility reasons, so that some older SQL's don't suddenly break.
Most SQL specialists these days would frown upon someone who still uses that outdated old comma syntax. (although it's often forgiven for an intentional cartesian product)
A CROSS JOIN
is a cartesian product JOIN that's lacking the ON
clause that defines the relationship between the 2 tables.
In the ANSI JOIN syntax there are also the OUTER joins: LEFT JOIN, RIGHT JOIN, FULL JOIN
And the normal JOIN, aka the INNER JOIN.
But those normally require the ON
clause, while a CROSS JOIN doesn't.
And example of a query using different JOIN types.
SELECT *
FROM jars
JOIN apples ON apples.jar_id = jars.id
LEFT JOIN peaches ON peaches.jar_id = jars.id
CROSS JOIN bananas AS bnns
RIGHT JOIN crates ON crates.id = jars.crate_id
FULL JOIN nuts ON nuts.jar_id = jars.id
WHERE jars.name = 'FruityMix'
The nice thing about the JOIN syntax is that the link criteria and the search criteria are separated.
While in the old comma style that difference would be harder to notice. Hence it's easier to forget a link criteria.
SELECT *
FROM crates, jars, apples, peaches, bananas, nuts
WHERE apples.jar_id = jars.id
AND jars.name = 'NuttyFruitBomb'
AND peaches.jar_id = jars.id(+)
AND crates.id(+) = jar.crate_id;
Did you notice that the first query has 1 cartesian product join, but the second has 2? That's why the 2nd is rather nutty.
SQL Server: What is the difference between CROSS JOIN and FULL OUTER JOIN?
A CROSS JOIN
produces a cartesian product between the two tables, returning all possible combinations of all rows. It has no ON
clause because you're just joining everything to everything.
A FULL OUTER JOIN
is a combination of a LEFT OUTER
and RIGHT OUTER
join. It returns all rows in both tables that match the query's WHERE
clause, and in cases where the ON
condition can't be satisfied for those rows it puts NULL
values in for the unpopulated fields.
This wikipedia article explains the various types of joins with examples of output given a sample set of tables.
CROSS JOIN vs INNER JOIN in SQL
Cross join does not combine the rows, if you have 100 rows in each table with 1 to 1 match, you get 10.000 results, Innerjoin will only return 100 rows in the same situation.
These 2 examples will return the same result:
Cross join
select * from table1 cross join table2 where table1.id = table2.fk_id
Inner join
select * from table1 join table2 on table1.id = table2.fk_id
Use the last method
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 =*
INNER JOIN vs multiple table names in FROM
There is no reason to ever use an implicit join (the one with the commas). Yes for inner joins it will return the same results. However, it is subject to inadvertent cross joins especially in complex queries and it is harder for maintenance because the left/right outer join syntax (deprecated in SQL Server, where it doesn't work correctly right now anyway) differs from vendor to vendor. Since you shouldn't mix implicit and explict joins in the same query (you can get wrong results), needing to change something to a left join means rewriting the entire query.
In SQL, what's the difference between JOIN and CROSS JOIN?
MySQL doesn't offer a distinction between JOIN
and CROSS JOIN
. They are the same.
In both your examples the clause
WHERE t1.a3 = t2.a1
converts any sort of join into an inner join. The standard way of expressing this query is
SELECT t1.a1, t1.a2, t1.a3
FROM t1
JOIN t2 ON t1.a3 = t2.a1
Performance of inner join compared to cross join
Cross Joins produce results that consist of every combination of rows from two or more tables. That means if table A has 6 rows and table B has 3 rows, a cross join will result in 18 rows. There is no relationship established between the two tables – you literally just produce every possible combination.
With an inner join, column values from one row of a table are combined with column values from another row of another (or the same) table to form a single row of data.
If a WHERE clause is added to a cross join, it behaves as an inner join as the WHERE imposes a limiting factor.
As long as your queries abide by common sense and vendor specific performance guidelines (i), I like to think of the decision on which type of join to use to be a simple matter of taste.
(i) Vendor Specific Performance Guidelines
- MySQL Performance Tuning and Optimization Resources
- PostgreSQL Performance Optimization
Related Topics
Running Powershell Scripts Through SQL
Is Not Null Test for a Record Does Not Return True When Variable Is Set
Determine Contiguous Dates in SQL Gaps and Islands
How to Optimize This SQL Query (Using Indexes)
SQL Query: How to Create Subtotal Rows When There Is No Aggregate Function
How to Create Text File Using SQL Script with Text "|"
Reverse in Oracle This Path Z/Y/X to X/Y/Z
SQL Case: Does the Order of the When Statements Matter
Using Insert into with 'Select' to Supply Some Values But Not Others (Access 2010)
Connect to Remote SQL Database Using Excel
Case Statement with Different Data Type
SQL Inner Join Over Multiple Tables Equal to Where Syntax
SQL Query Continues Running for a Very Long Time If Search Term Not Found
Update Statement in Oracle Using SQL or Pl/SQL to Update First Duplicate Row Only
Temporary Table Record Limit in SQL Server