Subconsultas com tabelas

Subconsulta retorna uma tabela

E por fim, a terceira opção é quando a subconsulta retorna a tabela inteira. Esta é a opção mais comum.

Muitas vezes, há situações em que queremos ajustar um pouco uma determinada mesa. E só então junte (usando o operador JOIN ON) a tabela corrigida com outra.

Vamos começar com o caso mais simples, onde juntamos duas tabelas com um JOIN:

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

E como você provavelmente se lembra, existem tarefas na tabela de tarefas que não são atribuídas a ninguém: employee_id é NULL .

Vamos gerar uma tabela corrigida , onde atribuímos todas as tarefas pendentes ao diretor (seu ID = 4).

Para fazer isso, usamos a função IFNULL() :

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

E o resultado desta consulta:

A célula corrigida é marcada em vermelho.

Agora vamos substituir nossa tabela corrigida na consulta:

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

Em vez da tabela de tarefas .

Tal solicitação seria mais ou menos assim:

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

Em vez da palavra task , escrevemos parênteses e colocamos o corpo da solicitação neles.

A propósito, o alias t (alias) para a consulta aninhada foi muito útil. Uma consulta aninhada, ao contrário de uma tabela, não tem seu próprio nome, então o alias está muito fora de lugar.

E aqui está o resultado de tal consulta:

Nosso diretor tem a função de “limpar o escritório”, acho que ele encontrará rapidamente alguém para quem delegar :) Usando a instrução WITH

A propósito, a partir da versão 8 do MySQL, você não precisa mais colocar todas as suas subconsultas dentro da consulta final. Eles podem ser executados separadamente. Para isso, a instrução WITH é usada .

Ele permite criar uma tabela virtual (chamada consulta) e sua aparência é dada por um template:

WITH Name AS (request) 

Muitas vezes, sua subconsulta tem colunas sem nome, como COUNT(*), às quais você não atribuiu um nome exclusivo. Nesse caso, a instrução WITH tem a opção de especificar novos nomes de colunas para a subconsulta.

Sua segunda forma é dada pelo modelo:

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

Você pode usar quantas tabelas virtuais (consultas nomeadas) quiser e fazer referência umas às outras nelas. A forma geral do seu pedido terá algo assim:

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

Agora vamos pegar nossa consulta assustadora:

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  

E reescreva-o usando a instrução WITH:

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

Ou você pode fazer sem nomes de coluna, mas terá que especificar um alias para a função 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