Aula 13: Subqueries e CTEs — WITH clause (Common Table Expressions)

Nesta aula, vamos explorar profundamente o uso de Subqueries e CTEs no contexto do Oracle SQL. Subconsultas e Common Table Expressions são componentes essenciais para a escrita de consultas complexas e otimização de código. Iremos compreender os conceitos fundamentais, ver exemplos práticos e aprender a aplicar técnicas que melhoram a performance e a legibilidade dos scripts SQL. Ao final, você será capaz de criar consultas poderosas usando estas técnicas, habilidades extremamente valorizadas no mercado de TI.

O que você vai aprender nesta aula

• Compreender o conceito e sintaxe de subqueries em Oracle SQL.
• Dominar o uso da cláusula WITH para criar Common Table Expressions (CTEs).
• Aplicar subqueries e CTEs para otimização de consultas.
• Resolver problemas práticos e complexos de consulta usando estas técnicas.
• Identificar e corrigir erros comuns associados ao uso de subqueries e CTEs.

Pré-requisitos e Ambiente

Antes de iniciar, certifique-se de ter o Oracle Database instalado e configurado. Recomendamos o uso da versão 12c ou superior para garantir a compatibilidade com todos os exemplos fornecidos. Além disso, ter um conhecimento básico de SQL e ambiente SQL*Plus ou SQL Developer é essencial.

Introdução às Subqueries

As subqueries são instruções SQL aninhadas dentro de outra consulta SQL. Elas são usadas para retornar dados que serão usados na consulta pai como condição para restringir ou filtrar resultados. As subqueries podem ser inseridas em várias partes de uma consulta, como na cláusula WHERE, FROM ou SELECT.

Exemplo de Subquery

Vamos considerar um exemplo prático, onde precisamos encontrar o salário médio de todos os departamentos e, em seguida, filtrar para os empregados cujo salário é superior a este valor médio.

SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

No exemplo acima, a subquery calcula o salário médio de todos os funcionários, e o resultado é usado na cláusula WHERE para filtrar funcionários com salários acima dessa média.

Introdução às Common Table Expressions (CTEs)

Uma Common Table Expression (CTE) é uma consulta temporária que você pode definir dentro uma instrução SELECT. Elas oferecem uma maneira mais legível e organizada para criar consultas complexas, especialmente quando você precisa referenciar a mesma subquery várias vezes.

Usando a Cláusula WITH para Criar CTEs

A cláusula WITH é usada para definir uma CTE. Vamos criar uma CTE que calcula o salário médio dos departamentos e utiliza esta CTE em uma consulta principal.

WITH AvgDeptSal AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.employee_id, e.first_name, e.salary
FROM employees e
JOIN AvgDeptSal a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

No exemplo acima, AvgDeptSal é a CTE que calcula o salário médio por departamento, e a consulta principal utiliza esta informação para filtrar os funcionários.

Verificando a Instalação / Testando a Configuração

Após configurar o ambiente e executar os exemplos, vamos verificar se tudo está correto.

SELECT COUNT(*) FROM employees;

105

Se o comando acima retornar um número, significa que a configuração está correta e a conexão com o banco de dados foi bem-sucedida.

Erros Comuns e Como Resolver

• ORA-00942: A tabela ou view não existe – Verifique se todos os nomes de tabelas estão corretos.
• ORA-00904: Identificador inválido – Cheque os nomes das colunas utilizados em consultas e subqueries.
• ORA-01427: Linha única esperada, mas são retornadas múltiplas – Isso ocorre quando a subquery não retorna exatamente uma linha para operações que esperam um único valor.
• ORA-06553: PL/SQL: subprograma retornou erros nonéxistentes – Assegure-se de que não haja erros de sintaxe nas subqueries e CTEs.

Boas Práticas e Dicas Avançadas

Durante o desenvolvimento de consultas complexas, sempre prefira o uso de CTEs quando lidar com subqueries repetidas, pois isso melhora a legibilidade e facilita a manutenção do código. Outra dica importante é sempre verificar os custos estimados das consultas para otimizar a performance.

Resumo da Aula 13

Nesta aula, exploramos o uso de Subqueries e CTEs no Oracle SQL, aprendendo a diferenciar suas aplicações e otimizar consultas para obter resultados interessantes e eficientes. Em nossos projetos na JRT Technology Solutions, aplicamos essas técnicas para reforçar a performance e clareza no desenvolvimento de relatórios complexos. Na próxima aula, vamos aprofundar em Joins Avançados e Performance Tuning, complementando o conhecimento que iniciamos hoje.