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)