CODEWITHSUNDEEP
mysqlsqljoin
DJ-coding
DJ-coding

Reputation: 323

implicit join or not？

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:

enter image description here

QUERY(Labeled picture):

enter image description here

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

Upvotes: 2

Views: 177

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 4

Related Questions