Federated query and connection query of database

http://blog.csdn.net/memgxingfeixiang/article/details/52765208

1.JOIN and Union differences
A join is a record set that is produced by a part of the same condition in which two tables are connected.
The Union is the resulting two recordset (the field is the same) and together, becomes a new recordset.

Join is used to join two tables on an on condition, with four main types:
INNER join: Internally joins records from two tables, and the inner join returns rows only if there is at least one row that belongs to two tables that matches the join condition. I understand that as long as the record does not meet the on condition, it is not displayed in the result set.
Left Join/left OUTER join: Outer joins records from two tables and contains all records from the table on the right. If a record in the left table does not have a matching record in the right table, all select list columns in the right table in the associated result set are null values. Understand that even if the on condition is not met, the records in the left table are all displayed, and the right table field of the record in the result set is a null value.
Right join/right OUTER join: Outer joins records from two tables, and contains all records in the table on top. Simply put, and leftjoin in turn.
Full Join/full OUTER join: The complete outer join returns all rows from the left and right tables. is Leftjoin and Rightjoin and merging, both the left and right table data are all displayed.

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

SQL notation
Internal Connection Innerjoin:
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 Connect Leftjoin:
SELECT Msp.name, Party.name
From MSP left JOIN party on Party=code

Right Connect Rightjoin:
SELECT Msp.name, Party.name
From MSP right JOIN party on Msp.party=party.code

Full Connection (Fulljoin):
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 Union 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 of the different usages are union and UnionAll, except that the union removes duplicate rows from the result set. If you use UnionAll, all rows will be included and duplicate rows will not be deleted.

The difference between union and UnionAll:
Union Check Duplicates
Union ALL does 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. The difference between 2 can be clearly seen and understood through the following example
Example 1 typical two-table connection demo

Suppose there are two tables Table1 and Table2, which contain columns and data, as shown in Table 1.1 and Table 1.2, respectively.

Table 1.1table1 Database tables

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

Table 1.2table2 Database tables

ColumnA	Columnd	Columne
X1	D1	E1
X2	D2	E2
X3	D3	E3

Table1 and Table2 tables share the column ColumnA, if the value of the ColumnA column is connected Table1 and Table2 two tables, that is, the connection condition is table1.columna= Table2.columna, the resulting connection results are shown in Table 1.3.

Table 1.3 Connecting the 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 expressed as follows: select* from Table1 JOIN Table2 on Table1.columna=table2.columna

Example 2 A typical two-table record of the Union operation

Suppose there are two tables Table3 and Table4, which contain columns and data, as shown in table 2.1 and Table 2.2, respectively.

Table 2.1table3 Database tables

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

Table 2.2table4 Database tables,

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

The Table3 table and the Table4 table have the same column structure, the same number of columns, the column name can be different, the column name of the first table is the column name of the new table, so you can use the union operator to concatenate two table recordsets, and the resulting connection results are shown in table 2.3.

Table 2.3 using union to connect records of TABLE3 and Table4 tables

columna	columnb	COLUMNC
X1	Y1	Z1
X2	Y2	Z2
X3	Y3	Z3
X4	Y4	Z4
< p>x5	Y5	Z5
X6	Y6	Z6

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

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

Federated query and connection query of database