SQL 多表连接查询

• First attempt: List the property numbers viewed by client number ‘CR56’:

• But we’d like to see the client name & property details • So we’ll need to access Client and PropertyForRent for names etc...

• We now have two table names in the FROM clause • Note use of Table.ColumnName to avoid ambiguity in column names 使用Table.ColumnName来避免重名

• The two AND clauses are called join criteria 两个AND子句成为连接条件

• Users shouldn’t have to know about internal keys...

Using Table Aliases • Table aliases can help reduce amount of typing • The following is identical to the previous query:可以使用表的简称来减少字符数

FROM 表名 表简称

Natural Joins • Natural joins implement relationships in the Relational model • The DBMS will know which columns are key columns • The following is equivalent to the previous query:自然连接输出属性上取值相同的元组对即有同一列名上值相等该行的数据

Cross Joins (Cartesian Products)交叉连接 • Cartesian Product: a join with no WHERE clause SELECT * FROM Left, Right;• Useful for: — query optimisation — data mining 优化查询和数据挖掘 将左右两边数据依次关联 左边第一行数据对应右边第一直到第n行数据 左边第二行直到第n行数据依次类推

 

Theta Joins

• For all clients, list the properties that each client can afford to rent:

• The DBMS could implement theta joins by: — First forming a cross join to give... — An intermediate (Cartesian product) table... — Then applying WHERE clause to find matching rows

Self-Joins • Sometimes it is useful to join a table to itself (must use aliases)

在使用自连接时，必须将使用表的两个不同的简称

Outer Joins – Selecting Unmatched Rows • Example: List the branch offices and properties in the same city, along with any unmatched branches:外连接的左连接 以左边为主 输出左边关系以及右边与左边条件值相同的属性 如果不同则输出NULL

右连接 全连接 输出左右两边关系如果有两边条件值相同的属性则输出 如果不同输出NULL

Why So Many Types of Join? • Theta join – a join using a simple WHERE predicate • Equi join – a special case of theta join (= predicate) • Natural join – special case of equi join (match keys) • Semi join – theta join that outputs from just one table • Self join – joining a table to itself • Outer join – a join that may include unmatched rows • Cross join – Cartesian products (no predicates) • Question: Why do we need to distinguish so many different types of join? • Answer: Queries with different joins are often optimised differently..