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

Question

Given 2 tables T1 and T2.

T1   T2 
---------
A    1 
B    2
C    3

You make a query:

SELECT * 
  FROM T1, T2

What is the no: of rows that are fetched from this query?

(a) 4
(b) 5
(c) 6
(d) 9

Answer is : 9

Question:

Why is the answer "9"?

Answer 1

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