SUBQUERIES

• Subqueries são divisões de uma instrução SELECT, a qual estarão separados entre parentes;
• Servem geralmente para resolver problemas que teriam que ser feitas com 2 consultas;
• Também utilizada quando um resultado encontrado é base de condição para uma outra consulta;
• A ordem de execução da SUBQUERY depende de como ela é escrita;
• Para as SUBQUERIES mais simples, são executadas as mais internas para depois ser executado a mais externa. Esse tipo de subquerie também é conhecida como NÃO-CORRELACIONADA;
• Há também SUBQUERIES quer dependem tanto da query externa quando interna para ser executada.
• É quando existe um relacionamento entre as QUERIES, ou seja, para cada linha da query mais externa, depende do resultado de uma linha (ou mais) de uma query mais interna. É também chamada de CORRELACIONADA;
• Não é necessário utilizar order by em queries mais internas;
• As subconsultas devem estar entre parênteses;
• Tome cuidado com subconsultas que retornam várias linhas utilizando operadores que suporte somente uma única linha;
• Exemplo de um problema que exigiria 2 consultas:
  • Qual(is) funcionário(s) que ganham igual ou mais que o funcionário Bruce do departamento 60?

SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     DEPARTMENT_ID = 60;

• No método tradicional, primeiro precisamos saber o quanto o funcionário Bruce ganha:

SELECT  FIRST_NAME,
        SALARY,
        DEPARTMENT_ID
FROM      EMPLOYEES
WHERE   FIRST_NAME = 'Bruce';

• Depois de encontrar o valor do salário de Bruce, colocamos o valor na cláusula SELECT:

SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     SALARY >= 6000
          AND DEPARTMENT_ID = 60;

• Podemos resolver da seguinte forma:

SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     DEPARTMENT_ID = 60
          AND SALARY >= (SELECT  SALARY
                         FROM    EMPLOYEES
                         WHERE   FIRST_NAME = 'Bruce');

TIPOS DE SUBQUERIES

• Subqueries podem retornar uma única linha, várias linhas e várias colunas;
• Quando a subquerie retorna uma única linha, podemos utilizar os operadores =, <, >, <>, <= ou >=;
• Quando a subquerie retorna várias linhas, temos que utilizar o IN, ALL ou ANY (uma ou mais linhas)
• As subqueries (internas) podem também utilizar funções de grupo (min, max, avg, …), com isso, a subquerie retornará um única linha;
• Subqueries são recursos poderosos do comando SELECT, que basicamente podemos ter retornar qualquer informação do banco de dados;
• Basicamente, é possível utilizar subquerie em qualquer cláusula de uma SELECT;
• Caso a subconsulta não retorna registros, nenhum valor será retornado para toda a SELECT;

EXEMPLO 1:

SELECT  FIRST_NAME, JOB_ID,SALARY,
        DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   JOB_ID =     (SELECT JOB_ID
                      FROM EMPLOYEES
                      WHERE EMPLOYEE_ID = 111)
        AND SALARY > (SELECT SALARY
                      FROM   EMPLOYEES
                      WHERE  EMPLOYEE_ID = 110);

EXEMPLO 2:

SELECT  FIRST_NAME, JOB_ID,SALARY,
        DEPARTMENT_ID
        FROM    EMPLOYEES
        WHERE   JOB_ID      = (SELECT JOB_ID
                               FROM EMPLOYEES
                               WHERE EMPLOYEE_ID = 111)
                AND SALARY <= (SELECT SALARY
                               FROM   EMPLOYEES
                               WHERE  EMPLOYEE_ID = 110);

EXEMPLO 3

SELECT  FIRST_NAME,         JOB_ID,
        SALARY,         DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   SALARY =  (SELECT MIN(SALARY)
                   FROM   EMPLOYEES);

EXEMPLO 4:

SELECT   JOB_ID, AVG(SALARY)
FROM     EMPLOYEES
GROUP BY JOB_ID
HAVING   AVG(SALARY) = (SELECT  MIN(AVG(SALARY))
                        FROM   EMPLOYEES
                        GROUP BY JOB_ID);

EXEMPLO 5 (ERRO)

