Database of Neusoft Training (5)

# #多表连接 #连接

A connection is an association between tables through a certain join condition, allowing data to be fetched from more than one table.

Syntax for

　　　　　　

Write the join condition in the WHERE clause.

If the same column name appears in more than one table, you need to use the table name as a prefix from the column name of the table.

When n tables are connected, at least one N-1 connection condition is required.

#连接的类型

　　　　　　　　

#笛卡尔积

The Cartesian product is: All rows in the first table and all rows in the second table are connected.

The Cartesian product is produced in the following cases:

• • • • The connection condition is omitted
      • The connection condition is invalid

In order to avoid the production of Cartesian product, it is usually necessary to include a valid join condition in the WHERE clause.

　　　　　　

#等值连接

　　　　　　　

#使用表的别名

　　　　　　　

#非等值连接

　　　　　　　

　　　　　　　

#多表连接的写法

1. Parses the tables from which the columns to query are from, constituting the FROM clause.
2. Analyze the association between these tables, if there is no direct association between the tables, but through another intermediate table association, then also in the FROM clause to supplement the Intermediate association table, and then in the WHERE clause to supplement the relationship between the table, usually n tables, at least N-1 to have an association relationship.
3. Analyze if there are additional constraints that are added to the table association relationship in the WHERE clause as a constraint.
4. Supplement the SELECT clause based on the information that the user wants to display.
5. To analyze whether there are sorting requirements, and if other tables are involved in the sort request, also take step 2nd to add an association between the tables.

#自身连接

Self-connection, also called self-connection, is a way for a table to connect through a condition and itself, as with multiple table joins.

#左外连接

The left outer join is the base table for the left table in the FROM clause, and all row data in the table is displayed according to whether the join condition matches the right table.

　　　　　　

#本章总结

• Why use a multi-table connection?
• Multi-table connection type
• Equivalent connection
• More than two tables of connections
• No equivalent connection
• Self-Connection
• Left outer connection
• Right outer connection
• The writing of various connections in SQL99

Database of Neusoft Training (5)