Shulou(Shulou.com)06/01 Report--

In relational database system, in order to satisfy the third normal form (3NF), it is necessary to separate the tables satisfying "transitive dependency" into separate tables, and connect the related tables through Join clause. There are three types of Join clause: outer join, inner join, and cross join; outer join is divided into left join, right join, and full join; inner join is inner join, and cross join is cross join.

I. Composition of the Join Clause

The Join clause consists of a join table, a join type, and an On clause. The pseudocode is as follows:

from Left_Table[inner|left|right|full] join Right_Table [on condition]

1. Divide the two tables participating in Join into left table and right table according to their positions.

In the Join clause, the left table and the right table perform Cartesian set operation, any row in the left table is "combined" with all rows in the right table to generate a virtual table, and the total number of data rows in the virtual table Rows (VT)=Rows(left_table)*Rows(right_table);

The left table and the right table perform the Join operation in no order, which is different from the Apply clause. The left table of the Apply clause performs the operation before the right table;

2. Connection type

In an outer join, the left, right, and full keywords identify the "reserved table" of the Join clause: when performing an outer join query, all data in the reserved table is returned and will not be filtered by the on clause.

3, On clause, used in all virtual tables for filtering

In the on clause expression, the commonly used operator is equal (=), but also can use unequal (>,), like and other operators, the return result is Boolean;

The operand of an on clause expression, which can be a column, a constant, an expression, for example;

on left_table.column=right_table.column

on left_table.column=value

on left_table.column+xx=value

Do not filter: for example, set on 1=1

4, On clause determines order of Join

If a query contains more than one Join clause, the On clause determines the order in which the Join clauses are executed; the order in which the Join is executed is tb and tc followed by ta and tb.

from taleft join tbleft join tc on tb.column=tc.column on ta.column=tb.column

5, On clause filtering and Where clause filtering

The On clause precedes the where clause in order of execution. When filtering, the On clause cannot filter the reserved table, but the where clause can filter the reserved table.

For inner joins, filtering in the On clause and in the where clause is the same, since there is no reserved table, but it is recommended to clearly distinguish between the roles of the where clause and the on clause, the on clause for filtering the joined virtual table, and the where clause for filtering the final result set.

For example: in the On clause, ta.column2= value1 will not filter the left table ta, if this condition is not met, then the corresponding data column of the right table is set to NULL, and the left keyword ensures that all data rows in the left table are returned;where clause (ta.column3= value2) filters the left table ta;

from taleft join tb on ta.column1=ta.column1 and ta.column2=value1where ta.column3=value2

Second, create test code

View Code

Third, left join

1, left join algorithm

Take the left table as a reserved table, return all the data of the left table, and return NULL for the data rows that do not match the on clause condition in the right table;

select * from dbo.ta a left join dbo.tb b on a.a=b.ca

2. Filter the left table with constants

In the left outer join, the left table will return all the data. For "and left_table.column=value", when the first condition is satisfied, the returned results will be filtered, while the left table data will be returned completely. When the condition is not satisfied, the right table data will be set to NULL;

select * from dbo.ta a left join dbo.tb b on a.a=b.ca and a.a=1

3, use the where clause to filter the left table

The where clause is the last Filter to filter the result set

select * from dbo.ta a left join dbo.tb b on a.a=b.ca where a.a=1

4, use the where clause to filter the right table

If you use the where clause to filter the right table, it can generally be converted to an inner join.

select * from dbo.ta a left join dbo.tb b on a.a=b.ca where b.ca=1

4, right join

The right join algorithm takes the right table as a reserved table, displays all the data in the right table, and sets the field value to NULL for the data rows that cannot be matched in the left table;

select * from dbo.ta a right join dbo.tb b on a.a=b.ca

V, inner join

The algorithm is: inner join does not retain tables, only returns data rows that meet the on clause conditions, and does not return data rows that do not meet the on clause conditions.

