The most common use of a subquery is in the form:子查询最常见的用法是:
non_subquery_operand comparison_operator (subquery)
Where comparison_operator
is one of these operators: 其中comparison_operator是以下操作符之一:
= > < >= <= <> != <=>
For example:
... WHERE 'a' = (SELECT column1 FROM t1)
MySQL also permits this construct: MySQL也允许这种构造:
non_subquery_operand LIKE (subquery)
At one time the only legal place for a subquery was on the right side of a comparison, and you might still find some old DBMSs that insist on this.
曾经,子查询的唯一合法位置是在比较的右侧,您可能仍然会发现一些旧的dbms坚持这样做。
Here is an example of a common-form subquery comparison that you cannot do with a join. It finds all the rows in table t1
for which the column1
value is equal to a maximum value in table t2
:
下面是一个使用连接无法实现的公共形式子查询比较的示例。它查找表t1中列n1值等于表t2中最大值的所有行:
SELECT * FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);
Here is another example, which again is impossible with a join because it involves aggregating for one of the tables. It finds all rows in table t1
containing a value that occurs twice in a given column:
下面是另一个例子,使用连接也是不可能的,因为它涉及到一个表的聚合。它查找表t1中包含在给定列中出现两次的值的所有行:
SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);
For a comparison of the subquery to a scalar, the subquery must return a scalar. For a comparison of the subquery to a row constructor, the subquery must be a row subquery that returns a row with the same number of values as the row constructor. See Section 13.2.11.5, “Row Subqueries”.
对于子查询与标量的比较,子查询必须返回标量。要将子查询与行构造函数进行比较,子查询必须是返回与行构造函数具有相同数量值的行子查询。