A Subquery como um Operando Escalar

Na sua forma mais simples (a subquery scalar é o oposto das subqueries de row ou table que será discutido posteriormente), uma subqquery é um opernado simples. Assim você pode usá-la se um valor de uma coluna ou literal é permitido, e você pode esperar que eles tenham certas características que todos os operandos possuem: um tipo de dados, um tamanho, um indicador para informar se ele pode ser NULL, etc. Por exemplo:

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
SELECT (SELECT s2 FROM t1);

The subquery in the above SELECT has a data type of CHAR, a length of 5, a character set and collation equal to the defaults in effect at CREATE TABLE time, and an indication that the value in the column can be NULL. In fact almost all subqueries can be NULL, because if the table is empty -- as in the example -- then the value of the subquery will be NULL. There are few restrictions.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.
A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT, joins, hints, UNIONs, comments, functions, and so on.

So, when you see examples in the following sections that contain the rather Spartan construct (SELECT column1 FROM t1), imagine that your own code will contain much more diverse and complex constructions.

For example, suppose we make two tables:

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

Then perform a SELECT:

SELECT (SELECT s1 FROM t2) FROM t1;

The result will be 2 because there is a row in t2, with a column s1, with a value of 2.

The subquery may be part of an expression. If it is an operand for a function, don't forget the parentheses. For example:

A Subquery como um Operando Escalar

Retornar