Combine two Tables in SQL. But what kind of join-CodePudding

I have two tables:

1. Column1 Column2
   X       4
   Z       7

2. Column1 Column1
   Bed     Pizza
   Sun     Hamburger

Now I want to combine these two tables and the output table should look like this:

3. Column1_1 Column2_1 Column_1_2 Column_2_2
   X         4         Null       Null
   Z         7         Null       Null
   Null      Null      Bed        Pizza
   Null      Null      Sun        Hamburger

CodePudding user response：

To get your desired output simply union your tables and provide NULL value placeholders. Column names are taken from the first query in the union:

select column1 Column1_1, column2 column2_1, null Column_1_2, null column_2_2
from t1
union all
select null, null, Column1, Column2
from t2

CodePudding user response：

To achive what you are asking for, you may want to use one of the following:

CROSS JOIN (the easiest way to explain is: it returns all records from both tables)

SELECT *
FROM table1 t1
CROSS JOIN table2 t2;

UNION (used for unique records) or UNION ALL (all records)

SELECT *
FROM table1 t1
UNION ALL
SELECT *
FROM table2 t2;

CodePudding user response：

This query to do this is not a join.

SELECT
Column1 Column1_1,
Column2 Column1_2,
null Column2_1,
null Column2_2
FROM table1
UNION ALL
SELECT
null,null,Columns,Columns
FROM table2;