CODEWITHSUNDEEP
sql
Tim
Tim

Reputation: 2411

SQL Inner Join with no WHERE clause

I was wondering, how does an inner join work when no WHERE clause is specified? For example,

SELECT table1.letter, table2.letter, table1.number, table2.number
FROM tbl AS table1, tbl AS table2;

tbl:
text, integer
a   , 1
b   , 2
c   , 3

Tried finding some examples online but I couldn't seem to find any :-/

Thanks!

Upvotes: 1

Views: 421

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The comma operator generates a Cartesian product -- every row in the first table combined with every row of the second.

This is more properly written using the explicit cross join:

SELECT table1.letter, table2.letter, table1.number, table2.number
FROM tbl table1 CROSS JOIN
     tbl table2;

If you have conditions for combining the two tables, then you would normally use JOIN with an ON clause.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

The current implicit join syntax you are using:

FROM tbl AS table1, tbl AS table2;

will result in a cross join if no restrictions are present in the WHERE clause. But really you should use modern ANSI-92 syntax when writing your queries, e.g.

SELECT
    table1.letter,
    table2.letter,
    table1.number,
    table2.number
FROM tbl AS table1
INNER JOIN tbl AS table2
-- ON <some conditions>

One obvious reason to use this syntax is that it makes it much easier to see the logic of your query. In this case, if your updated query were missing an ON clause, then we would know right away that it is doing a cross join, which most of the time is usually not what you want to be doing.

Upvotes: 2

srp
srp

Reputation: 585

You can use cross join 

select * from table1 cross join table2

Here is a link to understand more about the use of cross join. https://www.w3resource.com/sql/joins/cross-join.php

Upvotes: 1

Related Questions