[Original] Join Summary

Document directory

•  
• Example

Reference: SQL query beginner's guide, second edition, Mechanical Industry Press

Basic

The inner link is used to create a Cartesian Product for table A and table B in the unit of tuples, which is recorded as Table C. Then, in C, the entries that meet the constraints following the on statement are selected.

The left join is based on the internal join and adds the tuples in a but not in C. Since the number of columns in C is more than the number of columns in A, copy a on the left of the newly added tuples and null on the right.

The right link is based on the internal connection, and the tuples in B but not in C are also added. Because the number of columns in C is more than that in B, copy B to the right of the newly added tuples, and null to the left.

The so-called Full join is to add the left join to be added after the join result D, plus the right join to be added.

The so-called cross join tablename2 (select * From tablename1 cross join tablename2) and select * From tablename1, tablename2 are the same and get cartesian products. You can use select * From Table1, Table2 where... to simulate the join operation.

The so-called self-join means that the same table is connected to itself. This type of unary join is usually used to extract data from the self-inverse (also known as recursive) relationship. For example, the relationship between employees and bosses in the HR database.

Directly Writing join is an internal join, which is equivalent to inner join and inner can save

Write left join or right join is an external join, and outer can save

Example

There are two tables A and B

A

B

The following are some select operations.

Select * from a cross join

Select * from a join B on A. Name = B. Name

It is to further filter the result of Cartesian product, that is, the cross join above, and select the rows that meet a. Name = B. Name.

Only two rows meet the condition.

Adds a line based on the SELECT statement of the coat.

Select * from a left join B on A. Name = B. Name

Same as above

Select * from a right join B on A. Name = B. Name

Select * from a full join B on A. Name = B. Name

Advanced

Important theoretical basis:

A join clause enclosed in parentheses can be specified anywhere a table name can be specified.

In any place where a complete join statement of two tables appears, we can replace it with only one table name.

The so-called "a complete join statement for two tables", that is, the "Join clause", refers to a complete sentence such as "a join B on A. A = B. B ".

For example, if you want to connect the ABCDE five tables

You can do this:

Select * from a inner join (((

B inner join C on B. B = c. c)

Inner join D on B. B = D. D)

Inner Join e on D. D = E. E)

On a. A = E. E

You can also do this:

Select * from (((

A inner join B on A. A = B. B)

Inner join C on c. c = A.)

Inner join D on D. D = c. c)

Inner Join e

On E. E = D. D

You can also do this:

Select * from

A inner join B on A. A = B. B

Inner join C on c. c = A.

Inner join D on D. D = c. c

Inner Join e on E. E = D. D

We can first connect a and B, and then connect the result to C. Of course, if C is only related to B and not related to, we can also connect B and C first, and connect the result with A. As long as the relationship is correct, you can define nested join in any way.

However, most database systems analyze the entire from clause and try to determine the most effective method for connecting tables by combination. That is to say, the database does not necessarily start to execute queries from the brackets at the bottom. This may disrupt your logical design and lead to unexpected results.

In addition, some database system optimizers are sensitive to the sequence defined by join. If you find that it takes a long time to execute many join queries on a large database, changing the join sequence in an SQL statement may make it run faster.

Note: Sometimes, you only need a part of the data in Table A and Table C, but the relationship between table A and Table C can only happen through table B. Then you still need to connect table A and table B first, connect to C

A comprehensive example:

Select rcfiltered. classname, R. recipetitle

From

(Select recipeclassid,

Recipeclassdescription as classname

From recipe_classes as RC

Where RC. classname = 'salads' or RC. classname = 'soup' or RC. classname = 'main course ')

As rcfiltered

Left Outer Join recipes as R

On rcfiltered. recipeclassid = R. recipeclassid

The meaning of this example:

1. In most SQL implementations, we can replace any table name in the from clause with a complete SELECT statement. Of course, a related name must be assigned. In this example, the name allocated for the sub-select statement is rcfiltered, which is in row 7th.

2. When we decide to replace the table name with the SELECT statement, make sure that the select result contains not only the columns to appear in the final result, but also the columns to be joined. This is why we see both recipeclassid and recipeclassdescription in the embedded SELECT statement.

3. We gave the recipeclassdescription an alias classname, so we used the rdfiltered alias in the first line. Only the table rcfiltered is seen in the SELECT statement of the first row, and the table recipe_classes is not seen.