SELECT  EMPLOYEE_ID, LAST_ANME
FROM    EMPLOYEES
WHERE   SALARY = (SELECT MIN(SALARY)
                  FROM    EMPLOYEES
                  GROUP BY DEPARTMENT_ID);

EXEMPLO 6 (RETORNO NULO)

SELECT  EMPLOYEE_ID, LAST_NAME
FROM    EMPLOYEES
WHERE   COMMISSION_PCT = (SELECT COMMISSION_PCT
                          FROM    EMPLOYEES
                          WHERE FIRST_NAME = 'Santiago');

SUBQUERIES DE VÁRIAS LINHAS

• Quando temos em uma Subquerie que retorna várias linhas, temos que verificar qual o operador que podemos utilizar. O operador precisa ser compatível com a quantidade de retorno;
• Quando temos essa situação, temos que utilizar os operadores IN, ANY e ALL;

EXEMPLO DE IN

SELECT  JOB_ID, FIRST_NAME, SALARY
FROM    EMPLOYEES
WHERE   SALARY IN (SELECT MIN(SALARY)
                   FROM EMPLOYEES
                   GROUP BY JOB_ID)
ORDER BY JOB_ID;

EXEMPLO DE ANY

SELECT    EMPLOYEE_ID,   FIRST_NAME,
          JOB_ID, SALARY
FROM    EMPLOYEES
WHERE   SALARY < ANY (SELECT SALARY
                      FROM EMPLOYEES
                      WHERE JOB_ID = 'IT_PROG')
        AND JOB_ID <> 'IT_PROG'
ORDER BY SALARY DESC;

EXEMPLO DE ALL

SELECT    EMPLOYEE_ID,   FIRST_NAME,
          JOB_ID,   SALARY
FROM      EMPLOYEES
WHERE     SALARY > ALL (SELECT SALARY
                        FROM EMPLOYEES
                        WHERE JOB_ID = 'IT_PROG')
              AND JOB_ID <> 'IT_PROG'
ORDER BY SALARY DESC;

OPERADORES DE CONJUNTO

• São utilizados geralmente para se fazer a união de duas ou mais queries
• Trará um único resultado da consulta obtida;
• Geralmente, precisam ser compatíveis os tipos e quantidade de colunas;
• Podemos utilizar os seguintes comandos de operadores de conjunto: UNION, UNION ALL, INTERSECT, MINUS;

UNION: Retorna todas as linhas distintas das consultas que estarão envolvidas, precisando ter quantidade de colunas e tipos iguais; UNION ALL: Retorna todas as linhas (inclusive as duplicadas) das consultas que estarão envolvidas, precisando ter quantidade de quantidade de colunas e tipos iguais; Se possível, utilize o UNION ALL ao invés de UNION, pois ele tem mais performance no retorno de dados;

INTERSECT: Retorna todas as linhas das consultas que estarão envolvidas, presentes em ambas consultas (ou demais consultas); MINUS: Retorna todas as linhas que estão na primeira consulta que não estão na segunda consulta;

ORDEM DE EXECUÇÃO DOS OPERADORES

• Para o ORACLE, não há um operador que tenha prioridade de primeira execução;
• A ordem padrão de execução é de cima para baixo;
• A ordem de execução pode ter colocação de prioridade colocando parênteses;

UNION

• Retorna o resultados de duas ou mais consultas, eliminando resultados repetidos;
• A repetição de resultados é considerada toda a tupla;
• Os tipos de dados e quantidade de colunas devem ser os mesmos entre as queries;
• Não é necessário ter o mesmo nome de colunas entre as queries;
• Valores duplicados não serão ignorados (o comando irá retirar valores repetidos)
• O operador IN é executado antes do operador UNION;
• Será feito um ORDER BY automático da primeira coluna;

EXEMPLO DE UNION

SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
UNION
SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

UNION ALL

• Retorna o resultados de duas ou mais consultas, INCLUSIVE resultados repetidos;
• Possui as mesmas diretivas do UNION;
• NÃO ordena o resultado pela primeira coluna;
• O operador DISTINCT não pode ser utilizado;
• O retorno é mais rápido que a utilização do UNION;

