13.1 使用表别名
SQL除了可以对列名和计算字段使用别名,还允许给表名起别名,理由:
- 缩短SQL语句
- 允许在一条select语句中多次使用相同的表
# 使用表别名
select cust_name, cust_contact
from customers as C, orders as O, orderitems as OI
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
13.2 使用不同类型的联结
我们之前讲了内联结,还有其他三种联结:
自联结、自然联结、外联结。
自联结
使用表别名的一个主要原因是能在一条select语句中不止一次引用相同的表。
# 要给与JimJones同一公司的所有顾客发送一封信件
# 子查询方式
select cust_id, cust_name, cust_contact
from customers
where cust_name = (select cust_name
from customers
where cust_contact = 'Jim Jones');
# 自联结方式
select C1.cust_id, C1.cust_name, C1.cust_contact
from customers as C1, customers as C2
where C1.cust_name = C2.cust_name
and C2.cust_contact = 'Jim Jones';
许多DBMS处理联结要远比处理子查快得多。
自然联结
内联结会返回所有数据,相同的列甚至多次出现。但是自然联结排除多次出现,使每一列只返回一次。
而这个工作只能由自己完成,即自然联结要求只能选择那些唯一的列,一般通过对一个表使用通配符select * ,而对其他表的列使用明确的子集来完成。
# 自然联结
select C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
from customers as C, orders as O, orderitems as OI
where C.cust_id = O.cust_id
and OI.order_num = O.order_num
and prod_id = 'RGAN01';
在这里,通配符只对一个表使用,所有其他列都明确列出,所以没有重复的列被检索出来。
外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时还需要包含没有关联行的那些行,这种联结叫外联结。
# 检索所有顾客及其订单
# 内联结
select customers.cust_id, orders.order_num
from customers
inner join orders on customers.cust_id = orders.cust_id;
# 外联结(检索包括没有订单顾客在内的所有顾客)
select customers.cust_id, orders.order_num
from customers
left outer join orders on customers.cust_id = orders.cust_id;
其中cust_id = 1000000002 即为两个表没有关联的行。
在使用OUTER JOIN 语法时,必须使用RIGHT或LEFT关键字指定包括其所有行的表(RIGHT指的是OUTER JOIN右边的表,LEFT则相反),上面例子是左外联结,说明将customers表中所有行都要被选择。
# 右外联结
select customers.cust_id, orders.order_num
from customers
right outer join orders on customers.cust_id = orders.cust_id;
此外还有一种叫做全外联结,即检索两个表中的所有行并关联那些可以关联的行。
但是MariaDB、MySQL和SQLite不支持FULL OUTER JOIN语法。
13.3 使用带聚集函数的联结
聚集函数也可以与联结一起使用
# 带聚集函数的联结
select customers.cust_id, count(orders.order_num) as num_ord
from customers
inner join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
select customers.cust_id, count(orders.order_num) as num_ord
from customers
left outer join orders on customers.cust_id = orders.cust_id
group by customers.cust_id;
13.4 使用联结和联结条件
汇总一下联结及其使用的要点
- 注意所使用的联结类型。
- 关于确切的联结语法,查看相应的DBMS文档
- 保证使用正确的联结条件,否则会返回不正确的数据
- 应该总是提供联结条件
- 在一个联结中可以包含多个表,甚至可以对每个联结采用不同的联结类型。