Problem combining result of two different queries into one

Question

I have two tables (TableA and 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:

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

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?

I am using MS SQL Server 2005.

Answer 1

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

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:

1  1
1  2
2  1
2  2

Answer 2

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

Answer 3

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:

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

(1 row(s) affected)

if you DELETE @TableB, the output is:

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

(1 row(s) affected)

Answer 4

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