Cross Join In Sql Server not always return all rows from all tables

Question

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?

Answer 1

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)