implicit join or not？

Question

I am a bit confused about what kind of join is this in the code that is in yellow(below). It seems like an implicit join to me but I thought instead of using "ON" we use "WHERE" to specify which column we are using to join two tables together. But here, it doesn't look like it has "WHERE". The only possible reason I can think of is that since there is only one number in the CTE(seat_count), then I do not need to specify which column I am using?

In case anyone wants some idea about the problem:

QUERY(Labeled picture):

QUERY(In text):

WITH seat_count AS( SELECT COUNT(*) AS counts FROM seat) SELECT (CASE WHEN MOD(id,2)!= 0 AND counts != id THEN id+1 WHEN MOD(id,2)!= 0 AND counts = id THEN id ELSE id-1 END) AS id, student FROM seat, seat_count ORDER BY id ASC

That's the old syntax for a cross join, according to the SQL-89 standard. It was superseded 29 yeas ago by the SQL-92 standard that uses the CROSS JOIN clause instead. I would suggest you get this material updated. — The Impaler
– The Impaler, Commented Jan 3, 2021 at 1:04
'INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).' - dev.mysql.com/doc/refman/8.0/en/join.html — P.Salmon
– P.Salmon, Commented Jan 3, 2021 at 8:15

Bill Karwin · Accepted Answer · 2021-01-02 22:20:13Z

The join FROM seat, seat_count is equivalent to FROM seat CROSS JOIN seat_count which means without further conditions in the WHERE clause, it matches every row of one table to every row of the other table. When using the CROSS JOIN syntax, there is no ON clause.

In the example, the CTE SELECT COUNT(*) AS counts FROM seat is certain to return exactly one row. The cross-join ends up joining N rows from seat to 1 row from seat_count.

Therefore it's a cross-join, but it doesn't cause any confusing multiplication of rows in the results.

Lukasz Szozda · Accepted Answer · 2021-01-02 22:20:22Z

It seems like an implicit join to me but I thought instead of using "ON" we use "WHERE" to specify which column we are using to join two tables together

It is not a implicit join, but a CROSS JOIN

WITH seat_count AS( SELECT COUNT(*) AS counts FROM seat) SELECT (CASE WHEN MOD(id,2)!= 0 AND counts != id THEN id+1 WHEN MOD(id,2)!= 0 AND counts = id THEN id ELSE id-1 END) AS id, student FROM seat, seat_count ORDER BY id ASC

is the same as:

WITH seat_count AS( SELECT COUNT(*) AS counts FROM seat) SELECT (CASE WHEN MOD(id,2)!= 0 AND counts != id THEN id+1 WHEN MOD(id,2)!= 0 AND counts = id THEN id ELSE id-1 END) AS id, student FROM seat CROSS JOIN seat_count ORDER BY id ASC

Collectives™ on Stack Overflow

implicit join or not？

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related