Join and Union Differences in SQL, usage, and examples introduction _mssql

1.JOIN and Union differences

A join is a set of records in which the same conditions are produced when two tables are connected,
The Union is the resulting two recordset (the fields are the same) and together to become a new recordset.

Join is used to join two tables according to the on condition, with four main types:
INNER join: An inner join is a record in two tables, and the inner join returns rows only if at least one of the rows that belong to the two tables conforms to the join condition. I understand that as long as the record does not conform to the on condition, it is not displayed in the result set.
Left Join/left OUTER join: Outer joins the records from the two tables and contains all the records from the left-hand table. If a record in the left table does not have a matching record in the right table, all picklist columns in the right table of the associated result set are null values. It is understood that even if the on condition is not met, the records in the left table are all displayed and the right table field for that class of records in the result set is null.
Right join/right OUTER join: Outer joins the records from the two tables and contains all the records in the right-hand table. Simply put, and the left join in turn.
Full Join/full OUTER join: Complete outer joins return all rows in the left and right tables. is left join and right join and merge, and the data for both tables is displayed.

basic syntax for join:
Select table1.* from table1 JOIN table2 on Table1.id=table2.id

SQL notation

INNER JOIN INNER JOIN:

Copy Code code as follows:

SELECT Msp.name, Party.name
From MSP JOIN Party on Party=code

Or

Copy Code code as follows:

SELECT Msp.name, Party.name
From MSP Inner JOIN party on Party=code

Left join take LEFT join:

Copy Code code as follows:

SELECT Msp.name, Party.name
From MSP left JOIN party on Party=code

Right-side join:

Copy Code code as follows:

SELECT Msp.name, Party.name
From MSP right JOIN party on Msp.party=party.code

Fully connected (full join):

Copy Code code as follows:

SELECT Msp.name, Party.name
From MSP full JOIN party on Msp.party=party.code

Union operator

Combines the result set of two or more queries into a single result set that contains all the rows of all the queries in the federated query. The result set column name of the union is the same as the column name of the result set of the first SELECT statement in the Union operator. The result set column name of another SELECT statement is ignored.
Two different usages are union and union all, except that the union deletes duplicate rows from the result set. If you use union ALL, all rows will be included and duplicate rows will not be deleted.

the difference between Union and union all:

Union Check Repeat
UNION ALL do not check
For example, select ' A ' union select ' A ' output is a row a
For example, select ' A ' union ALL select ' A ' output is two lines a

2. Through the following examples, you can clearly see and understand the difference between 2 people
Example 1 typical two-table connection demo

Suppose you have two tables Table1 and Table2 that 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

The columns shared by TABLE1 and Table2 tables are ColumnA, if the Table1 and Table2 two tables are connected by ColumnA column values, that is, the join condition is table1.columna= Table2.columna, the resulting connection results are shown in Table 1.3.

Table 1.3 connecting Table1 and Table2 tables

ColumnA	ColumnB	Columnc	Columnd	Columne
X1	Y1	Z1	D1	E1
X2	Y2	Z2	D2	E2
X3	Y3	Z3	D3	E3

The implementation code for the above connection process can be represented as follows: SELECT * from Table1 JOIN Table2 on Table1.columna=table2.columna

The union operation of the typical two-table record in instance 2

Suppose you have two tables Table3 and Table4 that contain columns and data, as shown in table 2.1 and table 2.2.

Table 2.1 Table3 database table

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

Table 2.2 Table4 Database tables,

ColumnA	Columnd	Columne
X4	Y4	Z4
X5	Y5	Z5
X6	Y6	Z6

Table3 tables and TABLE4 tables have the same column structure, the number of columns is the same, the column names can be different, the first table's column name is the column name of the new table, so you can use the union operator to connect the two-table recordset, and the resulting connection results are shown in table 2.3.

Table 2.3 Record of Table3 table and TABLE4 table using Union connection

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

The implementation code for the above connection process can be expressed as follows: SELECT * from Table3 UNION select *from Table4

Comparing instance 1 and instance 2, it is not difficult to find the difference between the two.