Difference, usage and example of JOIN and UNION in SQL

1. Difference Between JOIN and UNION

Join is a record set generated by the records with the same conditions in the two tables after the join operation,
Union is the two record sets generated (the fields must be the same) and are combined to form a new record set.

JOIN is used to JOIN two tables according to the ON condition. There are four types:
Inner join: records in two tables are joined internally. rows are returned only when at least one row in the two tables meets the JOIN conditions. As long as the record does not meet the ON condition, it is not displayed in the result set.
Left join/left outer join: records in two external JOIN tables, including all records in the LEFT table. If a record in the left table does not have a matching record in the right table, all the columns in the right table in the associated result set are null. It is understood that, even if the ON condition is not met, all records in the left table are displayed, and the field in the right table of this type of records in the result set is null.
Right join/right outer join: records in two external JOIN tables, including all records in the RIGHT table. In short, it is opposite to left join.
Full join/full outer join: The Complete External JOIN returns all rows in the left and right tables. That is, left join and right join and merge. All data in both the LEFT and RIGHT tables is displayed.

Basic JOIN Syntax:
Select table1. * FROM table1 JOIN table2 ON table1.id = table2.id

SQL statement

Inner join:
Copy codeThe Code is as follows:
SELECT msp. name, party. name
FROM msp JOIN party ON party = code

Or
Copy codeThe Code is as follows:
SELECT msp. name, party. name
FROM msp inner JOIN party ON party = code


Left join:
Copy codeThe Code is as follows:
SELECT msp. name, party. name
FROM msp left join party ON party = code

Right join:
Copy codeThe Code is as follows:
SELECT msp. name, party. name
FROM msp right join party ON msp. party = party. code

Full join ):
Copy codeThe Code is as follows:
SELECT msp. name, party. name
FROM msp full join party ON msp. party = party. code


UNION operator

Combines two or more query result sets into a single result set, which contains all rows of all queries in the Union query. The name of the UNION result set column is the same as the name of the result set of the first Select statement in the UNION operator. The names of the result set columns of another Select statement are ignored.
Two different operations are UNION and union all. The difference is that UNION deletes duplicate rows from the result set. If union all is used, ALL rows are included and duplicate rows are not deleted.

Difference between UNION and union all:

Union check already exists
Union all does not check
For example, the output of select 'A' union select 'A' is a row.
For example, select 'A' union all select 'A', and the output is two rows.

2. The following example clearly shows and understands the differences between the two.
Example 1 Typical two-table join demonstration

Assume that two tables, Table1 and Table2, contain columns and data, as shown in Table 1.1 and table 1.2.

Table 1.1 Table1 database table

ColumnA	ColumnB	ColumnC
X1	Y1	Z1
X2	Y2	Z2
X3	Y3	Z3

 

Table 1.2 Table2 database table

 

ColumnA	ColumnD	ColumnE
X1	D1	E1
X2	D2	E2
X3	D3	E3

 

Table 1 and Table 2 Share ColumnA columns. If you use the values of the ColumnA column to connect to tables Table1 and table 2, the join condition is Table1.ColumnA = Table2.ColumnA. The result of the connection is shown in Table 1.3.

Table 1.3 connects Table 1 and Table 2

ColumnA	ColumnB	ColumnC	ColumnD	ColumnE
X1	Y1	Z1	D1	E1
X2	Y2	Z2	D2	E2
X3	Y3	Z3	D3	E3

The implementation code of the above connection process can be expressed as follows: SELECT * FROM Table1 JOIN Table2 ON Table1.ColumnA = Table2.columnA

Example 2 Typical two-Table Record UNION operation

Assume that two tables, Table3 and Table4, contain columns and data, as shown in Table 2.1 and Table 2.2.

Table 2.1 Table 3 database table

 

ColumnA	ColumnB	ColumnC
X1	Y1	Z1
X2	Y2	Z2
X3	Y3	Z3

 

Table 2.2 Table4 database table,

 

ColumnA	ColumnD	ColumnE
X4	Y4	Z4
X5	Y5	Z5
X6	Y6	Z6

 

Table 3 and table 4 have the same column structure and the columns must be the same. The column names can be different. The column names in the first table are the new column names, therefore, you can use the UNION operator to join the record sets of two tables. The result of the join is shown in table 2.3.

Table 2.3 records connecting table 3 and table 4 using UNION

 

ColumnA	ColumnB	ColumnC
X1	Y1	Z1
X2	Y2	Z2
X3	Y3	Z3
X4	Y4	Z4
X5	Y5	Z5
X6	Y6	Z6

The implementation code of the above connection process can be expressed as follows: SELECT * FROM Table3 union select * FROM Table4

Compared with instance 1 and instance 2, it is not difficult to find the difference between them.