EXEMPLO DE UNION ALL

SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
UNION ALL
SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

SELECT    EMPLOYEE_ID,
JOB_ID,
DEPARTMENT_ID
FROM    EMPLOYEES

UNION ALL
SELECT    EMPLOYEE_ID,
JOB_ID,
DEPARTMENT_ID
FROM    JOB_HISTORY;

INTERSECT

• Retorna as linhas que estejam em duas ou mais consultas, ou seja, somente os resultados iguais das consultas são retornados pelo operador;
• Faz a intersecção dos resultados de todas as consultas;
• É necessário ter o mesmo número e tipos de colunas;
• A ordem das consultas não influenciam no resultado;
• O INTERSECT não ignora valores nulos;

EXEMPLO DE INTERSECT
pre class=”brush: sql; title: ; notranslate” title=””>
SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
INTERSECT SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

MINUS

• Retorna as linhas da primeira consulta que NÃO estão na segunda consulta;
• Faz a “subtração” de linhas entre a primeira e segunda consulta;
• Caso tenha mais de duas consultas, a resolução é feita de cima para baixo;
• É necessário ter o mesmo número e tipo de colunas nas consultas;
• A alteração das consultas influencia no resultado final;
• Todas as colunas da cláusula WHERE precisam estar na cláusula SELECT;
• EXEMPLO DE MINUS

  SELECT  EMPLOYEE_ID, JOB_ID
  FROM    EMPLOYEES
  MINUS
  SELECT  EMPLOYEE_ID, JOB_ID
  FROM    JOB_HISTORY;
  

• DIRETRIZES GERAIS

• Para qualquer operador, o número de colunas e os tipos de dados devem ser equivalentes entre as consultas;
• A utilização de parênteses altera a sequencia de execução dos operadores;
• ORDER BY só pode ser usada no final da instrução;
• ORDER BY aceitará somente os nomes das colunas da primeira consulta ou a notação posição;
• O nome das colunas são considerados os da primeira consulta;
• Tuplas duplicadas são eliminadas automaticamente, com exceção de UNION ALL;
• Por padrão, as saídas são ordenadas em ordem crescente, com exceção de UNION ALL;
• Quando o número de colunas não são correspondentes entre as consultas, é possível resolver com NULL, ou com uma constante do mesmo tipo equivalente;
• É possível adicionar uma coluna virtual para ordenação de linhas entre as consultas;

• EXEMPLO DE TIPOS DE DADOS

  SELECT  DEPARTMENT_ID, NULL AS LOCATION,
  HIRE_DATE
  FROM    EMPLOYEES
  UNION
  SELECT  DEPARTMENT_ID,  LOCATION_ID,
  TO_DATE(NULL)
  FROM    DEPARTMENTS;
  

  EXEMPLO DE ORDEM

  SELECT    DEPARTMENT_ID, HIRE_DATE,
  2 AS ORDEM
  FROM    EMPLOYEES
  UNION
  SELECT    DEPARTMENT_ID,  TO_DATE(NULL),
  1 AS ORDEM
  FROM    DEPARTMENTS
  ORDER BY ORDEM;
  

JOINS

• Utilizamos JOINS quando precisamos consultar tabelas relacionadas numa única sentença SELECT;
• Os JOINS são muito utilizados em banco de dados relacionais, pois grande partes das consultas utilizam mais de uma tabela;
• O ORALCE 11g utiliza o padrão SQL 99, possuindo algumas sintaxes para diferentes tipos de JOINS;

NATURAL JOINS

• Une duas ou mais tabelas que tenham colunas do mesmo nome e do mesmo tipo;
• Caso se tenha o tipo de dado diferente, o ORACLE irá retornar um erro;
• Quando há mais de um campo com o mesmo nome e tipo de dados em tabelas diferentes que estão sendo avaliados pelo NATURAL JOIN, todos esses campos são considerados;
• Quando utilizamos o USING, NÃO podemos usar ALIAS em nomes de colunas e tabelas
• EXEMPLO DE NATURAL JOINS

SELECT  *
FROM    EMPLOYEES;

