创建联结

SQL语句1：SELECT vend_name, prod_name, prod_price FROM vendors, products ORDER BY vend_name, prod_name

vend_name字段是vendors表的，而prod_name和prod_price则是products表的，因此这里的查询其实是查询了“FROM vendors, products”，即查询了两个表。上面这个SQL语句就是一种联表查询，查询的列来自两个不同的表。由没有联结条件的表关系返回的结果为笛卡儿积，即检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。像这种返回笛卡尔积的联表查询就叫做叉查询，叉查询查询到的笛卡尔积在实际应用中并没有实际意义，因此联表查询一般都需要带查询条件的，比如：

SQL2：ELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendos.vend_id = products.vend_id ORDER BY vend_name, prod_name
image.png
image.png
可以看到SQL2相比SQL1多了一个WHERE的查询条件“vendos.vend_id = products.vend_id”，这样就限制了只会查询两个表中vendos.vend_id = products.vend_id的行。除了上面的查询条件可以使用“表名.列名”的格式，需要查询的列可以采用这种格式，比如：

SQL3：SELECT c.*, o.order_num, o.order_data, oi.prod_id, oi.quantity, oi.item_price FROM customers AS c, orders AS o, orderitms AS oi AND oi.order_num = o.order_num AND prod_id = ‘FB’

像这些使用别名的形式，可以很好的避免歧义，因此在联表查询中，由于涉及到多个表，最好能够尽量使用别名来避免歧义。

内部联结

SQL语句1：SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id

像前面那个SQL2是基于两个表之间的相等测试，这种联结可以称为等值联结，也被称为内部联结。我们可以使用一些语法上面的改变，来让它长得更加像内部联结，上面的SQL语句和前面的语句是一样的，上面的SQL只是使用了ON代替WHERE指定了联结条件。

注意：

  1. ANSI SQL规范首选INNER JOIN语法。此外，尽管使用WHERE子句定义联结的确比较简单，但是使用明确的联结语法能够确保不会忘记联结条件，有时候这样做也能影响性能。

此外，联表查询中如果出现多个表之间的联结，表名写起来比较麻烦，因此可以使用表别名，下面的SQL和上面的SQL是等价的：

SQL语句2：SELECT vend_name, prod_name, prod_price FROM vendors AS v, INNER JOIN products AS p ON v.vend_id = p.vend_id

自联结

SQL语句1：SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = ‘DTNTR’)

SQL语句2：SELECT FROM prodcuts AS p1, prodcuts AS p2 WHERE p1.vend_id = p2.vend_id AND p2.prod_id = ‘DTNTR’

对比上面的两句SQL，其实他们查询的数据都是一样的，只不过SQL1使用了子查询，而SQL2使用了联表查询，但是这个“联表”其实联结的表是自己本身，像这种联结的表都是同一张表的联表查询就叫做自联结。虽然上面的两句SQL可以查到相同的数据，但是在性能上自联结查询实际上是优于子查询的，所以最好可以选择自联结查询。

外部联结

在前面的内部联结中，只是包含了那些在相关表中有关联行的行（即上面的v.vend_id = p.vend_id），而对于外部联结，就会包含了那些在相关表中没有关联行的行），而外部联结就包含了左联结、右联结和完全联结。

简单的例子：假设有一个 Students 表和一个 Lockers 表。在SQL中，在联结中指定的第一个表Students 是左表，而第二个表 Lockers 是右表。每个学生可以被分配到一个储物柜，因此在Students 表中有一个 LockerNumber 列。一个储物柜中可能有多个学生，但是，可能会有一些没有储物柜的新生和一些没有分配学生的储物柜。比如，假设有100个学生，其中70个有储物柜。总共有50个储物柜，其中40个至少有1名学生，10个储物柜没有学生。

  • 内部联结相当于”向我展示所有带储物柜的学生”，没有储物柜的学生或没有学生的储物柜都不见了，返回70行数据
  • 左联结是“给我看所有的学生，不管是否有储物柜”，有储物柜的学学生就会展示储物柜，没有储物柜的学生就会展示NULL，返回100行数据
  • 右联结是“显示所有储物柜，不管它们有没有学生”，有学生的储物柜就显示学生，没有学生的就显示NULL，返回80行(40个储物柜中70个学生的列表，加上10个没有学生的储物柜)
  • 完全外部联结将是愚蠢的，可能不会有太多的用处。类似于”向我展示所有学生和所有储物柜，并将它们匹配到可以找到的地方”，返回110行（所有100名学生(包括那些没有储物柜的学生)+10个没有学生的储物柜）

image.png

参考文章： https://www.codenong.com/448023/

image.png

内联结

image.png
SQL语句：select * from a_table AS a inner join b_table AS b on a.a_id = b.b_id
image.png

左联结

image.png
SQL语句：select * from a_table AS a left outer join b_table AS b on a.a_id = b.b_id
image.png

右联结

image.png
SQL语句：select * from a_table AS a right outer join b_table AS b on a.a_id = b.b_id
image.png

完全联结

SQL语句：select from a_table AS a left join b_table AS b on a.a_id = b.b_id UNION select from a_table AS a right join b_table AS b on a.a_id = b.b_id
image.png

参考文章： https://www.cnblogs.com/zeroingToOne/p/9575203.html

组合查询

SQL语句：SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price<=5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002)
image.png
可以看到，组合查询使用UNION关键字来关联两条或以上的SELECT语句，上述的SQL语句其实是执行了两条SELECT语句，然后使用UNION将结果集取并集。即查询了价格小于等于5的所有物品的一个列表，而且还想包括供应商1001和1002生产的所有物品。上面的完全外部联结其实也是一种组合查询。下面是 UNION 的使用规则：

  • UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔
  • UNION中的每个查询必须包含相同的列、表达式或聚集函数，但是各个列不需要以相同的次序列出
  • 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型

    注意：

    1. UNION会默认取消重复的行，如果不想取消重复的行，则需要使用UNION ALL
    2. 使用WHERE子句完全可以达到和UNION一样的效果，但是当逻辑关系很复杂的时候，UNION的优势就会提现出来

如果要对组合查询进行排序，在用UNION组合查询时，只能使用一条ORDER BY子句，它必须出现在最后一条SELECT语句之后，如：
SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price<=5 UNION SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id