将两个不同查询的结果合并为一个的问题

I have two tables (TableA and TableB).

我有两个表（TableA和TableB）。

create table TableA
(A int null)

create table TableB
(B int null)

insert into TableA 
(A) values (1)

insert into TableB
(B) values (2)

I cant join them together but still I would like to show the result from them as one row.

我不能把他们加在一起，但我仍然希望将他们的结果显示为一行。

Now I can make select like this:

现在我可以像这样选择：

select
(select A from tableA) as A
, B from TableB

Result:

结果：

A    B
1    2

But if I now delete from tableB:

但是，如果我现在从tableB删除：

delete tableB

Now when I run the same query as before:

现在，当我运行与以前相同的查询时：

select
(select A from tableA) as A
, B from TableB

I see this:

我看到了这个：

A    B

But I was expecting seeing value from tableA

但我期待看到tableA的价值

like this:

喜欢这个：

Expected Result:

预期结果：

A    B
1

Why is this happening and how can I still see the value from TableA although selectB is returning 0 rows?

为什么会发生这种情况，虽然selectB返回0行，但仍然能看到TableA中的值？

I am using MS SQL Server 2005.

我正在使用MS SQL Server 2005。

4 个解决方案

#1

give this a try:

尝试一下：

DECLARE @TableA table (A int null)
DECLARE @TableB table (B int null)

insert into @TableA (A) values (1)
insert into @TableB (B) values (2)

--this assumes that you don't have a Numbers table, and generates one on the fly with up to 500 rows, you can increase or decrease as necessary, or just join in your Numbers table instead
;WITH Digits AS
( 
    SELECT 0 AS nbr
    UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
    UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
    UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 
)
, AllNumbers AS
(
    SELECT u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 AS Number
        FROM Digits u1, Digits u2, Digits u3 
        WHERE u3.nbr * 100 + u2.nbr * 10 + u1.nbr + 1 <= 500
)
, AllRowsA AS
(
  SELECT
      A, ROW_NUMBER() OVER (ORDER BY A) AS RowNumber
  FROM @TableA
)
, AllRowsB AS
(
  SELECT
      B, ROW_NUMBER() OVER (ORDER BY B) AS RowNumber
  FROM @TableB
)
SELECT
    a.A,b.B
    FROM AllNumbers               n
        LEFT OUTER JOIN AllRowsA  a on n.Number=a.RowNumber
        LEFT OUTER JOIN AllRowsB  b on n.Number=b.RowNumber
    WHERE a.A IS NOT NULL OR b.B IS NOT NULL

OUTPUT:

OUTPUT：

A           B
----------- -----------
1           2

(1 row(s) affected)

if you DELETE @TableB, the output is:

如果你删除@TableB，输出是：

A           B
----------- -----------
1           NULL

(1 row(s) affected)

#2

Use a LEFT JOIN (although it's more of a cross join in your case).

使用LEFT JOIN（尽管在你的情况下它更像是交叉连接）。

If your db supports it:

如果您的数据库支持它：

SELECT a.a, b.b
FROM a
CROSS JOIN b

If not, do something like:

如果没有，请执行以下操作：

SELECT a.a, b.b
FROM a
LEFT JOIN b ON ( 1=1 )

However, once you have more rows in a or b, this will return the cartesian product:

但是，一旦a或b中有更多行，这将返回笛卡尔积：

#3

This will actually give you what you're looking for, but if you only have one row per table:

这实际上会为您提供所需的内容，但如果每个表只有一行：

select 
(select A from tableA) as A 
, (select B from TableB) as B

#4

try this:

尝试这个：

select a, (select b from b) from a
union
select b, (select a from a) from b

should retrieve you all the existing data.

应检索所有现有数据。

you can filter it more by surrounding it with another select

你可以用另一个选择包围它来更多地过滤它

#1