SQL - Subconsultas

Uma consulta subconsulta ou interna ou uma consulta aninhada é uma consulta dentro de outra consulta SQL e incorporada na cláusula WHERE.

Uma subconsulta é usada para retornar dados que serão usados na consulta principal como uma condição para restringir ainda mais os dados a serem recuperados.

As subconsultas podem ser usadas com as instruções SELECT, INSERT, UPDATE e DELETE junto com os operadores como =, <,>,> =, <=, IN, BETWEEN, etc.

Existem algumas regras que as subconsultas devem seguir -

As subconsultas devem ser colocadas entre parênteses.
Uma subconsulta pode ter apenas uma coluna na cláusula SELECT, a menos que várias colunas estejam na consulta principal da subconsulta para comparar suas colunas selecionadas.
Um comando ORDER BY não pode ser usado em uma subconsulta, embora a consulta principal possa usar um ORDER BY. O comando GROUP BY pode ser usado para executar a mesma função que ORDER BY em uma subconsulta.
As subconsultas que retornam mais de uma linha só podem ser usadas com vários operadores de valor, como o operador IN.
A lista SELECT não pode incluir nenhuma referência a valores que avaliem para BLOB, ARRAY, CLOB ou NCLOB.
Uma subconsulta não pode ser imediatamente incluída em uma função definida.
O operador BETWEEN não pode ser usado com uma subconsulta. No entanto, o operador BETWEEN pode ser usado na subconsulta.

Subconsultas com a instrução SELECT

As subconsultas são usadas com mais freqüência com a instrução SELECT. A sintaxe básica é a seguinte -

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
   (SELECT column_name [, column_name ]
   FROM table1 [, table2 ]
   [WHERE])

Exemplo

Considere a tabela CLIENTES com os seguintes registros -

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Agora, vamos verificar a seguinte subconsulta com uma instrução SELECT.

SQL> SELECT * 
   FROM CUSTOMERS 
   WHERE ID IN (SELECT ID 
         FROM CUSTOMERS 
         WHERE SALARY > 4500) ;

Isso produziria o seguinte resultado.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  5 | Hardik   |  27 | Bhopal  |  8500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+

Subconsultas com a instrução INSERT

As subconsultas também podem ser usadas com instruções INSERT. A instrução INSERT usa os dados retornados da subconsulta para inserir em outra tabela. Os dados selecionados na subconsulta podem ser modificados com qualquer uma das funções de caractere, data ou número.

A sintaxe básica é a seguinte.

INSERT INTO table_name [ (column1 [, column2 ]) ]
   SELECT [ *|column1 [, column2 ]
   FROM table1 [, table2 ]
   [ WHERE VALUE OPERATOR ]

Exemplo

Considere uma tabela CUSTOMERS_BKP com estrutura semelhante à tabela CUSTOMERS. Agora, para copiar a tabela CUSTOMERS completa para a tabela CUSTOMERS_BKP, você pode usar a seguinte sintaxe.

SQL> INSERT INTO CUSTOMERS_BKP
   SELECT * FROM CUSTOMERS 
   WHERE ID IN (SELECT ID 
   FROM CUSTOMERS) ;

Subconsultas com a instrução UPDATE

A subconsulta pode ser usada em conjunto com a instrução UPDATE. Uma única coluna ou várias colunas em uma tabela podem ser atualizadas ao usar uma subconsulta com a instrução UPDATE.

A sintaxe básica é a seguinte.

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Exemplo

Assumindo que temos a tabela CUSTOMERS_BKP disponível, que é o backup da tabela CUSTOMERS. O exemplo a seguir atualiza SALÁRIO em 0,25 vezes na tabela CUSTOMERS para todos os clientes cuja IDADE seja maior ou igual a 27.

SQL> UPDATE CUSTOMERS
   SET SALARY = SALARY * 0.25
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );

Isso afetaria duas linhas e, finalmente, a tabela CUSTOMERS teria os seguintes registros.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  35 | Ahmedabad |   125.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  2125.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Subconsultas com a instrução DELETE

A subconsulta pode ser usada em conjunto com a instrução DELETE como com qualquer outra instrução mencionada acima.

A sintaxe básica é a seguinte.

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ]

Exemplo

Supondo que temos uma tabela CUSTOMERS_BKP disponível que é um backup da tabela CUSTOMERS. O exemplo a seguir exclui os registros da tabela CUSTOMERS para todos os clientes cuja IDADE seja maior ou igual a 27.

SQL> DELETE FROM CUSTOMERS
   WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP
      WHERE AGE >= 27 );

Isso afetaria duas linhas e, finalmente, a tabela CUSTOMERS teria os seguintes registros.

+----+----------+-----+---------+----------+
| ID | NAME     | AGE | ADDRESS | SALARY   |
+----+----------+-----+---------+----------+
|  2 | Khilan   |  25 | Delhi   |  1500.00 |
|  3 | kaushik  |  23 | Kota    |  2000.00 |
|  4 | Chaitali |  25 | Mumbai  |  6500.00 |
|  6 | Komal    |  22 | MP      |  4500.00 |
|  7 | Muffy    |  24 | Indore  | 10000.00 |
+----+----------+-----+---------+----------+