Cross Join
-
I've got a query like this:
WITH CTE1 AS
(
SELECT * FROM TABLE WHERE A = @A
),
CTE2 AS
(
SELECT * FROM TABLE WHERE B = @B
)
SELECT * FROM
CTE1
CROSS JOIN
CTE2The above query works fine for me and always returns cross join result of the two queries. The problem happens when one of those CTEs doesn't return any row and therefore the CROSS JOIN returns nothing as well. When only one of those CTEs return something, my expectation is to return the result of that CTE cross joined with null values. Do you know how I can achieve this purpose?
-
I've got a query like this:
WITH CTE1 AS
(
SELECT * FROM TABLE WHERE A = @A
),
CTE2 AS
(
SELECT * FROM TABLE WHERE B = @B
)
SELECT * FROM
CTE1
CROSS JOIN
CTE2The above query works fine for me and always returns cross join result of the two queries. The problem happens when one of those CTEs doesn't return any row and therefore the CROSS JOIN returns nothing as well. When only one of those CTEs return something, my expectation is to return the result of that CTE cross joined with null values. Do you know how I can achieve this purpose?
Since cross join doesn't have any 'OUTER' option, the result is always all combinations from all sources. If any of the sources have 0 rows the size of the result set is 0. I think you could use simple outer joins and unions to get desired rows. Something like:
SELECT *
FROM Table A LEFT OUTER JOIN Table B ON 1=1
WHERE ...
UNION
SELECT *
FROM Table A RIGHT OUTER JOIN Table B ON 1=1
WHERE ...However, I would re-check the design of the data model since normally the need to cross join is very rare and may indicate a design problem.
The need to optimize rises from a bad design. My articles[^]