select * from dbo.ta a inner join dbo.tb b on a.a=b.ca

6, full join

The algorithm is: full join takes both the left table and the right table as reserved tables. If the data rows in the left table and the right table meet the On clause condition, then the data row data is displayed. If they do not match, the corresponding fields are set to null.

select * from dbo.ta a full join dbo.tb b on a.a=b.ca

7, cross join

The algorithm is: cross join is the Cartesian product of the left table and cursor, cross join has no on clause, Cartesian product is to combine any row of data in the left table with all rows of data in the right table, cross join will be Cartesian product after the result is directly displayed

select * from dbo.ta a cross join dbo.tb b

Eight, self-connecting for cumulative summation

Self-join refers to a table and its own join, for example, the following statement, table dbo.ta and its own inner join, calculate the cumulative sum of the b field.

select t1.a,sum(t2.b) as b from dbo.ta t1 inner join dbo.ta as t2 on t1.a>=t2.a group by t1.a

In the actual product environment, self-connection is often used to calculate the cumulative sum, for example, there is a Table as follows: dbo.FinanceMonth, the output of each month is Quantity, calculate the cumulative value of Quantity in all months up to this month within a year.

View Code

Calculate cumulative values using self-linking

select a.MonthNum,sum(b.quantity) as TotalQuantity from dbo.FinanceMonth a inner join dbo.FinanceMonth b on a.MonthNum>=b.MonthNum group by a.MonthNum order by a.MonthNum

9, apply

Difference between join and apply

join clause left table and right table calculation is not divided into order, from the performance consideration, it is best to use small table as the left table, when the right table data volume, will reduce the query time consumption. The left and right tables of the apply clause are sequential, and apply evaluates the left table first and then the right table, so the apply clause is not a set operation statement. If the right table is a table-valued function, apply will first take the value of a row of records in the left table and pass it to the table-valued function as a parameter value for calculation. The Cartesian product of a row of records in the left table and the "right table" is the final result. If the result of the right table query is null, then the right table field is set to null.

select * from dbo.ta a outer apply (select * from dbo.tb b where a.a=b.ca) p

From the query results, it is the same as left join, but in terms of performance, outer apply is worse than left join, because TSQL is good at set operations, code written using set ideas generally has high performance, left join is a set operation statement, performance is better than outer apply

Although apply has low performance, it has its uses, and is the best choice when you need to connect sequentially.

2, two uses of apply

The similarities between outer apply and cross apply are:

First calculate the left table, then calculate the right table;

For each row of records in the left table, the right table needs to be calculated "row by row", similar to related subqueries. In fact, after TSQL optimizes apply, it is not row by row, but N rows by N rows;

The difference between outer apply and cross apply is:

outer apply: The left table is reserved, and if the right table has no matching rows, the fields in the right table are set to null, similar to left join.

cross apply: No table is reserved. For a row in the left table, if there is no matching row in the right table, the row will not be displayed in the final result set, similar to inner join.

select * from dbo.ta a cross apply (select * from dbo.tb b where a.a=b.ca) p

10. Application of join statement

1, cross join can quickly generate a large number of sequential numbers

The number of rows in the result set of cross join is the product of the number of rows in the left table and the number of rows in the right table. Since each table has 10 numbers (from 0 to 9), cross join of 4 tables can quickly generate 10 to the fourth power, that is, 10000 sequential numbers.

;with num as ( select n from(values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) as p(n) ) select a.n+b.n*10+c.n*100+d.n*1000 as n --into dbo.num from num across join num b cross join num c cross join num d order by n

2. Use left join to query data rows that do not exist in the right table

If the data in the left table does not exist in the right table, then the field in the right table is null. By setting filter in the where clause, you can query the data rows that exist in the left table but do not exist in the right table.

select * from dbo.ta t1 left join dbo.tb t2 on t1.a=t2.ca where t2.ca is null;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.