子查询

子查询，就是在一个查询中嵌套了其他若干查询，即在一个 SELECT 查询语句的 FROm 或 WHERE 字句中包含另一个 SELECT 查询语句，在这种嵌套的查询语句中，外层的 SELECT 查询语句称为主查询，WHERE 或 FROm 中的查询语句称为子查询，也叫嵌套查询。通过子查询可以实现多表查询，子查询经常出现在 WHERE 或 FROm 字句中。

WHERE 子句中的子查询：该位置处的子查询一般返回单行单列，多行单列，单行多列数据。就是返回能够作为 WHERE 子句查询条件的值。 FROm 子句中的子查询：该位置处的子查询一般返回多行多列数据，相当于是返回一张临时表，符合 FROm 子句后面是表的规则，就是通过这种方式来实现多表查询的。

子查询本质上是嵌套进其他 SELECT,UpDATE,INSERT,DELETE 语句的一个被限制的 SELECT 语句。在子查询中，只有 SELECT, FROm, WHERE, GROUp BY, HAVING 等子句可以使用。

子查询本质上是嵌套进其他 SELECT, UpDATE, INSERT, DELETE 语句的一个被限制的 SELECT 语句,在子查询中，只有下面几个子句可以使用：

SELECT 子句(必须)
FROm 子句(必选)
WHERE 子句(可选)
GROUp BY(可选)
HAVING(可选)

子查询也可以嵌套在其他子查询中，子查询也叫内部查询(Inner query)或者内部选择(Inner Select),而包含子查询的查询语句也叫做外部查询(Outter)或者外部选择(Outer Select)。

子查询作为数据源使用

当子查询在外部查询的 FROm 子句之后使用时,子查询被当作一个数据源使用,即使这时子查询只返回一个单一值(Scalar)或是一列值(Column)，在这里依然可以看作一个特殊的数据源,即一个二维数据表(Table).作为数据源使用的子查询很像一个 View(视图),只是这个子查询只是临时存在，并不包含在数据库中。比如：

SELECT
	p.product_id,
	p.name,
	p.product_number,
	m.name AS product_model_name
FROm
	production.product AS p
INNER JOIN
      (SELECT name, product_model_id FROm production.product_model) AS m
ON p.product_model_id = m.product_model_id

上述子查询语句将 product_model 表中的子集 m,作为数据源(表)和 product 表进行内连接。结果如下:

作为数据源使用也是子查询最简单的应用。当然，当子查询作为数据源使用时，也分为相关子查询和无关子查询。

当子查询在外部查询的 FROm 子句之后使用时,子查询被当作一个数据源使用,即使这时子查询只返回一个单一值(Scalar)或是一列值(Column)，在这里依然可以看作一个特殊的数据源,即一个二维数据表(Table)。作为数据源使用的子查询很像一个视图(View),只是这个子查询只是临时存在，并不包含在数据库中。

子查询作为查询条件使用

作为选择条件的子查询也是子查询相对最复杂的应用。作为选择条件的子查询是那些只返回一列(Column)的子查询，如果作为选择条件使用，即使只返回单个值，也可以看作是只有一行的一列。比如: 在 adventure_works 中，我想取得总共请病假天数大于 68 小时的员工:

SELECT [first_name]
      ,[middle_name]
      ,[last_name]
  FROm [adventure_works].[person].[contact]
  WHERE contact_id IN
  (SELECT employee_id
  FROm [adventure_works].[human_resources].[employee]
  WHERE sick_leave_hours>68)

结果如下：

上面的查询中，在 IN 关键字后面的子查询返回一列值作为外部查询的选择条件使用.同样的，与 IN 关键字的逻辑取反的 NOT IN 关键字，这里就不再阐述了但是要强调的是，不要用 IN 和 NOT IN 关键字，这会引起很多潜在的问题，这篇文章对这个问题有着很好的阐述:。这篇文章的观点是永远不要再用 IN 和 NOT IN 关键字，我的观点是存在即合理，我认为只有在 IN 里面是固定值的时候才可以用 IN 和 NOT IN，比如:

SELECT [first_name]
      ,[middle_name]
      ,[last_name]
  FROm [adventure_works].[person].[contact]
  WHERE contact_id  IN (25,33)

只有在上面这种情况下，使用 IN 和 NOT IN 关键字才是安全的，其他情况下，最好使用 EXISTS,NOT EXISTS,JOIN 关键字来进行替代. 除了 IN 之外，用于选择条件的关键字还有 ANY 和 ALL,这两个关键字和其字面意思一样. 和"<",">",”=“连接使用，比如上面用 IN 的那个子查询：我想取得总共请病假天数大于 68 小时的员工。用 ANY 关键字进行等效的查询为:

SELECT [first_name]
      ,[middle_name]
      ,[last_name]
  FROm [adventure_works].[person].[contact]
  WHERE contact_id =ANY

  (SELECT employee_id
  FROm [adventure_works].[human_resources].[employee]
  WHERE sick_leave_hours>68)

作为选择条件的子查询也是子查询相对最复杂的应用。作为选择条件的子查询是那些只返回一列(Column)的子查询，如果作为选择条件使用，即使只返回单个值，也可以看作是只有一行的一列。譬如我们需要查询价格高于某个指定产品的所有其余产品信息：

SELECT
	*
FROm
	product
WHERE
	price > (
		SELECT
			price
		FROm
			product
		WHERE
			name = "产品一"
	)

子查询作为计算列使用

当子查询作为计算列使用时，只返回单个值(Scalar)。用在 SELECT 语句之后，作为计算列使用。同样分为相关子查询和无关子查询。相关子查询的例子比如：我想取得每件产品的名称和总共的销量：

SELECT [Name],
      (SELECT COUNT(*) FROm adventure_works.Sales.SalesOrderDetail S
      WHERE S.product_id=p.product_id) AS SalesAmount
FROm [adventure_works].[production].[product] p

当子查询作为计算列使用时，只返回单个值(Scalar)，其用在 SELECT 语句之后，作为计算列使用，同样分为相关子查询和无关子查询。

--- 查询每个类别中价格大于某个值的产品数目
SELECT
	p1.category,
	(
		SELECT
			count(*)
		FROm
			product p2
		WHERE
			p2.category = p1.category
		AND p2.price > 30
	) AS 'Expensive'
FROm
	product p1
GROUp BY
	p1.category;

--- 自连接查询不同等级的数目
SELECT a.distributor_id,
      (SELECT COUNT(*) FROm my_table WHERE level='personal' and distributor_id = a.distributor_id) as personal_count,
      (SELECT COUNT(*) FROm my_table WHERE level='exec' and distributor_id = a.distributor_id) as exec_count,
      (SELECT COUNT(*) FROm my_table WHERE distributor_id = a.distributor_id) as total_count
FROm my_table a ;

最近更新于 0001-01-01