Sometimes Cross join not return any rows if one of tables doesn't has any row
for example
WITH q1 AS ( SELECT 1 AS V1 WHERE 0 <> 0 ), q2 AS ( SELECT 2 AS V2 ) SELECT * FROM q1 , q2 I expect this query will return one row V1 = null and V2 = 2 but it returns no rows
Is there any explanation?
Cross join (cartesian product) returns every combination of rows from the two tables. i.e. each row on one table is paired with every row on the other side.
This gives M * N rows where M is the number of rows from one side and N the number of rows on the other.
Multiplying by zero returns zero.
If you want to preserve rows with no match on the other side you need an outer join.
WITH q1 AS ( SELECT 1 AS V1 WHERE 0 <> 0 ), q2 AS ( SELECT 2 AS V2 ) SELECT * FROM q1 RIGHT OUTER JOIN q2 ON 1=1 (Or FULL OUTER JOIN to preserve from either side)
