CROSS JOIN 可以用连接条件 1=1 的 FULL JOIN 代替吗? [英] CROSS JOIN can replace with FULL JOIN with join condition 1=1?

问题描述

获取更多的 Full Join 或 Cross Join 优化查询

Get more optimize query for Full Join or Cross Join

我需要从 2 个表返回一个可移植性组合输出.当 table1 有行但 table2 为空时，它仍然能够返回行.我尝试过 CROSS JOIN 但失败了.似乎只有使用连接过滤器 1=1 的 FULL OUTER JOIN ON可以得到正确的结果.还有其他更好的方法吗?

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 和 FULL OUTER JOIN 不一样...

CROSS JOIN 将创建现有行的笛卡尔积(each with each).一组 a,b,c 和第二组 x,y 将导致 ax,ay,bx,by,cx,cy.

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.

如果表 2 中没有任何内容，结果应该是什么?

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

但是FULL OUTER JOIN(在您的情况下为always-true-condition)将在任何情况下返回集合1的每一行和集合2的每一行无论如何.

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.

试试这个:

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;

--两个 SELECT 返回的结果相同

--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;

--现在 CROSS JOIN 什么都不返回(作为 product 什么都不是)，FULL OUTER JOIN 返回值@tbl1 带有 NULL 值的 @tbl2.

--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.

a   NULL
b   NULL
c   NULL

这篇关于CROSS JOIN 可以用连接条件 1=1 的 FULL JOIN 代替吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！