SELECT  *
FROM    DEPARTMENTS;

SELECT  FIRST_NAME, LAST_NAME, DEPARTMENT_NAME
FROM    EMPLOYEES
        NATURAL JOIN DEPARTMENTS
WHERE   DEPARTMENT_NAME IN ('IT','Finance','Sales');

JOIN COM USING

• Utilizamos USING quando precisamos relacionar uma coluna entre diferentes tabelas (diferente do NATURAL, que irá obrigatoriamente fazer o relacionamento com todas);
• Quando utilizamos o USING, NÃO podemos usar ALIAS em nomes de colunas e tabelas (DENTRO DO USING);
• EQUIJOIN é um join interno, a qual é uma alternativa de utilização do USING e NATURAL JOIN, sendo muito utilizado para ligar tabelas com chaves primárias e estrangeiras;
• Aconselhável utilização de ALIAS, mas não são obrigatórios;
• ALIAS podem ter até 30 caracteres.
• Se o ALIAS for especificado, é necessário utilizá-lo em todo comando;
• EXEMPLO DE USING

SELECT  EMPLOYEES.EMPLOYEE_ID,
        EMPLOYEES.LAST_NAME,
        DEPARTMENTS.LOCATION_ID,
        DEPARTMENT_ID
FROM    EMPLOYEES
        JOIN DEPARTMENTS
            USING (department_id) ;

--ERRO
SELECT  EMPLOYEES.EMPLOYEE_ID,
        EMPLOYEES.LAST_NAME,
        DEPARTMENTS.LOCATION_ID,
        DEPARTMENTS.DEPARTMENT_ID
FROM EMPLOYEES
     JOIN DEPARTMENTS
         USING (department_id) ;

--ERRO
SELECT  E.EMPLOYEE_ID,
        E.LAST_NAME,
        D.LOCATION_ID,
        DEPARTMENT_ID
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
           USING (D.department_id);

--EQUIJOIN
SELECT L.CITY, D.DEPARTMENT_NAME
FROM   LOCATIONS L
       JOIN DEPARTMENTS D
           USING (LOCATION_ID)
WHERE  LOCATION_ID = 1400;

CLÁUSULA ON

• Utilizado em conjunto com JOIN;
• As colunas não precisam ter o mesmo nome entre tabelas, porém precisam ter o mesmo tipo (ou compatibilizar os tipos);
• Deixa os códigos mais legíveis, comparando com outros comandos de JOIN;
• Possível fazer SELF-JOIN, quando uma tabela precisa fazer referencia com ela mesma;
• No SELF-JOIN, é necessário criar um alias na segunda tabela, para que seja possível fazer o auto-relacionamento;
• É possível utilizar conectores AND ou OR em JOIN’s, possivelmente fazendo o mesmo papel da cláusula WHERE;
• No padrão SQL 99, JOIN’s são executados da esquerda para a direita;
• Quando unimos três ou mais tabelas, também são conhecidos como JOIN’s tridimensionais;

EXEMPLO DE CLÁUSULA ON

SELECT  E.EMPLOYEE_ID,
        E.FIRST_NAME,
        E.DEPARTMENT_ID,
        D.LOCATION_ID
FROM EMPLOYEES E
     JOIN DEPARTMENTS D
         ON (E.DEPARTMENT_ID =   D.DEPARTMENT_ID);

EXEMPLO DE SELF-JOIN

SELECT  E.LAST_NAME AS EMP,   M.LAST_NAME AS MGR
FROM    EMPLOYEES E
        JOIN EMPLOYEES M
            ON (E.MANAGER_ID =   M.EMPLOYEE_ID);

EXEMPLO DE RESTRIÇÃO NO ON

SELECT  E.EMPLOYEE_ID,
        E.FIRST_NAME,
        E.DEPARTMENT_ID,
        D.LOCATION_ID
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
          ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
          AND E.MANAGER_ID = 149;

EXEMPLO DE LIGAÇÃO DE 3 TABELAS

SELECT  EMPLOYEE_ID,
        CITY,
        DEPARTMENT_NAME
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
          ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
        JOIN LOCATIONS L
          ON D.LOCATION_ID = L.LOCATION_ID;

