Multi-table joint query (join, Union, etc)

 

Combination of inner join tables

Full outer -- full join. The two tables are combined in the same way. Table A has data that table B does not have (it is displayed as null), and table B has

Table A does not display (null)

Table A left join table B-left join, which is based on table A. All data in Table A and B combination. Null is not found.

Table A right join table B -- right join, which is based on table B, all data of Table B, and some combinations of Table. Null is not found.

 

Query analyzer execution:

-- Create Table Table1, Table2:

Create Table Table1 (ID int, name varchar (10 ))

Create Table Table2 (ID int, score INT)

Insert into Table1 select 1, 'lil'

Insert into Table1 select 2, 'zhang'

Insert into Table1 select 4, 'wang'

Insert into Table2 select 1, 90

Insert into Table2 select 2,100

Insert into Table2 select 3, 70

Such as table

-------------------------------------------------

Table1 | Table2 |

-------------------------------------------------

Idname | idscore |

1lee | 190 |

2zhang | 2100 |

4wang | 370 |

-------------------------------------------------

Run the following commands in the query Analyzer:

1. External Connection

1. Concept: including left Outer Join, right Outer Join or complete external join

2. Left join: left join or left Outer Join

(1) 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 the selection list columns in the right table in the row of the associated result set are null ).

(2) SQL statements

Select * From Table1 left join Table2 on table1.id = table2.id

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang2100

4 wangnullnull

------------------------------

Note: all the clauses containing Table 1 return the corresponding fields of Table 2 based on the specified conditions. The non-conforming fields are displayed as null.

 

3. Right join: Right join or right Outer Join

(1) 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.

(2) SQL statements

Select * From Table1 right join Table2 on table1.id = table2.id

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang2100

Nullnull370

------------------------------

Note: all the clauses containing Table 2 return the corresponding fields of Table 1 Based on the specified conditions. The non-conforming fields are displayed as null.

 

4. Complete External join: Full join or full outer join

(1) The Complete External join 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.

(2) SQL statements

Select * From Table1 full join Table2 on table1.id = table2.id

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang2100

4 wangnullnull

Nullnull370

------------------------------

Note: returns the sum of left and right connections (see upper left and right connections)

 

2. Internal Connection

1. Concept: inner join is a join that uses a comparison operator to compare the values of the columns to be joined.

2. Inner join: Join or inner join

3. SQL statements

Select * From Table1 join Table2 on table1.id = table2.id

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang2100

------------------------------

Note: Only the Table1 and Table2 columns that meet the conditions are returned.

 

4. equivalent (same as the following execution)

A: select a. *, B. * From Table1 A, Table2 B where a. ID = B. ID

B: Select * From Table1 cross join Table2 where table1.id = table2.id (Note: cross join can only use where, not on)

 

Iii. Cross-join (complete)

1. Concept: A cross join without a where clause will generate the Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table is equal to the size of the Cartesian result set. (Table1 and Table2 generate 3*3 = 9 records)

2. Cross join: cross join (without the condition where ...)

3. SQL statements

Select * From Table1 cross join Table2

------------- Result -------------

Idnameidscore

------------------------------

1lee190

2zhang190

4wang190

1lee2100

2zhang2100

4wang2100

1lee370

2zhang370

4wang370

------------------------------

Note: 3*3 = 9 records are returned, that is, Cartesian product.

4. equivalent (same as the following execution)

A: Select * From Table1, Table2

 

Union indicates the Union. Union and join are different. The Union operator merges data columns and joins columns!

If the column is merged, full join is the union, and inner join is the intersection! Left join or right join is not completely a difference set, but also contains the result of intersection. The query result of your statement depends on the actual statement, for example, cross join, when the WHERE clause is used, it becomes an inner join.

 

I searched the internet and found some new things:

Union: Union select × from a union (all) Select × from B Aub

Intersection: intersect select × from a intersect select × from B anb

Difference set: minus select × from a minus select × from B a-B