Join is a record set generated by the records with the same conditions in the two tables after the two tables are connected. union is the two record sets generated (the fields must be the same) and together, become a new record set
Join is a record set generated by the records with the same conditions in the two tables after the two tables are connected. union is the two record sets generated (the fields must be the same) and together, become a new record set
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:
The Code is as follows:
SELECT msp. name, party. name
FROM msp JOIN party ON party = code
Or
The Code is as follows:
SELECT msp. name, party. name
FROM msp inner JOIN party ON party = code
Left join:
The Code is as follows:
SELECT msp. name, party. name
FROM msp left join party ON party = code
Right join:
The Code is as follows:
SELECT msp. name, party. name
FROM msp right join party ON msp. party = party. code
Full join ):
The 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
Table 1.2 Table2 database table
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
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
Table 2.2 Table4 database table,
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
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.
