CODEWITHSUNDEEP
sql
Sagar
Sagar

Reputation: 490

Combine 2 tables which doesn't have any relationship

I have couple of tables like below-

Table1:

A B C D <<Columns
1 2 3 4 <<single row

Table2:

W X Y Z << Columns
5 6 7 8 << Single row

I want to combine these 2 tables such a way that it will give me following result

Result:

P Q R S << Column headers
1 2 3 4 << row from table1
5 6 7 8 << row from table2

Expected result will have column headers as P, Q, R, S and row from table1 and row from table2

How to achieve this using SQL?

Upvotes: 2

Views: 53

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

  • UNION ALL will not eliminate duplicates
  • In set operations (UNION / INTERSECT / EXCEPT) the aliases are taken from the first query (Currently I'm aware of only one exception- Hive requires the aliases to be the same for all queries - I consider this as a bug)
select  A as P, B as Q, C as R, D as S
from    table1

union all

select  W,X,Y,Z
from    table2

+---+---+---+---+
| p | q | r | s |
+---+---+---+---+
| 1 | 2 | 3 | 4 |
| 5 | 6 | 7 | 8 |
+---+---+---+---+

table2 with 3 Columns

select  B as Q, C as R, D as S
from    table1

union all

select  X,Y,Z
from    table2

+---+---+---+
| q | r | s |
+---+---+---+
| 2 | 3 | 4 |
| 6 | 7 | 8 |
+---+---+---+

or

select  A as P, B as Q, C as R, D as S
from    table1

union all

select  null,X,Y,Z
from    table2

+--------+---+---+---+
|   p    | q | r | s |
+--------+---+---+---+
| 1      | 2 | 3 | 4 |
| (null) | 6 | 7 | 8 |
+--------+---+---+---+

Upvotes: 1

cFreed
cFreed

Reputation: 4484

_Updated to be more strict and more complete, thanks to @AntDC (and @Matt) and @Dudu Markovitz__

Use UNION with aliases, like this:

SELECT A AS P, B AS Q, C AS R, D AS S
FROM table1
UNION
-- or UNION ALL if you want to keep duplicate rows
SELECT W, X, Y, Z
FROM table2

Upvotes: 1

Related Questions