Database connections: Union, Union all, inner jion, left jion, right jion, and cross jion

Connection query category

1. Self-join query, connecting to the same table
2. internal join query, [divided into: Natural join (that is, equivalent join removes duplicate rows, or select the column to be represented after select, instead of using "*" to list all columns), equijoin (that is, use "=" for logical judgment after where), non-equijoin]

[During inner join, the returned result set is only the rows that meet the query and connection conditions .]
3. External Connection query, which can be divided into three types: left outer connection, right outer connection, and full outer connection]

【When an external join is used, it returns to the result set that contains not only rows that meet the connection conditions, but also all data rows of the specified external join table.]
4. Cross-join queries are also unconditional queries.

Return the Cartesian set of the table]
5. Joint Query

[Returns a union instead of an intersection]

Example Table

T1

ID name sales date

1 LOS 1500 00:00:00. 000
2 San 250 00:00:00. 000
3 los 320 00:00:00. 000
4 Boston 800 00:00:00. 000

T2

Id sales date

1 250 00:00:00. 000
2 535 00:00:00. 000
3 320 00:00:00. 000
4 750 00:00:00. 000

Multi-table join

1. Self-connection Query

Syntax :【 Select  Table 1. Field name 1 , Table 2. Field name 2 , ... From  Table 1 , Table 2 Where  Connection Conditions ]

Select * fromT1, T2 whereT1.sales = t2.sales

ID name sales date ID sales date
2 San 250 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000

2. query internal connections

Syntax :【Select  Table 1. Field name 1 , Table 2. Field name 2 , ... From table 1  Join_type  Table 2  [On ( Connection Conditions )] ]

Inner join

Equijoin

Select * from T1 inner join T2On t1.sales = t2.sales

ID name sales date ID sales date
2 San 250 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000

Non-equivalent join

Select * from T1 inner join T2 On t1.sales! = T2.sales

ID name sales date ID sales date
1 LOS 1500 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 1 250 00:00:00. 000
4 Boston 800 00:00:00. 000 1 250 00:00:00. 000
1 LOS 1500 00:00:00. 000 2 535 00:00:00. 000
2 San 250 00:00:00. 000 2 535 00:00:00. 000
3 los 320 00:00:00. 000 2 535 00:00:00. 000
4 Boston 800 00:00:00. 000 2 535 00:00:00. 000
1 LOS 1500 00:00:00. 000 3 320 00:00:00. 000
2 San 250 00:00:00. 000 3 320 00:00:00. 000
4 Boston 800 00:00:00. 000 3 320 00:00:00. 000
1 LOS 1500 00:00:00. 000 4 750 00:00:00. 000
2 San 250 00:00:00. 000 4 750 00:00:00. 000
3 los 320 00:00:00. 000 4 750 00:00:00. 000
4 Boston 800 00:00:00. 000 4 750 00:00:00. 000

Natural connection

SelectT1.sales, t1.dateFrom T1 inner join T2On t1.sales = t2.sales

Sales date
250 00:00:00. 000
320 00:00:00. 000

Left join

Select * from T1 left join T2 on t1.sales = t2.sales

ID name sales date ID sales date
1 LOS 1500 00:00:00. 000 null
2 San 250 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000
4 Boston 800 00:00:00. 000 null

Right join

Select * from T1 right join T2 on t1.sales = t2.sales

ID name sales date ID sales date
2 San 250 00:00:00. 000 1 250 00:00:00. 000
Null null 2 535 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000
Null null 4 750 00:00:00. 000

Full outer join

Select * from T1 full outer join T2 on t1.sales = t2.sales

1 LOS 1500 00:00:00. 000 null
2 San 250 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000
4 Boston 800 00:00:00. 000 null
Null null 2 535 00:00:00. 000
Null null 4 750 00:00:00. 000

Cross join

Select * from T1 cross join T2

ID name sales date ID sales date
1 LOS 1500 00:00:00. 000 1 250 00:00:00. 000
2 San 250 00:00:00. 000 1 250 00:00:00. 000
3 los 320 00:00:00. 000 1 250 00:00:00. 000
4 Boston 800 00:00:00. 000 1 250 00:00:00. 000
1 LOS 1500 00:00:00. 000 2 535 00:00:00. 000
2 San 250 00:00:00. 000 2 535 00:00:00. 000
3 los 320 00:00:00. 000 2 535 00:00:00. 000
4 Boston 800 00:00:00. 000 2 535 00:00:00. 000
1 LOS 1500 00:00:00. 000 3 320 00:00:00. 000
2 San 250 00:00:00. 000 3 320 00:00:00. 000
3 los 320 00:00:00. 000 3 320 00:00:00. 000
4 Boston 800 00:00:00. 000 3 320 00:00:00. 000
1 LOS 1500 00:00:00. 000 4 750 00:00:00. 000
2 San 250 00:00:00. 000 4 750 00:00:00. 000
3 los 320 00:00:00. 000 4 750 00:00:00. 000
4 Boston 800 00:00:00. 000 4 750 00:00:00. 000

 

The Union command combines multiple tables and does not display connections.Union only combines result sets, rather than intersection.

The limit of union is that columns in multiple tables must be of the same type.

The Union result set automatically executes distincd to remove duplicate columns.

Union all indicates that duplicate columns are retained, and the Union of multiple result sets is displayed completely.

See the instance below

Run: Select date from T1Union allSelect date from T2

Date
00:00:00. 000
00:00:00. 000
00:00:00. 000
00:00:00. 000
00:00:00. 000
2011-01-10 00:00:00. 000
00:00:00. 000
00:00:00. 000

We can see the complete reality.Eight rows

Run: Select date from T1UnionSelect date from T2

Date
00:00:00. 000
00:00:00. 000
00:00:00. 000
2011-01-10 00:00:00. 000
00:00:00. 000
00:00:00. 000

Only 6 rows are displayed, and only one unique value is retained for the 7 or 8 months with repeated dates.

If you run: Select date, sales from T1UnionSelect date, sales from T2

Date sales
00:00:00. 000 1500
00:00:00. 000 250
00:00:00. 000 320
00:00:00. 000 800
2011-01-10 00:00:00. 000 535
00:00:00. 000 320
2011-01-12 00:00:00. 000 750

It can be seen that 250 of the data for sales was duplicated in January, and only one

If you run: Select date, sales from T1Union allSelect date, sales from T2

Date sales

00:00:00. 000 1500
00:00:00. 250
00:00:00. 000 320
00:00:00. 000 800
00:00:00. 000 250
00:00:00. 000 535
00:00:00. 000 320
00:00:00. 750 million