Ms-sql expression of a subquery

1.EXISTS EXISTS (subquery)
The EXISTS argument is an arbitrary SELECT statement, or subquery. The system operates a subquery to determine whether it returns rows. If it returns at least one row, the result of the EXISTS is true, and if the subquery does not return rows, then the EXISTS result is "false."
Subqueries can refer to variables from the surrounding query, which play a constant role in any calculation of the subquery.
This subquery is usually only run to determine whether it can generate at least one row, rather than wait until all ends. It is unwise to write any side-effects subqueries here, such as calling a sequence function, and whether these side effects occur is difficult to determine.
Because the result depends only on whether rows are returned, not on the contents of those rows, the output list of this subquery is usually irrelevant. A common coding habit is to write all EXISTS test EXISTS (SELECT 1 WHERE ...) in the following form. However, there are exceptions to this rule, such as those that use INTERSECT.
The following simple example resembles an inline join on a col2, but it generates up to one output for each tab1 row, even if there are multiple matching tab2 rows:
SELECT col1 from Tab1
where EXISTS (SELECT 1 from tab2 WHERE col2 = tab1.col2);

2.in          expression in (subquery)
     The right-hand side of this form of in is a parenthesized subquery that must return only one field. The left-hand-side expression evaluates and compares each row of the result of the subquery. If any case is found that is equal to the subquery row, then the result in is "true". If no equality row is found, the result is "false" (including a special example of a subquery that does not return any rows).
    Note If the left-hand-side expression generates NULL, or if there is no equal right-hand value, and at least one right-hand line generates NULL, the result of the in construct is null, not false. This behavior is determined in accordance with the rules of SQL handling Boolean and NULL values.
    As with EXISTS, it is not advisable to assume that subqueries will be run completely. Row_constructor in (subquery)    the left-hand side of this form in is a row constructor, and the right-hand side is a round-bracket subquery that must return exactly the same number of fields as the expression in the row returned by the left-hand side. The left-hand-side expression is compared to each row of the subquery's results. If any of the equal subquery rows are found, the result in is true. If no equality row is found, the result is "false" (including a special example of a subquery that does not return rows). Typically, NULL in an expression or subquery row is grouped by the general rules of the SQL Boolean expression. If two rows correspond to members that are non-null and equal, the two rows are considered equal, and if any corresponding member is non-null and unequal, the rows are unequal; otherwise, the result of such a row comparison is unknown (NULL). If the result of all rows is either unequal or null, and at least one null, then the result in is null.

3.NOT in             expression not in ( Subquery
    The right-hand side is a subquery surrounded by parentheses that must return a field. The left-hand-side expression calculates and compares each row of the subquery's results. If only the unequal subquery rows are found, including the special case where the subquery does not return rows, then the result of not in is true. If any equality rows are found, the result is "false."      Note that if the left-hand expression returns empty, or if there is no value equal to the right-hand side, and at least one right-hand line generates empty, the result of the not in construct will be null, not true. This is based on SQL's general rules for combining Boolean and null values.
    As with EXISTS, it would be unwise to assume that subqueries will end entirely. This form of Row_constructor not in (subquery) is a row constructor whose right-hand side is a round-bracket subquery that must return as many fields as rows returned by the left-hand-side expression. The left-hand-side expression evaluates and compares each row of the result of the subquery. If there is only one case where the subquery row is not equal, then the result of not in is true. (including a special example of a subquery that does not return any rows.) If an equal row is found, the result is "false."
    as usual, NULL in an expression or subquery row is grouped according to the general rules of the SQL Boolean expression. If two rows correspond to members that are non-null and equal, the two rows are considered equal, and if any corresponding member is non-null and unequal, the rows are unequal; otherwise, the result of such a row comparison is unknown (NULL). If the result of all rows is either unequal or null, and at least one null, then the result of not in is null.

4.any/someexpression operator ANY (subquery)
expression operator SOME (subquery)
This form of any right-hand side is a rounded parenthesis-enclosed subquery that must return a field. The left-hand-side expression computes and compares each row of the result of the operator subquery with the given. At present, if you get any truth results, then the result of any is "true".     If the truth result is not found, the result is "false" (including a special example of a subquery that does not return any rows). SOME is the consent word of any. In is equivalent to = any.
Note that if there is no success and at least one right-hand side behavior the result of the operator generates NULL, then the result of any construct will be null instead of FALSE. This behavior is determined in accordance with the rules of SQL handling Boolean and NULL values.
As with EXISTS, it is unwise to assume that subqueries will be completed. Row_constructor operator ANY (subquery)

Row_constructor operator SOME (subquery)

This form of the left-hand side is a row builder, and the right-hand side is a round-bracket subquery that must return as many columns as the expression given in the left-hand list. The left-hand-side expression computes and compares each row of the result of the operator subquery with the given. Currently, the system only allows the use of the = or <> operator to process a row-by-line any construct. If you find rows that are equal or not equal, then the result of any is true. If no such row is found, the result is "false" (including a special example of a subquery that does not return any rows).
Typically, NULL in an expression or subquery row is grouped by the general rules of the SQL Boolean expression. If two rows correspond to members that are non-null and equal, the two rows are considered equal, and if any corresponding member is non-null and unequal, the rows are unequal; otherwise, the result of such a row comparison is unknown (NULL). If there is at least one null row result, then the result of any will be true or null.

5.ALL expression operator All (subquery)
all of this form of the right-hand side is a rounded parenthesis-enclosed subquery that must return only one column. The left-hand-side expression computes and compares each row of the result of the operator subquery with the given. The operator must generate a Boolean result. If all rows have the result "true" (including a special example of a subquery that does not return any rows). So the result of all is "true". If no false value is present, the result is "false".

Not in is equivalent to <> all.

Note that if there is no operation failure but at least one right-hand side behavior the result of the operator generates a null value, then the result of the all construct will be null, not TRUE. This behavior is governed by the general rules of SQL Processing Boolean and NULL values.
As with EXISTS, it is unwise to assume that subqueries will be completed.

Row_constructor operator All (subquery)

All of this form of the left-hand side is a row builder, and the right-hand side is a round-bracket subquery that must return as many fields as the expression given by the left-hand side. The left-hand-side expression computes and compares each row of the result of the operator subquery with the given. Currently the system only allows the use of = and <> operators to process line-by-row all queries. If all subqueries are equal or unequal rows, the result of all is true. (including a special example of a subquery that does not return any rows.) If there are any rows that are unequal or equal, the result is "false".

Typically, NULL in an expression or subquery row is grouped by the general rules of the SQL Boolean expression. If two rows correspond to members that are non-null and equal, the two rows are considered equal, and if any corresponding member is non-null and unequal, the rows are unequal; otherwise, the result of such a row comparison is unknown (NULL). If there is at least one NULL row result, then the result of all cannot be true; it will be false or null.