How to Join two SELECT queries having same column names but different row values in both tables

Question

I got a table from SELECT Query 1 as below from SQL Server:

NUMBER	NAME	Date
21	Name1	20.03.2004
25	Name2	26.06.2005
23	Name3	26.06.2005
24	Name4	22.04.2012

I got a table from SELECT query 2 as below from SQL Server:

NUMBER	NAME	Date
30	Name10	20.03.2064
30	Name10	26.06.2035
35	Name30	26.06.2025
36	Name40	22.04.2042

I want to join these SELECT queries into one SELECT query like below:

NUMBER	NAME	Date
21	Name1	20.03.2004
25	Name2	26.06.2005
23	Name3	26.06.2005
24	Name4	22.04.2012
30	Name10	20.03.2064
30	Name10	26.06.2035
35	Name30	26.06.2025
36	Name40	22.04.2042

I tried like this:

SELECT * FROM ( SELECT Number,Name,Date FROM table1 ) t1
INNER JOIN ( SELECT Number, Name, Date FROM table2) t2
ON t1.number = t2.number

But it didn't work, This is not a actual table I want to join.

Basically I want to join two SELECT Query who got same Column names but have no common values between them. And I want to use SELECT query from the joined table.

Thank you in advance.

Answer 1

UNION and UNION ALL are SQL operators used to concatenate 2 or more result sets. This allows us to write multiple SELECT statements, retrieve the desired results, then combine them together into a final, unified set.

The main difference between UNION and UNION ALL is that:

UNION: only keeps unique records
UNION ALL: keeps all records, including duplicates

UNION Example:

SELECT column1 AS datacheck from table1 
  UNION 
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
+-----------+

UNION ALL example:

SELECT column1 AS datacheck from table1 
  UNION ALL
SELECT column1 AS datacheck from table2

Result:

+-----------+
| datacheck |
+-----------+
|   data2   |
|   data2   |
+-----------+