Subconsulta devuelve una tabla

Y finalmente, la tercera opción es cuando la subconsulta devuelve la tabla completa. Esta es la opción más común.

Muy a menudo hay situaciones en las que queremos modificar un poco una tabla determinada. Y solo entonces unir (usando el operador JOIN ON) la tabla corregida con otra.

Comencemos con el caso más simple, donde unimos dos tablas con un JOIN:

SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id

Y como probablemente recuerde, hay tareas en la tabla de tareas que no están asignadas a nadie: employee_id es NULL .

Generemos una tabla corregida , donde le asignemos todas las tareas pendientes al director (su ID = 4).

Para hacer esto, usamos la función IFNULL() :

SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task 

Y el resultado de esta consulta:

identificación ID de empleado nombre fecha límite
1 1 Arreglar un error en la interfaz 2022-06-01
2 2 Arreglar un error en el backend 2022-06-15
3 5 comprar cafe 2022-07-01
4 5 comprar cafe 2022-08-01
5 5 comprar cafe 2022-09-01
6 4 limpiar la oficina (NULO)
7 4 Disfruta la vida (NULO)
8 6 Disfruta la vida (NULO)

La celda corregida está marcada en rojo.

Ahora sustituyamos nuestra tabla corregida en la consulta:

SELECT * FROM employee e JOIN task t ON e.id = t.emploee_id

En lugar de la tabla de tareas .

Tal solicitud sería algo como esto:

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline
 	FROM task 
) t ON e.id = t.emploee_id

En lugar de la palabra tarea , escribimos paréntesis y colocamos el cuerpo de la solicitud en ellos.

Por cierto, el alias t (alias) para la consulta anidada fue muy útil. Una consulta anidada, a diferencia de una tabla, no tiene nombre propio, por lo que el alias está muy fuera de lugar.

Y aquí está el resultado de tal consulta:

identificación nombre ocupación salario edad Fecha de Ingreso identificación ID de empleado nombre
1 ivanov ivan Programador 100000 25 2012-06-30 1 1 Arreglar un error en la interfaz
2 petrov petr Programador 80000 23 2013-08-12 2 2 Arreglar un error en el backend
4 Rabinovich Moisha Director 200000 35 2015-05-12 6 4 limpiar la oficina
4 Rabinovich Moisha Director 200000 35 2015-05-12 7 4 Disfruta la vida
5 Anastasia Kirienko Gerente de oficina 40000 25 2015-10-10 4 5 comprar cafe
5 Anastasia Kirienko Gerente de oficina 40000 25 2015-10-10 5 5 comprar cafe
5 Anastasia Kirienko Gerente de oficina 40000 25 2015-10-10 3 5 comprar cafe
6 Vaska gato 1000 3 2018-11-11 8 6 Disfruta la vida

Nuestro director tiene la tarea de “limpiar la oficina”, creo que rápidamente encontrará a alguien en quien delegarla :) Usando el operador CON

Por cierto, a partir de la versión 8 de MySQL, ya no tiene que colocar todas sus subconsultas dentro de la consulta final. Se pueden realizar por separado. Para ello, se utiliza la instrucción CON .

Te permite crear una tabla virtual (denominada query) y su apariencia viene dada por una plantilla:

WITH Name AS (request) 

A menudo, su subconsulta tiene columnas sin nombre, como COUNT (*), a las que no ha asignado un nombre único. En este caso, la instrucción WITH tiene la opción de especificar nuevos nombres de columna para la subconsulta.

Su segunda forma viene dada por la plantilla:

WITH Name(column1, column2, …) AS (request) 

Puede utilizar tantas tablas virtuales (consultas con nombre) como desee y consultarlas entre sí. La forma general de su solicitud tendrá algo como esto:

WITH name1 AS (request1),  
       	name2 AS (request2), 
       	name3 AS (request3) 
SELECT * FROM name1 JOIN name2 ON

Ahora tomemos nuestra consulta aterradora:

SELECT * FROM employee e JOIN ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline 
 	FROM task 
) t ON e.id = t.emploee_id  

Y reescríbalo usando la declaración CON:

WITH task2(id, employee_id, name, deadline) 
   AS (SELECT id, IFNULL(employee_id, 4), name, deadline FROM task) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id

O puede prescindir de los nombres de las columnas, pero luego deberá especificar un alias para la función IFNULL():

WITH task2 AS ( 
 	SELECT id, IFNULL(employee_id, 4) AS employee_id, name, deadline FROM task 
) 
SELECT * FROM employee e JOIN task2 t ON e.id = t.emploee_id