The effect of a select with multiple tables in FROM is the same as INNER JOIN but what is the ON clause then?

Question

I have a query like this:

SELECT
    *
FROM
    table1,
    table2

I know this is somewhat equivalent to:

SELECT
    *
FROM
    table1
INNER JOIN
    table2
ON ???

However, what would be the resulting ON clause for the join?

Update

After some testing in SSMS here are my findings

SELECT * FROM table1,table2

gives the same execution plan and the same records as

SELECT * FROM table1 INNER JOIN table2 ON 1=1

and the same thing for

SELECT * FROM table1 CROSS JOIN table2

If your first query has no `WHERE` clause, then it would be a `CROSS JOIN`, not an `INNER JOIN`, and have no `ON`. — LittleBobbyTables - Au Revoir, Nov 02 '12 at 15:26
@LittleBobbyTables - Though you could write it as `... table1 inner join table2 on 1=1` — Martin Smith, Nov 02 '12 at 15:34
For what I tested on a big data table, the row count is the same with the multiple tables and with an inner join on 1 = 1... — Stécy, Nov 02 '12 at 15:38
Additionally, I see that the query plan is the same in both cases... — Stécy, Nov 02 '12 at 15:46

score 1 · Answer 1 · answered Nov 02 '12 at 15:25

1

the column that defines their relationship.

SELECT  *
FROM  table1 
      INNER JOIN table2
           ON table1.ID = table2.ID

actually the query you have showed is not equal. The first one produces cartesian product of all the records on both table or in other words CROSS JOIN.

answered Nov 02 '12 at 15:25

John Woo

258,903
69
498
492

So, the join will use all the columns from the relations between the 2 tables? Or only from the primary keys? – Stécy Nov 02 '12 at 15:29
the columns that you have defined an index, and these are usually primary and a foreign key. but can also be primary and primary. :D – John Woo Nov 02 '12 at 15:30
So, the first query is a cross join, how should it be rewritten then? – Stécy Nov 02 '12 at 15:31
add `WHERE` clause. like, `WHERE table1.ID = table2.ID` – John Woo Nov 02 '12 at 15:32
hmmm... According to the TSQL reference site (http://msdn.microsoft.com/en-us/library/ms177634.aspx) it says, under the description of INNER: When no join type is specified, this is the default... Maybe I misread this part? – Stécy Nov 02 '12 at 15:35
@Stécy - That just means that if you don't specify one of `INNER, LEFT, RIGHT, FULL` preceding the `JOIN` keyword then `INNER` is the default. You don't have the `JOIN` keyword in your query. – Martin Smith Nov 02 '12 at 15:37
it just means that when you try to join the table and you did not specify the type of join, example `SELECT * FROM table1 JOIN table2`, it just mean that you are doing `INNER JOIN`. and is the same as `SELECT * FROM table1 INNER JOIN table2` – John Woo Nov 02 '12 at 15:38
Yeah, makes sense now. However, I would like to see the location in the doc where it says that this is a cross join by default (not that I don't believe you, I'm just being thorough) ;) – Stécy Nov 02 '12 at 15:40
@Stécy [not really related to your question but the two are different format standards](http://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89) – John Woo Nov 02 '12 at 15:43

score 1 · Answer 2 · answered Nov 02 '12 at 15:31

SELECT
    *
FROM
    table1,
    table2

is equivalent to:

SELECT 
    * 
FROM 
    table1
CROSS JOIN 
    table2

there is no ON statement with a CROSS JOIN. If you need to filter a CROSS JOIN, put it in the WHERE clause.

WHERE table1.DateCreated <= table2.DateModified

score 0 · Accepted Answer · answered Nov 30 '12 at 19:19

0

After some testing in SSMS here are my findings

SELECT * FROM table1,table2

gives the same execution plan and the same records as

SELECT * FROM table1 INNER JOIN table2 ON 1=1

and the same thing for

SELECT * FROM table1 CROSS JOIN table2

answered Nov 30 '12 at 19:19

Stécy

11,951
16
64
89

The effect of a select with multiple tables in FROM is the same as INNER JOIN but what is the ON clause then?

Update

3 Answers3