mysql 子查询与连接表 mysql 子查询与连接表详情

1、什么是子查询？

列出订购物品TNT2的所有客户：

select cust_id
from orders
where order_num IN (SELECT order_num

from orderitems
where prod_id = 'TNT2'
)

格式化SQL 包含子查询的SELECT语句难以阅读和调试，特别是它们较为复杂时更是如此。如上所示把子查询分解为多行并且适当地进行缩进，能极大地简化子查询的使用。

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

注:

列必须匹配 在WHERE子句中使用子查询（如这里所示），应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，
子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列。

除了子查询可以放在where 中，还可以放到select中去。

假如需要显示customers表中每个客户的订单总数。

select cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) as orders
from customers
ORDER BY cust_name

mysql 的运行过程是先执行了customers 中查出来了cust_name，cust_state，cust_id，然后执行5次子查询，查出来了结果。

逐渐增加子查询来建立查询 用子查询测试和调试查询很有技巧性，特别是在这些语句的复杂性不断增加的情况下更是如此。用子查询建立（和测试）查询的最可靠的方法是逐渐进行，这与MySQL处理它们的方法非常相同。首先，建立和测试最内层的查询。然后，用硬编码数据建立和测试外层查询，并且仅在确认它正常后才嵌入子查询。这时，再次测试它。对于要增加的每个查询，重复这些步骤。这样做仅给构造查询增加了一点点时间，但节省了以后（找出查询为什么不正常）的大量时间，并且极大地提高了查询一开始就正常工作的可能性

下面介绍一下联结:

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE  vendors.vend_id=products.vend_id
ORDER BY vend_name, prod_name

注:

完全限定列名 在引用的列可能出现二义性时，必须使用完全限定列名（用一个点分隔的表名和列名）。如果引用一个没有用表名限制的具有二义性的列名，MySQL将返回错误。

这里使用where 语句进行联接的作用:

利用WHERE子句建立联结关系似乎有点奇怪，但实际上，有一个很充分的理由。请记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中不存在能指示MySQL如何对表进行联结的东西。你必须自己做这件事情。在联结两个表时，你实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每个行将与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

注:

笛卡儿积（cartesian product） 由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。目前为止所用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内部联结。其实，对于这种联结可以使用稍微不同的语法来明确指定联结的类型。

下面的SELECT语句返回与前面例子完全相同的数据：

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

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

性能考虑 MySQL在运行时关联指定的每个表以处理联结。这种处理可能是非常耗费资源的，因此应该仔细，不要联结
不必要的表。联结的表越多，性能下降越厉害。

多做实验 正如所见，为执行任一给定的SQL操作，一般存在不止一种方法。很少有绝对正确或绝对错误的方法。性能可能
会受操作类型、表中数据量、是否存在索引或键以及其他一些条件的影响。因此，有必要对不同的选择机制进行实验，以找
出最适合具体情况的方法。我们同样可以使用多张表的联接，但是有一个问题，因为表名多个地方使用，故而表名很长，那么可以使用表的别名。

如：

下面介绍一下几种特殊的连接。

2、自联接

假如你发现某物品（其ID为DTNTR）存在问题，因此想知道生产该物品的供应商生产的其他物品是否也存在这些问题。此查询要求首先找到生产ID为DTNTR的物品的供应商，然后找出这个供应商生产的其他物品。

下面是解决此问题的一种方法：

你可能使用子查询，这样做:

select prod_id,prod_name
from products
where vend_id = (SELECT vend_id from products WHERE prod_id ='DTNTR')

 同样可以使用自联接。

select t1.prod_id,t2.prod_name
from products t1, products t2
where t1.vend_id = t2.vend_id and t1.prod_id='DTNTR'

用自联结而不用子查询 自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。虽然最终的结果是
相同的，但有时候处理联结远比处理子查询快得多。应该试一下两种方法，以确定哪一种的性能更好。

3、自然联接

无论何时对表进行联结，应该至少有一个列出现在不止一个表中（被联结的列）。标准的联结（前一章中介绍的内部联结）返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然联结是这样一种联结，其中你只能选择那些唯一的列。这一般是通过对表使用通配符（SELECT * ）对所有其他表的列使用明确的子集来完成的。

4、外部联结

许多联结将一个表中的行与另一个表中的行相关联。但有时候会需要包含没有关联行的那些行。例如，可能需要使用联结来完成以下工作：

比如：对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；

SELECT customers.cust_id,order_num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

这条SELECT语句使用了关键字OUTER JOIN来指定联结的类型（而不是在WHERE子句中指定）。但是，与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字
指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。

使用带聚集函数的联结:

要检索所有客户及每个客户所下的订单数:

SELECT customers.cust_id, COUNT(order_num) as num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id
GROUP BY cust_id

注意点:
1.注意所使用的联结类型。一般我们使用内部联结，但使用外部联结也是有效的。
2.保证使用正确的联结条件，否则将返回不正确的数据。
3.应该总是提供联结条件，否则会得出笛卡儿积。
4.在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。