SQL JOIN and UNION usage example

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.

The result set of the left outer join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

Right join or right outer join.
The right outer join is the reverse join of the left outer join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

Full join or full outer join.
The complete external join operation returns all rows in the left and right tables. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

Union in SQL

Note that the SELECT statement inside the UNION must have the same number of columns. Columns must also have similar data types. In addition, the columns in each SELECT statement must be in the same order.

SQL statement
Inner join:
SELECT msp. name, party. name
FROM msp JOIN party ON party = code
Or
SELECT msp. name, party. name
FROM msp inner JOIN party ON party = code

Left join:
SELECT msp. name, party. name
FROM msp left join party ON party = code

Right join:
SELECT msp. name, party. name
FROM msp right join party ON msp. party = party. code

Full join ):
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

SQL UNION syntax
SELECT column_name (s) FROM table_name1
UNION
SELECT column_name (s) FROM table_name2
Note: By default, the UNION operator selects different values. If repeated values are allowed, use UNION ALL