SQL Joins
Links relacionados: Banco de Dados
Tipos de SQL Joins
Exemplo
Iremos aqui trabalhar com um pequeno exemplo. Vamos considerar alunos que sabem determinadas linguagens de programação. Segue a criação das tabelas que iremos utilizar:
Modelo Entidade-Relacionamento e do Banco de Dados
Criação das Tabelas
CREATE TABLE aluno (
idaluno INT,
nome VARCHAR(255),
PRIMARY KEY (idaluno)
);
CREATE TABLE linguagem (
idlinguagem INT,
nome VARCHAR(255),
PRIMARY KEY (idlinguagem)
);
CREATE TABLE aluno_linguagem (
idaluno INT REFERENCES aluno,
idlinguagem INT REFERENCES linguagem,
PRIMARY KEY (idaluno, idlinguagem)
);
Alimentação da Base de Dados para os Testes
INSERT INTO aluno(idaluno, nome) VALUES
(1, 'Mikasa'),
(2, 'Armin'),
(3, 'Eren'),
(4, 'Levi'),
(5, 'Krista'),
(6, 'Mina');
INSERT INTO linguagem (idlinguagem, nome) VALUES
(1, 'C'),
(2, 'C++'),
(3, 'Python'),
(4, 'Java'),
(5, 'PHP'),
(6, 'Javascript');
INSERT INTO aluno_linguagem (idaluno, idlinguagem) VALUES
(1, 1), (1, 2), (1, 3),
(2, 2), (2, 4),
(3, 4), (3, 6),
(4, 3), (4, 5),
(5, 1), (5, 3),
(6, 1), (5, 2), (6, 3), (6, 5);
Relacionamento
Mostrar as linguagens conhecidas pelos alunos, ordenado pela linguagem:
SELECT l.nome AS linguagem_nome, a.nome AS aluno_nome
FROM aluno a, linguagem l, aluno_linguagem r
WHERE
a.idaluno = r.idaluno AND
l.idlinguagem = r.idlinguagem
ORDER BY linguagem_nome;
linguagem_nome | aluno_nome ----------------+------------ C | Mina C | Krista C | Mikasa C++ | Krista C++ | Armin C++ | Mikasa Java | Armin Java | Eren Javascript | Eren PHP | Mina PHP | Levi Python | Mikasa Python | Levi Python | Krista Python | Mina
INNER JOIN
Listar todos os alunos que sabem C++ e também Python:
SELECT
p.aluno_nome AS nome,
p.linguagem_nome AS linguagem_a,
q.linguagem_nome AS linguagem_b
FROM
(SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
FROM aluno a, linguagem l, aluno_linguagem r
WHERE
a.idaluno = r.idaluno AND
l.idlinguagem = r.idlinguagem AND
l.idlinguagem = '2') p
INNER JOIN
(SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
FROM aluno a, linguagem l, aluno_linguagem r
WHERE
a.idaluno = r.idaluno AND
l.idlinguagem = r.idlinguagem AND
l.idlinguagem = '3') q
ON p.aluno_nome=q.aluno_nome;
nome | linguagem_a | linguagem_b --------+-------------+------------- Mikasa | C++ | Python Krista | C++ | Python
Criando visões
Criar uma visão com todos os alunos que sabem C++:
CREATE VIEW cpp AS
SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
FROM aluno a, linguagem l, aluno_linguagem r
WHERE
a.idaluno = r.idaluno AND
l.idlinguagem = r.idlinguagem AND
l.nome = 'C++';
aulajoin=# select * from cpp; aluno_nome | linguagem_nome ------------+---------------- Mikasa | C++ Armin | C++ Krista | C++
Criar uma visão com todos os alunos que sabem Python:
CREATE VIEW python AS
SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
FROM aluno a, linguagem l, aluno_linguagem r
WHERE
a.idaluno = r.idaluno AND
l.idlinguagem = r.idlinguagem AND
l.nome = 'Python';
aulajoin=# select * from python; aluno_nome | linguagem_nome ------------+---------------- Mikasa | Python Levi | Python Krista | Python Mina | Python
Inner Join reduzido
1SELECT
2 c.aluno_nome AS nome,
3 c.linguagem_nome AS linguagem_a,
4 p.linguagem_nome AS linguagem_b
5FROM cpp c INNER JOIN python p
6ON c.aluno_nome = p.aluno_nome;
nome | linguagem_a | linguagem_b --------+-------------+------------- Mikasa | C++ | Python Krista | C++ | Python
RIGHT JOIN
Mostrar todos os alunos que sabem Python:
SELECT * FROM cpp c
RIGHT JOIN python p
ON c.aluno_nome = p.aluno_nome;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- Mikasa | C++ | Mikasa | Python | | Levi | Python Krista | C++ | Krista | Python | | Mina | Python
Mostrar todos os alunos que sabem Python, mas não sabem C++:
SELECT * FROM cpp c
RIGHT JOIN python p
ON c.aluno_nome = p.aluno_nome
WHERE c.aluno_nome IS null;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- | | Levi | Python | | Mina | Python
LEFT JOIN
Mostrar todos os alunos que sabem C++:
SELECT * FROM cpp c
LEFT JOIN python p
ON c.aluno_nome = p.aluno_nome;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- Mikasa | C++ | Mikasa | Python Armin | C++ | | Krista | C++ | Krista | Python
Mostrar todos os alunos que sabem C++, mas não sabem Python:
SELECT * FROM cpp c
LEFT JOIN python p
ON c.aluno_nome = p.aluno_nome
WHERE p.aluno_nome IS null;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- Armin | C++ | |
FULL OUTER JOIN
Mostrar todos os alunos que sabem C++ ou Python ou ambas as linguagens:
SELECT * FROM cpp c
FULL OUTER JOIN python p
ON c.aluno_nome = p.aluno_nome;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- Mikasa | C++ | Mikasa | Python Armin | C++ | | Krista | C++ | Krista | Python | | Levi | Python | | Mina | Python
Mostrar todos os alunos que apenas conhecem C++ ou Python, mas não ambas:
SELECT * FROM cpp c
FULL OUTER JOIN python p
ON c.aluno_nome = p.aluno_nome
WHERE
c.aluno_nome IS null OR
p.aluno_nome IS null;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome ------------+----------------+------------+---------------- Armin | C++ | | | | Levi | Python | | Mina | Python