Aula 6: Associação de Tabelas e Subconsultas

1. Associação de Tabelas (JOINs)

Ao trabalhar com bancos de dados relacionais, os dados muitas vezes estão distribuídos em várias tabelas. Para obter informações dessas tabelas relacionadas, utilizamos os JOINs.

1.1. Tipos de JOINs

1.1.1. INNER JOIN

Retorna apenas os registros que possuem correspondências em ambas as tabelas.

Sintaxe:

SELECT colunas
FROM tabela1
INNER JOIN tabela2
ON tabela1.coluna = tabela2.coluna;

Exemplo:

SELECT alunos.nome, cursos.nome
FROM alunos
INNER JOIN cursos
ON alunos.curso_id = cursos.id;

1.1.2. LEFT JOIN

Retorna todos os registros da tabela da esquerda e os registros correspondentes da tabela da direita. Quando não há correspondência, o resultado será NULL.

Sintaxe:

SELECT colunas
FROM tabela1
LEFT JOIN tabela2
ON tabela1.coluna = tabela2.coluna;

Exemplo:

SELECT alunos.nome, cursos.nome
FROM alunos
LEFT JOIN cursos
ON alunos.curso_id = cursos.id;

1.1.3. RIGHT JOIN

Retorna todos os registros da tabela da direita e os correspondentes da tabela da esquerda. Quando não há correspondência, o resultado será NULL.

Sintaxe:

SELECT colunas
FROM tabela1
RIGHT JOIN tabela2
ON tabela1.coluna = tabela2.coluna;

Exemplo:

SELECT alunos.nome, cursos.nome
FROM alunos
RIGHT JOIN cursos
ON alunos.curso_id = cursos.id;

1.1.4. FULL OUTER JOIN

Retorna todos os registros quando há uma correspondência em uma das tabelas. Se não houver correspondência, o resultado será NULL em colunas sem correspondência.

Sintaxe:

SELECT colunas
FROM tabela1
FULL OUTER JOIN tabela2
ON tabela1.coluna = tabela2.coluna;

Exemplo:

SELECT alunos.nome, cursos.nome
FROM alunos
FULL OUTER JOIN cursos
ON alunos.curso_id = cursos.id;

1.1.5. CROSS JOIN

Realiza o produto cartesiano, ou seja, combina todos os registros de ambas as tabelas.

Sintaxe:

SELECT colunas
FROM tabela1
CROSS JOIN tabela2;

Exemplo:

SELECT alunos.nome, cursos.nome
FROM alunos
CROSS JOIN cursos;

2. Subconsultas (Subqueries)

Uma subconsulta é uma consulta dentro de outra consulta. Ela pode ser usada em cláusulas como SELECT, WHERE e FROM.

2.1. Subconsulta em SELECT

Retorna um valor calculado por uma subconsulta dentro de uma consulta principal.

Exemplo:

SELECT nome, (SELECT COUNT(*) FROM matriculas WHERE alunos.id = matriculas.aluno_id) AS total_matriculas
FROM alunos;

2.2. Subconsulta em WHERE

A subconsulta pode ser usada para filtrar os resultados da consulta principal.

Exemplo:

SELECT nome
FROM alunos
WHERE curso_id = (SELECT id FROM cursos WHERE nome = 'Desenvolvimento de Sistemas');

2.3. Subconsulta com IN e NOT IN

Verifica se o valor existe em uma lista retornada pela subconsulta.

Exemplo:

SELECT nome
FROM alunos
WHERE curso_id IN (SELECT id FROM cursos WHERE duracao > 200);

2.4. Subconsulta com EXISTS

Verifica se a subconsulta retorna algum resultado.

Exemplo:

SELECT nome
FROM alunos
WHERE EXISTS (SELECT * FROM matriculas WHERE alunos.id = matriculas.aluno_id);

3. Exercícios Práticos

1. INNER JOIN: Liste os nomes dos alunos e seus respectivos cursos utilizando um INNER JOIN. SELECT alunos.nome, cursos.nome FROM alunos INNER JOIN cursos ON alunos.curso_id = cursos.id;
2. LEFT JOIN: Liste todos os cursos, mesmo aqueles que não possuem alunos matriculados, usando um LEFT JOIN. SELECT cursos.nome, alunos.nome FROM cursos LEFT JOIN alunos ON cursos.id = alunos.curso_id;
3. Subconsulta em WHERE: Liste os nomes dos alunos que estão matriculados em cursos com duração superior a 200 horas. SELECT nome FROM alunos WHERE curso_id IN (SELECT id FROM cursos WHERE duracao > 200);
4. Subconsulta com EXISTS: Liste os alunos que estão matriculados em pelo menos um curso. SELECT nome FROM alunos WHERE EXISTS (SELECT * FROM matriculas WHERE alunos.id = matriculas.aluno_id);

4. Desafio

1. Crie uma consulta usando RIGHT JOIN que mostre todos os cursos e seus respectivos alunos, incluindo os cursos que ainda não têm alunos matriculados.
2. Escreva uma subconsulta para listar os cursos que possuem mais de 5 alunos matriculados.
3. Utilize CROSS JOIN para listar todas as combinações possíveis de alunos e cursos, independentemente de estarem matriculados ou não.