CROSS JOIN can replace with FULL JOIN with join co

Get more optimize query for Full Join or Cross Join

I need to return an portability combination output from 2 table. When table1 has rows but table2 is empty, it will still able to return the rows. I tried CROSS JOIN but failed. Seem like only FULL OUTER JOIN ON using join filter 1=1 can get the correct result. Any others better way?

declare @table1 table (col1    int,    col2    int ) 
declare @table2 table (col1    int,    col2    int )

insert into @table1 select  1, 11   union all select    2, 22   
union all select    1, 22   

-- When @table2 is empty, CROSS JOIN is return empty rows.
select  t1.*, ISNULL(t2.col1, 0), ISNULL(t2.col2, 0)
from    @table1 t1 CROSS JOIN @table2 t2
order by t1.col1, t1.col2, t2.col1, t2.col2    

-- When @table2 is empty, still show record from @table1 with zero values
select  t1.*, ISNULL(t2.col1, 0), ISNULL(t2.col2, 0)
from    @table1 t1 FULL OUTER JOIN @table2 t2
    on  1=1
order by t1.col1, t1.col2, t2.col1, t2.col2

Following result is currently what i want, but any possible it will wrong? or more better implementation?

-- When @table2 is empty, still show record from @table1 with zero values
select  t1.*, ISNULL(t2.col1, 0), ISNULL(t2.col2, 0)
from    @table1 t1 FULL OUTER JOIN @table2 t2
    on  1=1
order by t1.col1, t1.col2, t2.col1, t2.col2

CROSS JOIN and FULL OUTER JOIN is not the same...

CROSS JOIN will create a cartesian product of the existing rows (each with each). A set of a,b,c and a second set of x,y will result in ax,ay,bx,by,cx,cy.

If there is nothing in table 2 what product should be the result?

But a FULL OUTER JOIN (in your case with an always-true-condition) will return each row of set 1 in any case and each row of set 2 in any case.

Try this:

DECLARE @table1 TABLE (chr CHAR(1)) 
DECLARE @table2 TABLE (chr CHAR(1))

INSERT INTO @table1 VALUES('a'),('b'),('c');
INSERT INTO @table2 VALUES('x'),('y');

SELECT * FROM @table1 t1 CROSS JOIN @table2 t2;
SELECT * FROM @table1 t1 FULL OUTER JOIN @table2 t2 ON 1=1;

--Both SELECTs have returned the same

a   x
b   x
c   x
a   y
b   y
c   y

--But now we empty one of the tables

DELETE FROM @table2;

SELECT * FROM @table1 t1 CROSS JOIN @table2 t2;
SELECT * FROM @table1 t1 FULL OUTER JOIN @table2 t2 ON 1=1;

--Now the CROSS JOIN returns nothing (as a product with nothing is nothing) and the FULL OUTER JOIN returns the values of @tbl1 with NULL values for @tbl2.