No primeiro post sobre a linguagem SQL fomos apresentados ao conceito de bancos de dados relacionais e a um aplicativo que nos possibilita armazenar dados em tabelas relacionadas. Nessas tabelas os dados são armazenados em colunas e cada linha dessas colunas guardam os valores de um elemento. Com a linguagem SQL podemos fazer uma junção de linhas de uma tabela com outra tabela.
Com o modelo relacional construirmos tabelas que eliminam dados duplicados, são mais flexíveis na consulta para obter dados especificísticos e fáceis de manter.
Vinculando tabelas usando JOIN
Podemos juntar tabelas durante uma consulta com a declaração JOIN ... ON (Existem outras variações que veremos mais a frente). Usamos essa declaração para vincular uma tabela a outra durante uma consulta. Desse modo usamos valores correspondentes em colunas que especificamos em ambas as tabelas. A sintaxe que vamos usar é essa:
SELECT *
FROM tabela_a JOIN tabela_b
ON tabela_a.coluna_a = tabela_b.coluna_b
Esse exemplo é parecido com a sintaxe básica da declaração SELECT. A diferença é que utilizamos JOIN para fornecer duas tabelas e não somente uma. Depois do ON é onde especificamos as colunas que queremos combinar os valores. O resultado dessa declaração é que realizamos uma consulta nas duas tabelas e retornamos as linhas onde o valor da coluna coluna_a seja igual ao valor da coluna coluna_b.
O uso mais comum da cláusula ON é procurar por colunas com valores iguais, mas também é possível realizar consultas com outras expressões onde o resultado seja verdadeiro ou falso.
Relacionando tabelas com colunas-chave
Vamos imaginar o seguinte cenário: Você é um analista de dados e acabou de ser contratado para analisar os gastos com folha de pagamento por departamento. Você faz o pedido dos dados que vai precisar esperando receber uma tabela parecida com essa:
depart localizacao nome sobrenome salario
------ ----------- -------- --------- -------
RH São Paulo Daniela Silva 62450
RH São Paulo Everton Oliveira 59300
AD Fortaleza Fábio Feitosa 87654
AD Fortaleza Dénis Reis 87654
Mas não é o que você recebe. Você acaba de receber dois arquivos csv, cada um representando uma tabela. Analisando essas tabelas você percebe que as colunas colaboradores e departamentos se destacam.
Usando o código abaixo vamos criar essas duas tabelas e encontrar o melhor jeito de juntar os dados das tabelas. Se não souber criar uma tabela veja os tutoriais anteriores.
CREATE TABLE departamentos (
departamento_id BIGINT AUTO_INCREMENT,
departamento VARCHAR(100),
cidade VARCHAR(100),
CONSTRAINT departamento_chave PRIMARY KEY (departamento_id),
CONSTRAINT depart_cidade_u UNIQUE (departamento, cidade)
);
CREATE TABLE colaboradores (
colaboradores_id BIGINT AUTO_INCREMENT,
nome VARCHAR(100),
sobrenome VARCHAR(100),
salario INTEGER,
departamento_id BIGINT REFERENCES departamentos (departamento_id),
CONSTRAINT colaboradores_chave PRIMARY KEY (colaboradores_id),
CONSTRAINT colabo_depart_unique UNIQUE (colaboradores_id, departamento_id)
);
INSERT INTO departamentos (departamento, cidade)
VALUES
('RH', 'São Paulo'),
('AD', 'Fortaleza');
INSERT INTO colaboradores (nome, sobrenome, salario, departamento_id)
VALUES
('Erique', 'Silva', 62500, 1),
('Fabiana', 'Oliveira', 59300, 1),
('Eveline', 'Reis', 83000, 2),
('Daniela', 'Ferreira', 95000, 2);
Podemos notar que as duas tabelas que acabamos de criar seguem o modelo proposto por Codd em que cada tabela descreve atributos de uma única entidade, nesse exemplo sobre os colaboradores e os departamentos. Fazendo uma consulta simples vemos que o conteúdo da tabela departamentos é esse:
SELECT * FROM departamentos;
+-----------------+--------------+-----------+
| departamento_id | departamento | cidade |
+-----------------+--------------+-----------+
| 2 | AD | Fortaleza |
| 1 | RH | São Paulo |
+-----------------+--------------+-----------+
2 rows in set (0.003 sec)
A coluna departamento_id é a chave primária da tabela, definimos isso na linha cinco. Isso quer dizer que os valores dessa tabela não podem ser repetidos e não devem ser nulos (deve ser adicionado algum valor). Para não precisarmos adicionar um valor para a coluna departamento_id adicionamos a cláusula AUTO_INCREMENT. Desse modo quando for adicionado uma nova linha a tabela, a coluna departamento_id será acrecida de um valor crescente. Adicionamos a restrição PRIMARY KEY porque cada departamento é único, não existe dois RH numa empresa. Nos próximos tutoriais vamos aprender mais sobre restrições (CONSTRAINT).
Se você realizar uma consulta simples na tabela de colaboradores o conteúdo deve ser esse:
SELECT * FROM colaboradores;
+------------------+----------+-----------+---------+-----------------+
| colaboradores_id | nome | sobrenome | salario | departamento_id |
+------------------+----------+-----------+---------+-----------------+
| 1 | Erique | Silva | 62500 | 1 |
| 2 | Fabiana | Oliveira | 59300 | 1 |
| 3 | Eveline | Reis | 83000 | 2 |
| 4 | Daniela | Ferreira | 95000 | 2 |
+------------------+----------+-----------+---------+-----------------+
8 rows in set (0.003 sec)
A coluna colaboradores_id é utilizada para identificar cada funcionário. Para saber em qual departamento um funcionário trabalha temos a colunas departamento_id. Os valores dessa coluna fazem referência aos valores da coluna departamento_id da tabela departamentos. Essa referência a outra coluna em outra tabela é chamada de chave estrangeira. Uma restrição com chave estrangeira usando referência (REFERENCES) só nos permite adicionar um valor se esse valor já estiver presente na coluna estrangeira, no nosso caso na coluna departamento_id. Isso quer disser que para adicionar o valor 1 na coluna departamento_id da tabela colaboradores esse valor já deve estar presente na coluna departamento_id da tabela departamentos.
Outra restrição é a UNIQUE. Utilizamos UNIQUE para definir que a tabela não pode ter valores repetidos. Agora você deve estar pensando que UNIQUE e PRIMARY KEY são a mesma coisa, mas isso não é verdade. Existem duas diferenças entre as duas. A primeira é que uma tabela só pode ter uma coluna PRIMARY KEY enquanto a mesma tabela pode ter várias colunas UNIQUEs. E a segunda diferença é que uma coluna UNIQUE aceita o valor nulo. Vamos aprender mais sobre essa restrição em outro tutorial.
Nesse momento você deve estar pensando pra que serve essas restrições, além de complicar a sua vida. E a resposta é bem simples: Não permitir a entrada de dados duplicados. Se cada departamento e cidade são únicos porque duplicar esses valores. Se tivéssemos recebidos os dados do jeito que queríamos inicialmente era isso que ia acontecer. Não tem problema ter dados duplicados numa tabela com quatro valores como a nossa, ou quatro mil. Mas agora imagine um banco de dados com milhares de linhas. Agora temos um problema: estamos desperdiçando espaço para armazenar valores duplicados.
Agora que você sabe o básico de como as tabelas podem se relacionar, vamos dar uma olhada em como juntá-los em uma consulta.
Consultando várias tabelas usando JOIN
Quando realizamos uma consulta com JOIN o banco de dados nos retorna uma tabela com as linhas das tabelas lado a lado. Isso para cada linha que a condição passada depois da cláusula ON seja verdadeira. Ainda podemos filtrar os resultados dessa consulta com a cláusula WHERE.
As consultas usando JOIN são parecidas com a sintaxe básica de uma consulta simples com SELECT. A diferença é que usamos JOIN … ON para selecionar as tabelas e as variações de JOIN para definir os critérios da junção.
Vamos começar com um exemplo simples. Para a nossa primeira consulta utilizando JOIN queremos os dados de todas as colunas onde os valores de departamento_id (da tabela colaboradores) e departamento_id (da tabela departamentos) sejam iguais.
SELECT *
FROM colaboradores JOIN departamentos
ON colaboradores.colaboradores_id = departamentos.departamento_id;
+------------------+---------+-----------+---------+-----------------+-----------------+--------------+-----------+
| colaboradores_id | nome | sobrenome | salario | departamento_id | departamento_id | departamento | cidade |
+------------------+---------+-----------+---------+-----------------+-----------------+--------------+-----------+
| 2 | Fabiana | Oliveira | 59300 | 1 | 2 | AD | Fortaleza |
| 1 | Erique | Silva | 62500 | 1 | 1 | RH | São Paulo |
+------------------+---------+-----------+---------+-----------------+-----------------+--------------+-----------+
2 rows in set (0.002 sec)
Nessa busca foi retornado apenas duas linhas, mesmo fazendo a consulta nas duas tabelas, isso acontece porque definimos uma condição para a busca. E a condição é o valor das colunas colaboradores_id das duas tabelas serem iguais.
É possível fazer uma consulta nas duas colunas sem definir uma condição. Desse modo as tabelas serão apresentadas uma do lado da outra. Para isso use a declaração SELECT * FROM tabela1 JOIN tabela2;.
Utilizando a cláusula ON ou não você vai perceber que a tabela com menos linhas vai se repeti até que as duas tabelas tenham o mesmo número.
JOIN
Utilizamos JOIN ou INNER JOIN quando queremos consultar duas ou mais tabelas que tenham uma tabela em comum. No exemplo anterior, as tabelas colaboradores e departamentos tinham a coluna colaboradores_id em comum. Para os nossos próximos exemplos vamos usar essa tabela:
CREATE TABLE escola_esquerda (id INT, escola_esquerda VARCHAR(50));
CREATE TABLE escola_direita (id INT, escola_direita VARCHAR(50));
INSERT INTO escola_esquerda (id, escola_esquerda)
VALUES
(1, 'COLEGIO CONTATO MACEIO'),
(2, 'COLEGIO HARMONIA'),
(3, 'ESC COLEGIO LATO SENSU'),
(4, 'COLEGIO OBJETIVO'),
(5, 'ESC AQUARELA ENSINO E CULTURA');
INSERT INTO escola_direita (id, escola_direita)
VALUES
(1, 'COLEGIO CONTATO MACEIO'),
(2, 'COLEGIO HARMONIA'),
(3, 'ESC COLEGIO LATO SENSU'),
(5, 'ESC AQUARELA ENSINO E CULTURA'),
(6, 'ESC COLEGIO VITORIA');
Veja um exemplo utilizando JOIN:
SELECT *
FROM escola_esquerda JOIN escola_direita
ON escola_esquerda.id = escola_direita.id;
+------+-------------------------------+------+-------------------------------+
| id | escola_esquerda | id | escola_direita |
+------+-------------------------------+------+-------------------------------+
| 1 | COLEGIO CONTATO MACEIO | 1 | COLEGIO CONTATO MACEIO |
| 2 | COLEGIO HARMONIA | 2 | COLEGIO HARMONIA |
| 3 | ESC COLEGIO LATO SENSU | 3 | ESC COLEGIO LATO SENSU |
| 5 | ESC AQUARELA ENSINO E CULTURA | 5 | ESC AQUARELA ENSINO E CULTURA |
+------+-------------------------------+------+-------------------------------+
4 rows in set (0.167 sec)
Você deve utilizar o JOIN quando os dados estiverem bem estruturados e uma relação entre as tabelas. Para retornar os valores das duas tabelas use outro tipo de JOIN.
LEFT JOIN e RIGHT JOIN
Utilizamos LEFT JOIN e RIGHT JOIN quando queremos que todos os elementos de uma tabela apareçam na consulta. Por exemplo, você quer que todos os elementos da tabela escola_esquerda apareçam na consulta, assim usamos LEFT JOIN. Os elementos da tabela escola_direita que não tiverem um correspondente na tabela escola_esquerda apareceram em branco (NULL). O mesmo vale para RIGHT JOIN. Veja um exemplo de utilização dos dois JOIN:
SELECT *
FROM escola_esquerda LEFT JOIN escola_direita
ON escola_esquerda.id = escola_direita.id;
+------+-------------------------------+------+-------------------------------+
| id | escola_esquerda | id | escola_direita |
+------+-------------------------------+------+-------------------------------+
| 1 | COLEGIO CONTATO MACEIO | 1 | COLEGIO CONTATO MACEIO |
| 2 | COLEGIO HARMONIA | 2 | COLEGIO HARMONIA |
| 3 | ESC COLEGIO LATO SENSU | 3 | ESC COLEGIO LATO SENSU |
| 5 | ESC AQUARELA ENSINO E CULTURA | 5 | ESC AQUARELA ENSINO E CULTURA |
| 4 | COLEGIO OBJETIVO | NULL | NULL |
+------+-------------------------------+------+-------------------------------+
5 rows in set (0.004 sec)
SELECT *
FROM escola_esquerda RIGHT JOIN escola_direita
ON escola_esquerda.id = escola_direita.id;
+------+-------------------------------+------+-------------------------------+
| id | escola_esquerda | id | escola_direita |
+------+-------------------------------+------+-------------------------------+
| 1 | COLEGIO CONTATO MACEIO | 1 | COLEGIO CONTATO MACEIO |
| 2 | COLEGIO HARMONIA | 2 | COLEGIO HARMONIA |
| 3 | ESC COLEGIO LATO SENSU | 3 | ESC COLEGIO LATO SENSU |
| 5 | ESC AQUARELA ENSINO E CULTURA | 5 | ESC AQUARELA ENSINO E CULTURA |
| NULL | NULL | 6 | ESC COLEGIO VITORIA |
+------+-------------------------------+------+-------------------------------+
5 rows in set (0.001 sec)
RIGHT JOIN e LEFT JOIN são usados quando você precisa que todos os elementos de uma tabela apareçam, quando você quer comparar duas tabelas procurando por dados ausentes e quando você precisa saber quais elementos de uma tabela não tem correspondentes na outra tabela.
Usando NULL para encontrar linhas com valores ausentes
Podemos descobrir quais são os valores ausentes numa consulta usando WHERE com NULL. Por exemplo, no exemplo acima a nossa consulta retorna um valor NULL, podemos usar esse valor para descobrir quais são as linhas que não têm correspondentes na outra tabela. Veja como fazer isso:
SELECT *
FROM escola_esquerda LEFT JOIN escola_direita
ON escola_esquerda.id = escola_direita.id
WHERE escola_direita.id IS NULL;
+------+------------------+------+----------------+
| id | escola_esquerda | id | escola_direita |
+------+------------------+------+----------------+
| 4 | COLEGIO OBJETIVO | NULL | NULL |
+------+------------------+------+----------------+
1 row in set (0.001 sec)
Com esse exemplo descobrimos que o valor 4 da coluna id da tabela escola_esquerda não tem um correspondente na tabela escola_direita.
Obs: Quando for trabalhar com o valor NULL use sempre IS porque NULL = NULL retorna NULL e NULL IS NULL retorna 1.
