Sintaxis de subconsultas

Una subconsulta es un comando SELECT dentro de otro comando.
MySQL 5.0 soporta todas las formas de subconsultas y operaciones que requiere el estándar SQL, así como algunas características
específicas de MySQL.
Aquí hay un ejemplo de subconsulta:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
En este ejemplo, SELECT * FROM t1 ... es la consulta externa (o comando externo), y (SELECT column1 FROM t2)
es la subconsulta. Decimos que la subconsulta está anidada dentro de la consulta exterior, y de hecho, es posible anidar subconsultas
dentro de otras subconsultas hasta una profundidad considerable. Una subconsulta debe siempre aparecer entre paréntesis.
Las principales ventajas de subconsultas son:
• Permiten consultas estructuradas de forma que es posible aislar cada parte de un comando.
• Proporcionan un modo alternativo de realizar operaciones que de otro modo necesitarían joins y uniones complejos.
• Son, en la opinión de mucha gente, leíbles. De hecho, fue la innovación de las subconsultas lo que dio a la gente la idea original
de llamar a SQL “Structured Query Language.”
Aquí hay un comando de ejemplo que muestra los puntos principales de la sintaxis de subconsultas como especifica el estándar
SQL y soporta MySQL:
DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));
Una subconsulta puede retornar un escalar (un valor único), un registro, una columna o una tabla (uno o más registros de una o más
columnas). Éstas se llaman consultas de escalar, columna, registro y tabla. Las subconsultas que retornan una clase particular de resultado
a menudo pueden usarse sólo en ciertos contextos, como se describe en las siguientes secciones.
Hay pocas restricciones sobre los tipos de comandos en que pueden usarse las subconsultas. Una subconsulta puede contener cualquiera
de las palabras claves o cláusulas que puede contener un SELECT ordinario: DISTINCT, GROUP BY, ORDER BY, LIMIT,
joins, trucos de índices, constructores UNION , comentarios, funciones, y así.
Una restricción es que el comando exterior de una subconsulta debe ser: SELECT, INSERT, UPDATE, DELETE, SET, o DO. Otra
restricción es que actualmente no puede modificar una tabla y seleccionar de la misma tabla en la subconsulta.

La subconsulta, como un operador sobre valores escalares


En su forma más sencilla, una subconsulta es una subconsulta escalar que retorna un único valor. Una subconsulta escalar es un
operando simple, y puede usarlo prácticamente en cualquier sitio en que un valor de columna o literal sea legal, y puede esperar
que tenga las características que tienen todos los operandos: un tipo de datos, una longitud, una indicación de si puede ser NULL,
etcétera. Por ejemplo:
CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);
La subconsulta en este SELECT retorna un valor único ('abcde') que tiene un tipo de datos CHAR, una longitud de 5, un conjunto
de caracteres y una colación iguales a la que había por defecto cuando se realizó el CREATE TABLE , y una indicación que el
valor en la columna puede ser NULL. De hecho, casi todas las consultas pueden ser NULL. Si la tabla usada en este ejemplo estuviese
vacía, la tabla de la subconsulta sería NULL.
Hay algunos contextos en que una subconsulta escalar no se puede usar. Si un comando permite sólo un valor literal, no puede usar
una subconsulta. Por ejemplo, LIMIT necesita argumentos enteros, y LOAD DATA necesita una cadena con un nombre de fichero.
No puede usar subconsultas para proporcionar estos valores.
Cuando vea los ejemplos en las siguientes secciones que contengan el constructor (SELECT column1 FROM t1), imagine que
su própio código contiene construcciones mucho más diversas y complejas.
Por ejemplo, suponga que hacemos dos tablas:
CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);
Luego realice SELECT:
SELECT (SELECT s1 FROM t2) FROM t1;
El resultado es 2 ya que hay un registro en t2 que contiene una columna s1 con un valor de 2.
Una subconsulta escalar puede ser parte de una expresión. No olvide los paréntesis, incluso si la subconsulta es un operando que
proporciona un argumento para una función. Por ejemplo:
SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

Uso de subconsultas en subconsultas


El uso más común de una subconsulta es de la forma:
non_subquery_operand comparison_operator (subquery)
Donde comparison_operator es uno de estos operadores:
= > < >= <= <>
Por ejemplo:
... 'a' = (SELECT column1 FROM t1)
Tiempo atrás el único sitio legal para una subconsulta era la parte derecha de la comparación, y puede encontrar algunos SGBDs
que insistan en ello.
He aquí un ejemplo de una comparación común de subconsultas que no puede hacerse mediante un join. Encuentra todos los valores
en la tabla t1 que son iguales a un valor máximo en la tabla t2:
SELECT column1 FROM t1
WHERE column1 = (SELECT MAX(column2) FROM t2);
Aquí hay otro ejemplo, que de nuevo es imposible de hacer con un join ya que involucra agregación para una de las tablas. Encuentra
todos los registros en la tabla t1 que contengan un valor que ocurre dos veces en una columna dada:
SELECT * FROM t1 AS t
WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

