MySQL Sub-query

1 single value (Scalar operand)

The corresponding value is returned only if the outer layer (Parent) is not empty, otherwise null is returned.

Note:for the subquery just shown, if t1 were empty, the result would be NULL even though s2 is NOT NULL .

2 for operator comparisons

[14.2.10.2 comparisons Using subqueries]

Non_subquery_operand Comparision_operator (subquery)

The Comparision_operator is: =,>,>=,<,<=,!=,like, <=>[and = functionally similar, can also be used for null value comparisons]

3 Using In,any,some

Operand comparison_operator any (subquery)

operand in (subquery)

Operand comoparison_operator SOME (subquery)

Where Comparison_operator is one of these operators:=,>, >=,<,<=,<>,!=

Any, stating any one. True if any one of the right-hand operands satisfies the operand condition. [Any (cannot be a constant value)]

In, contains [can have constant value, multiple with, ' delimited].

SOME and any are functionally equivalent, but differ in their daily syntax.

4, use all

Operand comparison_operator All (subquery)

Satisfies all, only returns TRUE.

5, row sub-query [row subqueries]

A value or a column subquery (as shown above) returns a value or a column of values that meet the criteria; The row subquery returns one row of data, possibly multiple columns; The legal operators for row subqueries are:

=,>,<,>=,<=,<>,!=,<=>

Only multiple columns satisfy the operator to return true, otherwise false or NULL.

6, using EXISTS OR not EXISTS

When the subquery returns data, EXISTS subquery is True,and not EXISTS subquery is false.

7, associating subqueries

is a query that contains a reference to the external layer query.

8 for the FROM clause

9. Sub-query Error

10, Optimizing subqueries

"Development is ONGOING,SO no optimization tip was reliable for the long term." There is no reliable optimization technique in the long run when development is underway.

MySQL Sub-query