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:

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