Subconsultas con ANY, IN y SOME


Sintaxis:
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
La palabra clave ANY , que debe seguir a un operador de comparación, significa “return TRUE si la comparación es TRUE para ANY
(cualquiera) de los valores en la columna que retorna la subconsulta.” Por ejemplo:
SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);
Suponga que hay un registro en una tabla t1 que contiene (10). La expresión es TRUE si la tabla t2 contiene (21,14,7) ya
que hay un valor 7 en t2 que es menor que 10. La expresión es FALSE si la tabla t2 contiene (20,10), o si la tabla t2 está vacía.
La expresión es UNKNOWN si la tabla t2 contiene (NULL,NULL,NULL).
La palabra IN es un alias para = ANY. Por lo tanto, estos dos comandos son lo mismo:
SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);
Sin embargo, NOT IN no es un alias para <> ANY, sino para <> ALL. Consulte Sección 13.2.8.4, “Subconsultas con ALL”.
La palabra SOME es un alias para ANY. Por lo tanto, estos dos comandos son el mismo:
SELECT s1 FROM t1 WHERE s1 <> ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);
El uso de la palabra SOME es raro, pero este ejemplo muestra cómo puede ser útil. Para la mayoría de gente, la frase en inglés “a is
not equal to any b” significa “there is no b which is equal to a,” pero eso no es lo que quiere decir la sintaxis SQL. La sintaxis significa
“there is some b to which a is not equal.” Usando <> SOME en su lugar ayuda a asegurar que todo el mundo entiende el significado
de la consulta.

Subconsultas con ALL


Sintaxis:
operand comparison_operator ALL (subquery)
La palabra ALL, que debe seguir a un operador de comparación, significa “return TRUE si la comparación es TRUE para ALL todos
los valores en la columna que retorna la subconsulta.” Por ejemplo:
SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);
Suponga que hay un registro en la tabla t1 que contiene (10). La expresión es TRUE si la tabla t2 contiene (-5,0,+5) ya que
10 es mayor que los otros tres valores en t2. La expresión es FALSE si la tabla t2 contiene (12,6,NULL,-100) ya que hay un
único valor 12 en la tabla t2 mayor que 10. La expresión es UNKNOWN si la tabla t2 contiene (0,NULL,1).
Finalmente, si la tabla t2 está vacía, el resultado es TRUE. Puede pensar que el resultado debería ser UNKNOWN, pero lo sentimos,
es TRUE. Así, aunque extraño, el siguiente comando es TRUE cuando la tabla t2 está vacía:
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
Pero este comando es UNKNOWN cuando la tabla t2 está vacía:
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
Además, el siguiente comando es UNKNOWN cuando la tabla t2 está vacía:
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
En general, las tablas con valores NULL y las tablas vacías son casos extremos. Al escribir código para subconsultas, siempre con-
sidere si ha tenido en cuenta estas dos posibilidades.
NOT IN es un alias para <> ALL. Por lo tanto, estos dos comandos son equivalentes:
SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

Subconsultas de registro


La discusión en este punto ha sido entre subconsultas escalares o de columnas, esto es, subcolumnas que retornan un único valor o
una columna de valores. Una subconsulta de registro es una variante de subconsulta que retorna un único registro y por lo tanto retorna
más de un valor de columna. Aquí hay dos ejemplos:
SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);
Las consultas aquí son ambas TRUE si la tabla t2 tiene un registro en que column1 = 1 y column2 = 2.
Las expresiones (1,2) y ROW(1,2) a veces se llaman constructores de registros. Ambos son equivalentes. También son legales
en otros contextos. Por ejemplo, los siguientes dos comandos son semánticamente equivalentes (aunque actualmente sólo puede optimizarse
el segundo):
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
El uso normal de constructores de registros, sin embargo, es para comparaciones con subconsultas que retornan dos o más columnas.
Por ejemplo, la siguiente consulta responde a la petición, “encuentra todos los registros en la tabla t1 que también existen en
la tabla t2”:
SELECT column1,column2,column3
FROM t1
WHERE (column1,column2,column3) IN
(SELECT column1,column2,column3 FROM t2);

EXISTS y NOT EXISTS


