How Does Select from Two Tables Separated by a Comma Work? (Select * from T1, T2)

How does SELECT from two tables separated by a comma work? (SELECT * FROM T1, T2)

The comma between the two tables signifies a CROSS JOIN, which gives the Cartesian product of the two tables. Your query is equivalent to:

SELECT *
FROM T1
CROSS JOIN T2

The result is every pairing of a row from the first table with a row from the second table. The number of rows in the result is therefore the product of the number of rows in the original tables. In this case the answer is 3 x 3 = 9.

The rows will be as follows:

T1.foo   T2.bar
A        1
A        2
A        3
B        1
B        2
B        3
C        1
C        2
C        3

What type of join is this?

Cartesian product of the 2 tables...

Explanation of the SQL query below

Another way of writing that query is this...

SELECT DISTINCT
    T.name
FROM
    instructor  AS T
CROSS JOIN
    instructor  AS S
WHERE
    T.salary > S.salary

In general CROSS JOINs are a bad idea. And here they're a really bad idea. If you have 100 instructors, you evaluate 4950 different combinations to get a unique list of 99 instructors.

- Instructor001 has 99 instructors who are paid less

- Instructor002 has 98 instructors who are paid less

- ...

- Instructor100 has 00 instructors who are paid less

- Totalling 4950 combinations

If you have 1000 instructors you evaluate 499500 combinations to get a unique list of 999 instructors.

A better idea would be...

SELECT
    i.Name
FROM
    instructor    i
WHERE
    i.salary > (SELECT MIN(salary) FROM instructor)

Or...

SELECT
    i.Name
FROM
    instructor    i
WHERE
    i.Name <> (SELECT Name FROM instructor ORDER BY Salary ASC LIMIT 1)

Or...

SELECT
    i.*
FROM
    instructor    i

EXCEPT

SELECT
    i.*
FROM
    instructor    i
ORDER BY
    Salary ASC
LIMIT 1

So, you're right, it's not necessary (or even a good idea) to do it that way.

---

Related Topics