1-内连接|INNER JOINS(在多张表格中检索数据)
- SELECT order_id,first_name,last_name
- FROM orders
- JOIN customers
- ON orders.customer_id=customers.customer_id
- 以上表示连接orders和customers两张表。
- 当SELECT后接customer_id时会出现执行错误,因为两张表都有此列,MYSQL无法确定从哪张表中获取此列。此时在列前加上前缀名即可,体现为orders.customer_id或者customers.customer_id。
- 另外,可以观察到orders和customers均出现多次,用别名使表述更简洁:
- SELECT order_id,first_name,last_name
- FROM orders o
- JOIN customers c
- ON o.customer_id=c.customer_id
2-跨数据库连接|Joining Across Databases(将分散在多个数据库中的表中的列合并)
此时查询的数据库是sql_store,即默认开头有USE sql_store。把sql_inventory中的products表和sql_store中的order_items进行连接得到:
- SELECT order_id,first_name,last_name
- FROM order_items oi
- JOIN sql_inventory.products p
- ON oi.product_id=p.product_id
3-自连接|SELF JOINS(将一张表与它自己连接)
如下图,sql_hr中有employees数据库,以employee_id区分:
每位employee有report对象,该report对象也用employee_id表示:
- USE sql_hr;
- SELECT *
- FROM employees e
- JOIN employees m
- ON e.reports_to=m.employee_id
4 -多表连接|Joining Multiple Tables(如何连接超过两张表)
5-复合连接条件|Compound Join Conditions(当存在我们无法用单一列来准确识别某张表里列的情况时)
表order_items中,order_id和product_id都有重复值:
观察表属性,黄键代表这两列都有主键,即存在复合主键:
将这两列值——order_id和product_id结合起来,就能唯一识别每个order item:
6-隐式连接语法|Implicit Join Syntax
以上的两条指令给出的结果一样。
第二条是隐式连接语法。当没有WHERE时,如下:
此时会出现笛卡尔积。
因此尽量使用显式连接语法,因为在显性语法中,将两个表通过JOIN连接后,会强制要求输入连接条件ON。如果之后没有输入连接条件ON,就会得到语法错误提示。
7-外连接|OUTER JOINS(有两种连接,INNER JOIN和OUTER JOIN:其中INNER JOIN常常省略使用为JOIN,OUTER JOIN分为LEFT JOIN和RIGHT JOIN)
以上得到的结果中只显示有order的customer,而当我们想看到所有的customer,不论他们有没有order时,应该怎么做?
这时可以用到外连接。
SQL有LEFT JOIN和RIGHT JOIN两个外连接,使用LEFT JOIN时,所有左表的记录,也就是customers,会被返回,不论条件c.customer_id=o.customer_id正不正确,此时我们会得到所有的customer。
eg:
8-多表外连接|Outer Join Between Multiple Tables
理解:把第一次LEFT JOIN之后的数据当做一个表,再进行第二次LEFT JOIN连接。
最好避免使用右连接,因为当在连接多表,并且同时有左、右和内连接时,情况会变得复杂。
eg:
9-自外连接|Self Outer Joins
10-USING字句|The USING Clause
如何简化ON之后的查询条件?
如果两个表中有列名称是完全一样的,此时可以运用USING。
eg:
11-自然连接|Natural Joins(不建议使用)
使用自然连接时,数据引擎会自己看着办,基于共同的列连接:
12-交叉连接|Cross Joins(结合或连接第一个表的每条记录和第二个表的每条记录)
真正用到交叉连接的案例是,当下有一个型号表(小中大),还有个颜色表(红蓝绿),现在要把所有的型号跟所有的颜色结合。
显式连接:
隐式连接(参照前文第6节):
13-联合|Unions(之前学习的是结合多张表的列,SQL也可以结合多张表的行)
以下为查找表orders中所有2019年之后的order:
现在在order_date旁边插入一列:如果订单是2019年的,则显示为Active;如果订单是2019年以前的,则显示为Archive:
eg1:
想让查询返回的列的数量一定要相等(以上是都只返回一列)。
eg2: