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)