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
- Selecione o código do departamento, nome do departamento e quantidade de funcionários que trabalham no respectivo departamento;
- Selecione o código do departamento, nome do departamento e a média de salário do respectivo departamento;
- 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;
- Selecione a soma de todos os salários dos funcionários subordinados à Steven King (inclusive o próprio Steven King)
- 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)
