SQL Joins
De Aulas
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
1CREATE TABLE aluno (
2 idaluno INT,
3 nome VARCHAR(255),
4 PRIMARY KEY (idaluno)
5);
6
7CREATE TABLE linguagem (
8 idlinguagem INT,
9 nome VARCHAR(255),
10 PRIMARY KEY (idlinguagem)
11);
12
13CREATE TABLE aluno_linguagem (
14 idaluno INT REFERENCES aluno,
15 idlinguagem INT REFERENCES linguagem,
16 PRIMARY KEY (idaluno, idlinguagem)
17);
Alimentação da Base de Dados para os Testes
1INSERT INTO aluno(idaluno, nome) VALUES
2 (1, 'Mikasa'),
3 (2, 'Armin'),
4 (3, 'Eren'),
5 (4, 'Levi'),
6 (5, 'Krista'),
7 (6, 'Mina');
8
9INSERT INTO linguagem (idlinguagem, nome) VALUES
10 (1, 'C'),
11 (2, 'C++'),
12 (3, 'Python'),
13 (4, 'Java'),
14 (5, 'PHP'),
15 (6, 'Javascript');
16
17INSERT INTO aluno_linguagem (idaluno, idlinguagem) VALUES
18 (1, 1), (1, 2), (1, 3),
19 (2, 2), (2, 4),
20 (3, 4), (3, 6),
21 (4, 3), (4, 5),
22 (5, 1), (5, 3),
23 (6, 1), (5, 2), (6, 3), (6, 5);
Relacionamento
Mostrar as linguagens conhecidas pelos alunos, ordenado pela linguagem:
1SELECT l.nome AS linguagem_nome, a.nome AS aluno_nome
2FROM aluno a, linguagem l, aluno_linguagem r
3WHERE
4 a.idaluno = r.idaluno AND
5 l.idlinguagem = r.idlinguagem
6ORDER 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:
1SELECT
2 p.aluno_nome AS nome,
3 p.linguagem_nome AS linguagem_a,
4 q.linguagem_nome AS linguagem_b
5FROM
6(SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
7FROM aluno a, linguagem l, aluno_linguagem r
8WHERE
9 a.idaluno = r.idaluno AND
10 l.idlinguagem = r.idlinguagem AND
11 l.idlinguagem = '2') p
12INNER JOIN
13(SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
14FROM aluno a, linguagem l, aluno_linguagem r
15WHERE
16 a.idaluno = r.idaluno AND
17 l.idlinguagem = r.idlinguagem AND
18 l.idlinguagem = '3') q
19ON 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++:
1CREATE VIEW cpp AS
2SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
3FROM aluno a, linguagem l, aluno_linguagem r
4WHERE
5 a.idaluno = r.idaluno AND
6 l.idlinguagem = r.idlinguagem AND
7 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:
1CREATE VIEW python AS
2SELECT a.nome AS aluno_nome, l.nome AS linguagem_nome
3FROM aluno a, linguagem l, aluno_linguagem r
4WHERE
5 a.idaluno = r.idaluno AND
6 l.idlinguagem = r.idlinguagem AND
7 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:
1SELECT * FROM cpp c
2RIGHT JOIN python p
3ON 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++:
1SELECT * FROM cpp c
2RIGHT JOIN python p
3ON c.aluno_nome = p.aluno_nome
4WHERE 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++:
1SELECT * FROM cpp c
2LEFT JOIN python p
3ON 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:
1SELECT * FROM cpp c
2LEFT JOIN python p
3ON c.aluno_nome = p.aluno_nome
4WHERE 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:
1SELECT * FROM cpp c
2FULL OUTER JOIN python p
3ON 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:
1SELECT * FROM cpp c
2FULL OUTER JOIN python p
3ON c.aluno_nome = p.aluno_nome
4WHERE
5 c.aluno_nome IS null OR
6 p.aluno_nome IS null;
aluno_nome | linguagem_nome | aluno_nome | linguagem_nome
------------+----------------+------------+----------------
Armin | C++ | |
| | Levi | Python
| | Mina | Python
