SQL Server 中连接两个表的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20849888/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining All Rows of Two Tables in SQL Server
提问by BRomine
My goal is combining all rows in 2 tables. The simplest example I can think of is:
我的目标是合并 2 个表中的所有行。我能想到的最简单的例子是：
Table 1
表格1
Letter
A
B
Table 2
表 2
Number
0
1
Combined Table
组合表
Letter Number
A 0
B 0
A 1
B 1
I have come up with this SQL statement:
我想出了这个 SQL 语句：
select * from
(
select * From (
select 'A' as 'Letter'
UNION
select 'B' as 'Letter'
) as Letter
) as Letter,
(
select * from (
select 0 as 'Number'
UNION
select 1 as 'Number'
) as Number
) as Number
This works but I don't like it.
这有效，但我不喜欢它。
- defining the same alias multiple times
- 7 select statements? really....
- 多次定义同一个别名
- 7 选择语句？真的....
Does anyone know a cleaner way of doing this? I am sure the answer is out there already but I had no idea how to search for it. Thanks all
有谁知道这样做的更清洁的方法？我确信答案已经在那里了，但我不知道如何搜索它。谢谢大家
回答by peter.petrov
Try this
尝试这个
select * from table1 join table2 on 1=1
This is the Cartesian product and if that's what you want to get,
you just have to specify some join condition which is always true.
这是笛卡尔积，如果这就是您想要的结果，
您只需要指定一些始终为真的连接条件。
And try this too.
也试试这个。
SELECT * FROM
(
SELECT 'A' AS ch
UNION ALL
SELECT 'B'
)
T1
JOIN
(
SELECT 0 AS dg
UNION ALL
SELECT 1
) T2
ON 1 = 1
In SQL Server you can also do this (if you find it more concise/clear).
在 SQL Server 中，您也可以这样做（如果您觉得它更简洁/清晰）。
SELECT *
FROM
(
VALUES
('A'),
('B')
)
AS ch1(ch)
JOIN
(
SELECT *
FROM
(
VALUES
(0),
(1)
)
AS dg1(dg)
) TBL
ON 1 = 1
回答by Vland
Easy enough with CROSS JOIN...
很容易CROSS JOIN...
SELECT *
FROM Table1
CROSS JOIN Table2
Result:
结果：
Letter Number
------------------------- -----------
A 0
B 0
A 1
B 1
(4 row(s) affected)
