Cartesian Product the same as an explicit join in Teradata SQL and T-SQL?

Question

Is a cross join with a where clause the same as an inner join with an ON clause?

The syntax of Teradata SQL is confusing me. Here is a simple Teradata query with two tables:

SELECT pno, hours, lName
FROM Employee, Works_on
WHERE employee.ssn = works_on.essn

There is no explicit join. A Cartesian Product is implied and the Where clause defines a filter on the results of that product, I think. In T-SQL I would use an explicit ON clause rather than the WHERE clause.

I can do this in T-SQL but it seems rather inefficient with the Cross Join:

SELECT pno, hours, lName
FROM Employee CROSS JOIN Works_on
WHERE employee.ssn = works_on.essn

This is how I would actually do it in T-SQL, with an explicit inner join

SELECT pno, hours, lName
FROM Employee INNER JOIN
     Works_on
     ON employee.ssn = works_on.essn

I don't know anything about Teradata, but I do know that MySQL allows the old "Cartesian" syntax, and it correctly detects that it is actually an inner join. In fact, CROSS JOIN, INNER JOIN and comma join are treated as synonyms in MySQL. — siride, Sep 01 '16 at 00:50
@siride a Cross Join is not mathematically the same as an Inner Join. — nicomp, Sep 01 '16 at 00:52
"correctly detects that it's actually an inner join". That's what MySQL is doing, regardless of the mathematical definitions. And in truth, the end results of a Cartesian join with a filter in the where clause and an inner join are the same. It doesn't matter at which logical step the filter is applied. — siride, Sep 01 '16 at 00:53
@siride I gothcha. I do get the same results with both but I wonder if one is more efficient. These days, the SQL Engine probably optimizes away the inefficiencies. — nicomp, Sep 01 '16 at 00:57
you have to look at the execution plans. I don't know if Teradata provides this, but SQL Server and MySQL do. That will show whether the DB is actually treating it like an inner join. — siride, Sep 01 '16 at 01:06
Implicit (comma-delimiited) join syntax is still valid Standard SQL (and valid T-SQL, too), but the common recommendation is to use SQL-92 explicit `JOIN` syntax. — dnoeth, Sep 01 '16 at 08:46
[CROSS JOIN vs INNER JOIN in SQL Server 2008](http://stackoverflow.com/a/25957600/3404097) (Applies to all SQLs.) — philipxy, Apr 11 '17 at 02:18

score 2 · Answer 1 · edited Jan 06 '22 at 02:44

The correct syntax is to use the explicit JOIN syntax. I think you are probably aware of that. Teradata supports this syntax and has for a long, long time.

The more important point is that you should not confuse the expression of a query with the execution. So, you might write:

SELECT pno, hours, lName
FROM Employee CROSS JOIN
     Works_on
WHERE employee.ssn = works_on.essn;

The SQL query describes the result set, not the method of processing. The result of this query is the same as the INNER JOIN:

SELECT pno, hours, lName
FROM Employee INNER JOIN
     Works_on
     ON employee.ssn = works_on.essn;

Only the expression of the query is different.

Using CROSS JOIN (although not the preferred version) does not mean that the engine actually generates a cartesian product and then filters the result. In fact, this should have the same execution plan as the inner join -- the compiler and optimizer are pretty smart in both SQL Server and Teradata.

"The correct syntax is to use the explicit JOIN syntax. I think you are probably aware of that." -- Why do you think I knew that? — nicomp, Sep 01 '16 at 01:04

score 0 · Answer 2 · answered Sep 01 '16 at 01:33

The syntax:

SELECT pno, hours, lName FROM Employee, Works_on WHERE employee.ssn = works_on.essn

is the same as the regular JOIN, just a different transcription, same as in Oracle; no cartesian product with subsequent filter. Check out its explain plan.

diagnostic helpstats on for session;
explain
SELECT pno, hours, lName FROM Employee, Works_on WHERE employee.ssn = works_on.essn;

Cartesian Product the same as an explicit join in Teradata SQL and T-SQL?

2 Answers2