Is a query with table separated by comma a cross join query?

Question

I know some of SQL but, I always use join, left, cross and so on, but in a query where the tables are separated by a comma. It's looks like a cross join to me. But I don't know how to test it (the result is the same with the tries I made).

SELECT A.id, B.id
FROM A,B
WHERE A.id_B = B.id

Even in the great Question (with great answers) "What is the difference between Left, Right, Outer and Inner Joins?" I didn't find an answer to this.

I'm using Mysql at the moment. But I don't think this is relevant to the main point of the question. — Michel Ayres, Apr 15 '14 at 19:29
The tables separate by comma is older syntax. For example prior to Oracle 9i, the syntax to perform joins was similar to the SQL statement you have above. This is an INNER JOIN example — abhi, Apr 15 '14 at 19:31
Possible duplicate of [What is the difference between using a cross join and putting a comma between the two tables?](http://stackoverflow.com/questions/3918570/what-is-the-difference-between-using-a-cross-join-and-putting-a-comma-between-th) — Mark Hurd, Jun 15 '16 at 02:35

score 6 · Accepted Answer · answered Apr 15 '14 at 19:32

It would be a cross join if there wasn't a WHERE clause relating the two tables. In this case it's functionally equivalent to an inner join (matching records by id_rel and id)

It's an older syntax for joining tables that is still supported in most systems, but JOIN syntax is largely preferred.

score 2 · Answer 2 · answered Apr 15 '14 at 19:31

2

This is old pre - 1992 standard SQL join syntax, which you should not mimic or use in anything you write today

The where clause specifies the join style and columns. You can use = or *= or=* in the where to make join, left or right joins. No joins in the WHERE make it a cross join.

answered Apr 15 '14 at 19:31

KM.

101,727
34
178
212

Not even for a cross join (query without the `where`) ? (I don't like this syntax, but is good to know) – Michel Ayres Apr 15 '14 at 22:57

CodeMonkey · Answer 3 · 2014-04-15T19:39:57.357

0

As long as you have join criteria provided in the where clause, this syntax results in an inner join. For example:

Where tableA.value = TableB.Value

edited Apr 15 '14 at 19:39

answered Apr 15 '14 at 19:32

CodeMonkey

1,087
2
15
24

score 0 · Answer 4 · edited Apr 15 '14 at 22:57

0

Looks to me like an INNER JOIN

i.e. both sides must exist to get a result.

For example:

SELECT 
  A.id, B.id
FROM A
  INNER JOIN B ON A A.id_rel = B.id

edited Apr 15 '14 at 22:57

Michel Ayres

5,891
10
63
97

answered Apr 15 '14 at 19:32

LeeWatson

1

Is a query with table separated by comma a cross join query?

4 Answers4