Si una subconsulta retorna algún registro, entonces EXISTS subquery es TRUE, y NOT EXISTS subquery es FALSE. Por
ejemplo:
SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);
Tradicionalmente, una subconsulta EXISTS comienza con SELECT *, pero puede comenzar con SELECT 5 o SELECT col1 o
nada. MySQL ignora la lista SELECT en tales subconsultas, así que no hace distinción.
Para el ejemplo precedente, si t2 contiene algún registro, incluso registros sólo con valores NULL entonces la condición EXISTS
es TRUE. Este es un ejemplo poco probable, ya que prácticamente siempre una subconsulta [NOT] EXISTS contiene correlaciones.
Aquí hay algunos ejemplos más realistas:
• ¿Qué clase de tienda hay en una o más ciudades?
SELECT DISTINCT store_type FROM Stores
WHERE EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
• ¿Qué clase de tienda no hay en ninguna ciudad?
SELECT DISTINCT store_type FROM Stores
WHERE NOT EXISTS (SELECT * FROM Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
• ¿Qué clase de tienda hay en todas las ciudades?
SELECT DISTINCT store_type FROM Stores S1
WHERE NOT EXISTS (
SELECT * FROM Cities WHERE NOT EXISTS (
SELECT * FROM Cities_Stores
WHERE Cities_Stores.city = Cities.city
AND Cities_Stores.store_type = Stores.store_type));
El último ejemplo es un doblemente anidado NOT EXISTS . Esto es, tiene una cláusula NOT EXISTS dentro de otra NOT
EXISTS. Formalmente, responde a la pregunta “¿existe una ciudad con una tienda que no esté en Stores?” Sin embargo, es más
fácil decir que un NOT EXISTS responde a la pregunta “¿es x TRUE para todo y?”

Subconsultas correlacionadas


Una subconsulta correlacionada es una subconsulta que contiene una referencia a una tabla que también aparece en la consulta exterior.
Por ejemplo:
SELECT * FROM t1 WHERE column1 = ANY
(SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);
Tenga en cuenta que la subconsulta contiene una referencia a una columna de t1, incluso aunque la cláusula FROM de la subconsulta
no menciona una tabla t1. Por lo tanto, MySQL busca fuera de la subconsulta y encuentra t1 en la consulta externa.
Suponga que la tabla t1 contiene un registro en que column1 = 5 y column2 = 6; mientras, la tabla t2 contiene un registro
en que column1 = 5 y column2 = 7. La expresión ... WHERE column1 = ANY (SELECT column1 FROM t2)
sería TRUE, pero en este ejemplo, la cláusula WHERE dentro de la subconsulta es FALSE (ya que (5,6) no es igual a (5,7)), así
que la subconsulta como un todo es FALSE.
Regla de visibilidad: MySQL evalúa desde dentro hacia fuera. Por ejemplo:
SELECT column1 FROM t1 AS x
WHERE x.column1 = (SELECT column1 FROM t2 AS x
WHERE x.column1 = (SELECT column1 FROM t3
WHERE x.column2 = t3.column1));
En este comando, x.column2 debe ser una columna en la tabla t2 ya que SELECT column1 FROM t2 AS x ... renombra
t2. No hay una columna en la tabla t1 porque SELECT column1 FROM t1 ... es una consulta externa que está demasiado
afuera.
Para subconsultas en cláusulas HAVING u ORDER BY , MySQL busca nombres de columna en la lista de selección exterior.
Para ciertos casos, una subconsulta correlacionada es óptima. Por ejemplo:
val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)
De otro modo, son ineficientes y lentas. Reescribir la consulta como un join puede mejorar el rendimiento.
Las subconsultas correlacionadas no pueden referirse a los resultados de funciones agregadas de la consulta exterior.

Subconsultas en la cláusula FROM


Las subconsultas son legales en la cláusula FROM de un comando SELECT. La sintaxis que vería es:
SELECT ... FROM (subquery) [AS] name ...
La cláusula [AS] name es obligatoria, ya que cada tabla en la cláusula FROM debe tener un nombre. Cualquier columna en la lista
selecta de la subquery debe tener nombre único. Puede encontrar esta sintaxis descrita en este manual, dónde se usa el término
“tablas derivadas.”
Asuma que tiene la tabla:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
Aquí se muestra cómo usar una subconsulta en la cláusula FROM usando la tabla de ejemplo:
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
WHERE sb1 > 1;
Resultado: 2, '2', 4.0.
Aquí hay otro ejemplo: suponga que quiere conocer la media de un conjunto de sumas para una tabla agrupada. Esto no funcionaría:
SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;
Sin embargo, esta consulta proporciona la información deseada:
SELECT AVG(sum_column1)
FROM (SELECT SUM(column1) AS sum_column1
FROM t1 GROUP BY column1) AS t1;
Tenga en cuenta que el nombre de columna usado dentro de la subconsultas (sum_column1) se reconoce en la consulta exterior.
Las subconsultas en la cláusula FROM pueden retornar un escalar, columna, registro o tabla. De momento, las subconsultas en la
cláusula FROM no pueden ser subconsultas correladas.
Las subconsultas en la cláusula FROM se ejecutan incluso para el comando EXPLAIN (esto es, se construyen las tablas temporales
derivadas). Esto ocurre porque las consultas de niveles superiores necesitan información acerca de todas las tablas durante la fase
de optimización.

 
<<Menú Inicio