JOIN INTERNOS E EXTERNOS

• NO EQUI-JOIN são quando temos que utilizar JOIN’s, porém não contém o operador de igualdade;
• Não são muito utilizados, pois deixam o código menos legível;
• EXEMPLO

SELECT      E.LAST_NAME,
            E.SALARY,
            J.JOB_TITLE
FROM        EMPLOYEES E
            JOIN  JOBS J
              ON E.SALARY BETWEEN   J.MIN_SALARY
              AND J.MAX_SALARY;

• Sendo os JOIN’S INTERNOS, o NATURAL JOIN, USING e ON retornam o resultado de apenas linhas iguais entre as tabelas que estão envolvidas;
• Sendo os JOIN’s EXTERNOS, LEFT, RIGHT e FULL JOIN, servem para retornar resultado que não tenham apenas linhas iguais;

LEFT OUTER JOIN ou LEFT JOIN

• Retorna todas as linhas que estão na esquerda do JOIN (como se fosse a tabela principal), sem necessariamente ter uma linha correspondente no lado oposto do JOIN;
• Para melhor visualizar, coloque em uma única linha as tabelas dos lados do JOIN;
• EXEMPLO DE LEFT OUTER JOIN

SELECT  E.FIRST_NAME,
        E.LAST_NAME,
        E.DEPARTMENT_ID,
        D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        LEFT OUTER JOIN DEPARTMENTS D
          ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

RIGHT OUTER JOIN ou RIGHT JOIN

• • Retorna todas as linhas que estão na direita do JOIN (como se fosse a tabela principal), sem necessariamente ter uma linha correspondente no lado oposto do JOIN;
  • Para melhor visualizar, coloque em uma única linha as tabelas dos lados do JOIN;
  • EXEMPLO DE RIGHT OUTER JOIN

    SELECT  E.FIRST_NAME,
            E.LAST_NAME,
            E.DEPARTMENT_ID,
            D.DEPARTMENT_NAME
    FROM    EMPLOYEES E
            RIGHT OUTER JOIN DEPARTMENTS D
              ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

FULL OUTER JOIN OU FULL JOIN

• Retorna todas as linhas que estão na direita do JOIN e na esquerda do JOIN (ao mesmo tempo);
• FULL OUTER JOIN é diferente de ter na mesma expressão SQL o LEFT e RIGHT JOIN;
• EXEMPLO DE FULL OUTER JOIN

SELECT  E.FIRST_NAME,
        E.LAST_NAME,
        E.DEPARTMENT_ID,
        D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        FULL OUTER JOIN DEPARTMENTS D
          ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

CROSS JOIN

• • Produtos cartesianos são quando todas as linhas da primeira tabela são relacionadas com todas as linhas da segunda tabela;
  • Tem pouca utilização no dia a dia;
  • Pode-se fazer massa de dados para testes, dependendo dos dados que a tabela tenha;
  • Irá retornar a quantidade de linhas resultado da multiplicação do número de linhas das duas tabelas;
  • Pode-se fazer CROSS JOIN com quaisquer tabelas, porém, a combinação dos dados não teria muito sentido;
  • EXEMPLO DE CROSS JOIN

    SELECT  E.FIRST_NAME,
            E.LAST_NAME,
            E.DEPARTMENT_ID,
            D.DEPARTMENT_NAME
    FROM    EMPLOYEES E
            CROSS JOIN DEPARTMENTS D;

EXERCÍCIOS

Utilize o schema HR do banco de dados Oracle 11g XE

1. Selecione o código do departamento, nome do departamento e quantidade de funcionários que trabalham no respectivo departamento;
2. Selecione o código do departamento, nome do departamento e a média de salário do respectivo departamento;
3. Selecione o código do serviço (job_id), a descrição do serviço, o maior valor de salário, o menor valor de salário e a média de salário por job_id;
4. Selecione a soma de todos os salários dos funcionários subordinados à Steven King (inclusive o próprio Steven King)
5. Selecione o nome e sobrenome dos empregados a qual o seu salário seja maior que a média dos salários do departmamento de TI (IT_PROD)