Joining All Rows of Two Tables in SQL Server

Question

My goal is combining all rows in 2 tables. The simplest example I can think of is:

Table 1

Letter
A
B

Table 2

Number
0
1

Combined Table

Letter  Number
   A      0
   B      0
   A      1
   B      1

I have come up with this SQL statement:

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

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

Answer 1

Easy enough with CROSS JOIN...

SELECT *
FROM Table1
CROSS JOIN Table2

Result:

Letter                    Number
------------------------- -----------
A                         0
B                         0
A                         1
B                         1

(4 row(s) affected)

Answer 2

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

    SELECT * 
    FROM 
    (
       VALUES 
       ('A'), 
       ('B')
    )
    AS ch1(ch)
    JOIN
    (
        SELECT * 
        FROM 
        (
          VALUES 
          (0), 
          (1)
        )
        AS dg1(dg)
    ) TBL 

    ON 1 = 1