SQL - Structured Query Language

Linguagem SQL

SQL (Structured Query Language) é uma linguagem de consulta estruturada, que é usada para manipular e recuperar dados de um banco de dados relacional. SQL é uma linguagem padrão para acessar e manipular bancos de dados.

Tem como base a álgebra relacional e o cálculo relacional.

O SQL é dividido em três partes principais:

DDL (Data Definition Language) - Linguagem de Definição de Dados
Define esquemas e tabelas, chaves primárias, chaves estrangeiras, exclusão de esquemas, tabelas e colunas, alteração de tabelas.
Diz respeito à estrutura das tabelas e esquemas no DB.

DML (Data Manipulation Language) - Linguagem de Manipulação de Dados
Consulta, inserção de dados no DB, exclusão de dados, alteração de dados. Diz respeito aos dados das tabelas do BD - CRUD ¹

DCL (Data Control Language) - Linguagem de Controle de Dados
Define permissões.

Principais comandos

DDL - Definição de dados: CREATE, DROP, ALTER.
DML - Manipulação de dados: SELECT, INSERT, UPDATE, DELETE.
DCL - Controle de dados: GRANT, REVOKE.

Conceitos

DDL - Data Definition Language

DDL - Data Definition Language (Linguagem de Definição de Dados) é usada para definir a estrutura que armazenará os dados. Define a estrutura das tabelas, índices, chaves primárias, chaves estrangeiras, etc.

Propriedades

A DDL permite não só a especificação de um conjunto de relações (tabelas), como também informações acerca de cada uma das relações, incluindo:

• O esquema de cada relação (estrutura)
• O domínio dos valores associados a cada atributo (int, float, varchar, etc)
• As regras de integridade de cada uma das relações
  • O conjunto de índice para manutenção de cada uma das relações
  • Informações sobre segurança e autoridade sobre cada relação
  • A estrutura de armazenamento físico de cada relação no disco.

Criar banco de dados/esquema

Antes de qualquer tabela, é necessário criar um database (banco de dados) ou um schema (esquema). Em SQL uma base de dados (ou esquema) é identificada atravez de um nome. Os elementos do esquema incluem tabelas, restrições, etc.

Sintaxe:

CREATE DATABASE nome_do_banco;

ou

CREATE SCHEMA nome_do_esquema;

Exemplos:

CREATE SCHEMA Empresa;
CREATE SCHEMA Universidade;
CREATE DATABASE Hospital;

Tipos de domínios

Numéricos

INTEGER: É um inteiro, originado da palavra integer (em inglês).

NUMERIC(p,d): É um número de ponto fixo cuja precisão é definida pelo usuário. O número consiste de $p$ dígitos (mais o sinal), sendo que $d$ dos $p$ dígitos estão à direita do ponto decimal.
Ex. NUMERIC(4,2): 42,00

SERIAL: Números inteiros auto incrementados.

Caracteres (Strings)

CHAR(n): É uma cadeia de caracteres de tamanho fixo, com o tamanho $n$ definido pelo DBA². Abreviação de character (em inglês).
Ex. CHAR(12): Jack Sparrow

VARCHAR(n): É uma cadeia de caracteres de tamanho variável, com o tamanho $n$ definido pelo DBA. Abreviação de character varying (em inglês).

Booleano

BOOLEAN: É um tipo de dado que pode ter um dos dois valores possíveis: TRUE ou FALSE (verdadeiro ou falso).

Data/Tempo

DATE: É um tipo de dado que contém um ano (com 4 dígitos) mês e dia do mês sendo o formato "aaaa/mm/dd" o padrão do MySQL Workbench.

• Year (date) - retorna o ano de uma data
• Month (date) - retorna o mês de uma data
• Day (date) - retorna o dia de uma data

TIME: Representa um horário, com o formato "hh:mm:ss" (00:00:00).

Observações

• Uma chave estrangeira deve possuir o mesmo típo de domínio da chave primária correspondente.
• O valor nulo NULL é um membro de todos os tipos de domínio, isto é, qualquer atributo pode receber o valor NULL exceto aqueles que são chaves primárias (restrição de integridade de entidade).
• O SQL permite que a declaração de domínio de qualquer atributo inclua a especificação de NOT NULL (não nulo), proibindo assim, a inserção de um valor nulo para esse tipo de atributo (obrigatório na PK).

Criar tabela

CREATE TABLE define a estrutura de uma tabela, suas restrições de integridade e cria uma tabela vazia.

Sintaxe:

CREATE TABLE nome_tabela (...);

Exemplos:

CREATE TABLE empregado (atributo1 tipo, atributo2 tipo, ...);

CREATE DATABASE EMPRESA;
 
CREATE TABLE DEPARTAMENTO (
  CodDep SERIAL NOT NULL,
  NomeDepVARCHAR(30),
  PRIMARY KEY (CodDep)
);
 
CREATE TABLE FUNCIONARIO (
  Matricula INTEGER NOT NULL,
  Nome VARCHAR(30) NOT NULL,
  Salario NUMERIC(8,2),
  Cargo VARCHAR(15) DEFAULT 'Analista',
  Estado CHAR(2),
  Idade INTEGER, CodDepto INT,
  PRIMARY KEY (Matricula),
  FOREIGN KEY (CodDepto) references DEPARTAMENTO (CodDep) ON DELETE
  NO ACTION ON UPDATE NO ACTION
);

Criação de um código que gere códigos automáticos não e padrão SQL, mas caso seja necessario, pode-se utilizar o SERIAL na criação do campo. Muito utilizado em relações que possuiem IDs.

Exemplo:

CREATE TABLE cidade (
  id_cidade SERIAL NOT NULL,
  nome_cidade VARCHAR(100) NOT NULL,
 
  PRIMARY KEY (id_cidade)
);

Remover tabela

DROP TABLE remove todos os dados e a própria tabela, estando vazia ou não.

Sintaxe:

DROP TABLE nome_tabela;

Exemplo:

DROP TABLE empregado;

Alterar tabela

ALTER TABLE usado para alterar o esquema da tabela, permite modificar a estrutura de uma tabela existente.

Sintaxe:

ALTER TABLE nome_da_tabela;

1. Sintaxe básica para inclusão de uma coluna:

ALTER TABLE nome_da_tabel ADD COLUMN nome_coluna tipo_atributo;

Ex.:

ALTER TABLE funcionario ADD COLUMN identidade VARCHAR(10);

2. Sintaxe básica para exclusão de uma coluna:

ALTER TABLE nome_da_tabela DROP nome_coluna;

Ex.:

ALTER TABLE funcionario DROP identidade;

3. Sintaxe básica para alteração do nome de uma coluna:

ALTER TABLE nome_da_tabela RENAME COLUMN nome_da_coluna_atual TO novo_nome_da_coluna;

Ex.:

ALTER TABLE empregado RENAME COLUMN sexo TO genero;

Atributos

Chave Primária PRIMARY KEY: É um atributo ou conjunto de atributos que identifica unicamente uma tupla em uma relação. A PK é um atributo ou conjunto de atributos que não pode ter valores repetidos.

Chave Estrangeira FOREIGN KEY: É um atributo ou conjunto de atributos que faz referência a uma chave primária ou única em outra tabela. A FK é um atributo ou conjunto de atributos que pode ter valores repetidos.
A FK pode ser declarada com algumas opções de ação para deleção (ON DELETE) e atualização (ON UPDATE) de registros:

• CASCADE
• SET NULL
• SET DEFAULT
• RESTRICT
• NO ACTION

Restrição de Atributos (PostgreSQL):

• NOT NULL - NN - O valor não pode ser nulo.
• DEFAULT <valor> - O valor padrão para o atributo caso não seja passado.
• UNIQUE - O valor não pode ser repetido, deve ser único.

Cláusulas da FK

ON DELETE

Cascata - CASCADE

Quando um registro é deletado da tabela referenciada, todos os registros que possuem a chave estrangeira referenciando o registro deletado também são deletados.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE CASCADE

Onde cpf_cliente é o atributo da tabela atual e Cliente(cpf) é a tabela e atributo referenciado.

Restrito - RESTRICT

Quando um registro é deletado da tabela referenciada, a operação é restringida (da erro), ou seja, não é permitido deletar o registro pai se houver outros registros filhos á referenciando.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE RESTRICT

Não faz nada - NO ACTION padrão - default

Quando um registro é deletado da tabela referenciada, um erro é exibido, e a operação de DELETE é revertida.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE NO ACTION

Define como nulo - SET NULL

Quando um registro é deletado da tabela referenciada, a chave estrangeira é definida como NULL.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE SET NULL

Valor Padrão - SET DEFAULT

Quando um registro é deletado da tabela referenciada, a chave estrangeira é definida como o valor padrão DEFAULT.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON DELETE SET DEFAULT

ON UPDATE

Restrito - RESTRICT

Quando um registro é atualizado na tabela referenciada, a operação é restringida (da erro), ou seja, não é permitido atualizar o registro pai se houver outros registros filhos á referenciando.
Sinatxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE RESTRICT

Define como nulo - SET NULL

Quando um registro é atualizado na tabela referenciada de modo que não exista mais a chave primária da tabela alterada, a chave estrangeira é definida como NULL.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE SET NULL

Define como padrão - SET DEFAULT

Quando um registro é atualizado na tabela referenciada de modo que não exista mais a chave primária da tabela alterada, a chave estrangeira é definida como o valor padrão DEFAULT.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE SET DEFAULT

Não faz nada - NO ACTION padrão - default

Quando um registro é atualizado na tabela referenciada, de modo que a chave primária referenciada não exista mais, um erro é exibido, e a operação de UPDATE é revertida.
Sintaxe:

FOREIGN KEY cpf_cliente REFERENCES Cliente(cpf) ON UPDATE NO ACTION

Constraits

Constraits são todas as restrições que uma coluna pode ter (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, etc).

Remover base de dados (DB)

DROP remove toda a base de dados, incluindo todas as tabelas, dados, índices, etc.

Sintaxe:

DROP DATABASE nome_do_banco;

Exemplo:

DROP DATABASE Empresa;
DROP DATABASE Hospital;
DROP DATABASE Universidade;

DML - Data Manipulation Language

DML - Data Manipulation Language (Linguagem de Manipulação de Dados) é usada para gerenciar os dados armazenados em um banco de dados. Manipula os dados de uma tabela, como inserir, atualizar, excluir e selecionar.

Propriedades

A linguagem DML é composta por 4 operações de manipulação de dados:

• Inserção de dados - INSERT
• Exclusão de dados - DELETE
• Atualização de dados - UPDATE
• Seleção de dados (consulta) - SELECT

Inserir Dados

INSERT INTO é usado para inserir novos registros em uma tabela.

Sintaxe:

INSERT INTO nome_tabela (coluna1, coluna2, ...) VALUES (valor1, valor2, ...);

Exemplo:

INSERT INTO empregado (nome, salario, cargo) VALUES ('João', 2000.00, 'Analista');

CREATE TABLE empregado (
  nome VARCHAR(30),
  salario NUMERIC(8,2),
  cargo VARCHAR(15)
);

INSERT INTO empregado VALUES ('João', 2000.00, 'Analista');

"Frodo Bolseiro"

'Frodo Bolseiro'

Excluir Dados

DELETE FROM é usado para excluir registros (tupla/linha) de uma tabela (relação).

Sintaxe:

DELETE FROM nome_tabela WHERE condicao;

Exemplo:

DELETE FROM peca WHERE cod_peca = 200;

Atualizar dados

UPDATE/SET é usado para atualizar registros existentes em uma tabela. Quando há mudança de endereço, nome, etc...

Sintaxe:

UPDATE nome_tabela SET coluna1 = valor1, coluna2 = valor2 WHERE condicao;

Exemplo:

UPDATE peca SET preco = 90.00 WHERE cod_peca = 200;

Selecionar Dados

SELECT é usado para selecionar dados de um banco de dados. A instrução SELECT é usada para recuperar registros de uma ou mais tabelas.

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela WHERE condicao;

Exemplo:

SELECT nome_peca, quantidade FROM peca WHERE preco > 50;

SELECT * FROM peca;

Cláusula WHERE (condição)

A cláusula WHERE é usada para filtrar registros. A cláusula WHERE é usada para extrair apenas os registros que atendem a uma condição específica.

Usa conectores lógicos:

• AND - E
• OR - OU
• NOT - NÃO

Usa operadores de comparação:

• > - Maior
• < - Menor
• = - Igual
• <= - Menor ou igual
• >= - Maior ou igual
• BETWEEN - Entre um intervalo (incluindo os extremos). Facilita a especificação de condições númericas que envolvam um intervalo, ao invés de usar os operadores <= e >=.

Exemplos

Iremos fazer algumas operações de busca SELECT usando cláusulas de condição/filtro WHERE na tabela abaixo:

EXEMPLO 1 - Selecionar o código e o nome das peças com o preço menor que $70.00$

SELECT cod_peca, nome_peca FROM peca WHERE preco < 70.00;

Resultado:

EXEMPLO 2 - Selecionar o nome e o preço das peças com preço maior que $50.00$ e menor do que $70.00$

SELECT nome_peca, preco WHERE preco BETWEEN 50.00 AND 70.00

Resultado:

EXEMPLO 3 - Selecionar todas as informações das peças cuja quantidade em estoque seja maior ou igual a $10$.

SELECT * FROM peca WHERE qtd >= 10;

Resultado:

EXEMPLO 4 - Selecionar o código, nome, preço e quantidade de peças no estoque cujo código é $200$.

SELECT cod_peca, nome_peca, preco, qtd FROM peca WHERE cod_peca = 200;

Resultado:

Cláusula ORDER BY (ordenação)

A cláusula ORDER BY é usada para ordenar o resultado de uma consulta em ordem crescente ou decrescente. Ela é aplicada somente à operações de consulta SELECT, após a cláusula WHERE.

Para especificar a forma de ordenação, devemos indicar

• ASC - Crescente padrão - default
• DESC - Decrescente

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela WHERE condicao ORDER BY coluna ASC|DESC;

Exemplo:

SELECT nome_peca, quantidade FROM peca ORDER BY nome_peca DESC;

Resultado:

Funções de agregação

As funções de agregação são usadas para calcular algo a partir de um conjunto de valores. As funções de agregação são usadas com a cláusula SELECT.

As principais são:

• COUNT - Conta o número de linhas (tuplas)
• SUM - Soma os valores da coluna - apenas em dados numéricos
• AVG - Calcula a média dos valores da coluna (average³) - apenas em dados numéricos
• MIN - Retorna o menor valor da coluna
• MAX - Retorna o maior valor da coluna

EXEMPLO 1 - Encontrar a soma dos preços de todas as peças, o maior preço, o menor preço e a média dos preços.

SELECT SUM(preco), MAX(preco), MIN(preco), AVG(preco) FROM peca;

Resultado:

EXEMPLO 2 - Contar o número de peças que há no estoque.

SELECT COUNT(*) FROM peca;

ou

SELECT COUNT(cod_peca) FROM peca;

Resultado:

Cláusula DISTINCT (linhas únicas)

Linhas duplicadas podem aparecer nas relações. No caso de desejarmos a eliminação de duplicidade, devemos inserir a palavra DISTINCT na cláusula SELECT.

Tabela neste momento:

SELECT * FROM peca;

Sinatxe:

SELECT DISTINCT coluna1, coluna2, ... FROM nome_tabela;

Exemplo:

Selecionar o nome de todas as peças, sem o DISTINCT:

SELECT nome_peca FROM peca;

Selecionar o nome de todas as peças, com o DISTINCT:

SELECT DISTINCT nome_peca FROM peca;

Cláusula GROUP BY (agrupar)

A cláusula GROUP BY é usada para agrupar linhas que possuem o mesmo valor em uma ou mais colunas. É normalmente usada em conjunto com funções de agregação para agrupar os resultados de acordo com um ou mais campos. Desta forma, as funções de agregação será aplicada a cada grupo, e não a todas as tuplas.

Tabela neste momento:

SELECT * FROM peca ORDER BY nome_peca;

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela GROUP BY coluna1, coluna2, ...;

EXEMPLO 1 - Selecionar o nome de todas as peças e agrupar por veículo (contar por grupo):

SELECT veiculo, COUNT(1) FROM peca GROUP BY veiculo;

Resultado:

EXEMPLO 2 - Obter a soma da quantidade de peças por tipo de veículo

SELECT veiculo, SUM(qtd) FROM peca GROUP BY veiculo;

Resultado:

Cláusula HAVING (filtro)

A cláusula HAVING é usada para filtrar grupos de registros que resultam de uma operação de GROUP BY. A cláusula HAVING é usada em conjunto com a cláusula GROUP BY.

Sintaxe:

SELECT coluna1, coluna2, ... FROM nome_tabela GROUP BY coluna1, coluna2, ... HAVING condicao;

EXEMPLO 2 anterior (alterado) - Obter a soma da quantidade de peças por tipo de veículo que sejam maiores que 20

SELECT veiculo, SUM(qtd) FROM peca GROUP BY veiculo HAVING SUM(qtd) > 20;

Resultado:

Seleção com Junção

As vezes queremos retornar dados de mais de uma tabela, relacionando os dados de uma tabela com os dados de outra.

Para fazer a junção das tabelas, precisamos definir uma condição de junção, na qual os atributos chave primária (primary key) e chave estrangeira (foreign key) das relações devem ser relacionados.

Tabelas de exemplo

Exemplo 1: Obter os nomes dos técnicos com experiência em secadora.

Query

SELECT nome FROM tecnicos, experiencia
WHERE num_tec = num_tecnico AND tipo = 'Secadora';

Resultado

| Nome  |
|-------|
| Hélio |
| Tião  |

Exemplo 2: Listar o nome dos técnicos e sua experiência em aparelhos da categoria 1

Query

SELECT tecnicos.nome, experiencia.anos_exp, tipos.tipo
FROM tecnicos, tipos, experiencia
WHERE
  tipos.tipo = experiencia.tipo
AND
  experiencia.num_tecnico = tecnicos.num_tec
AND
  tipos.categoria = 1

SELECT tecnicos.nome, experiencia.anos_exp, tipos.tipo
FROM tecnicos, tipos, experiencia
WHERE
  tipos.tipo = experiencia.tipo
AND
  experiencia.num_tecnico = tecnicos.num_tec
AND
  tipos.categoria = 1

Resultado

|  nome  | anos_exp |   tipo    |
|--------|----------|-----------|
| Hélio  |    15    | Secadora  |
| Tião	 |    18    | Lavadora  |
| Marco	 |    1     | Secadora  |
| Sílvio |    5     | Lavadora  |
| Tião	 |    12    | Secadora  |

Uso de aliases (apelido)

Alias são apelidos que podemos dar aos atributos na hora de retornar valores no SELECT, permite associar um "nome de variável" para cada relação, a fim de simplificar comandos SQL, e torna o retorno mais legível.

Para criarmos um alias podemos usar a palavra reservada AS. Criando um SELECT com alias temos:

Alias para tabelas

SELECT T.nome, E.anos_exp, TP.tipo
FROM
  tecnicos AS T,
  tipos AS TP,
  experiencia AS E
WHERE TP.tipo = E.tipo
  AND E.num_tecnico = T.num_tec
  AND TP.categoria = 1;

Alias para atributos

SELECT
  tecnicos.nome AS Tecnico,
  experiencia.anos_exp AS Experiencia,
  tipos.tipo AS Tipo
FROM tecnicos, tipos, experiencia
WHERE tipos.tipo = experiencia.tipo
  AND experiencia.num_tecnico = tecnicos.num_tec
  AND tipos.categoria = 1;

nome | anos_exp | tipo         Tecnico | Experiencia | Tipo
-----|----------|------        --------|-------------|------
 ... |   ...    |  ...           ...   |     ...     |  ...
 ... |   ...    |  ...    ->     ...   |     ...     |  ...
 ... |   ...    |  ...           ...   |     ...     |  ...
 ... |   ...    |  ...           ...   |     ...     |  ...

Resumo

Uma consulta em SQL pode consistir em até seis cláusulas:

SELECT [*] [DISTINCT] <lista de atributos> <funções de agregação>
FROM <lista de tabelas>
[WHERE <condição>]
[GROUP BY <lista de atributos para agrupamento>]
[HAVING <condição para agrupamento, aceita funções de agregação>]
[ORDER BY <lista de atributos para ordenação>] [ASC] [DESC]

OBSERVAÇÕES

• Apenas as cláusulas SELECT e FROM são obrigatórias
• Quando existentes, as cláusulas devem aparecer na ordem específica acima.
• O ORDER BY só pode ser ultilizado após o último SELECT (se a linguagem permitir)
• As cláusulas GROUP BY e HAVING só podem ser usadas nos comandos SELECT individuais

Valores NULL (nulo)

Suponhamos que temos a tabela Peça criada anteriormente, estruturada e preenchida da seguinte forma:

CREATE TABLE peca (
  cod_peca INTEGER NOT NULL,
  nome_peca VARCHAR(30) NOT NULL,
  preco NUMERIC(6,2),
  qtd INTEGER DEFAULT 0
);

INSERT INTO peca VALUES
  (1, 'Peça A', 15.00, 10),
  (2, 'Peça B', 8.00, 20),
  (3, 'Peça C', 8.00, 30),
  (4, 'Peça D', 8.00, 10);

Inserindo valores nulos

Quando realizamos um INSERT e não passamos o campo, o banco de dados vai automaticamente inserir NULL no valor da célula.

INSERT INTO peca (cod_peca, nome_peca, qtd) VALUES (5, 'Peça E', 15);

Resultado:

Lembre-se que colunas que tem o valor DEFAULT definido, não serão preenchidas com NULL, mas sim com o valor DEFAULT especificado no momento da criação da tabela.

INSERT INTO peca (cod_peca, nome_peca, preco) VALUES (6, 'Peça F', 20.00);

Resultado:

Existe um outra forma de definir um valor como NULL. Deixando explícito no comando INSERT que a coluna deve receber o valor NULL.

INSERT INTO peca (cod_peca, nome_peca, preco, qtd) VALUES (7, 'Peça G', 17.00, NULL);

Resultado:

Repare que mesmo o campo qtd possuindo um valor DEFAULT, foi definido de forma explícita no INSERT que essa coluna deveria possuir um valor NULL.

INSERT INTO peca (cod_peca, nome_peca, preco, qtd) VALUES (7, NULL, 17.00, 12);

Resultado:

ERROR:  null value in column "nome_peca" of relation "peca" violates not-null constraint
DETAIL:  Failing row contains (7, null, 17.00, 12).
SQL state: 23502

Selecionando valores nulos

Caso você queira selecionar somente as linhas com valores nulos em uma determinada célula, a forma correta é utilizar o IS NULL, e não ... = NULL.

SELECT * FROM peca WHERE preco IS NULL;

SELECT * FROM peca WHERE preco = NULL;

Selecionando valores não nulos

Caso você queira selecionar apenas as linhas que não possuem valores nulos em uma determinada coluna, é só utilizar o c omando IS NOT NULL.

SELECT * FROM peca WHERE preco IS NOT NULL;

Resultado:

Repare que a peça de código $5$ não foi incluída no resultado, por possuir o valor null na coluna preco.

Ordenando colunas com NULL

Por default, caso você ordene um SELECT por uma coluna que possui células com valor NULL, essas células serão as últimas a serem retornadas.

SELECT * FROM peca ORDER BY preco;

Resultado:

Caso você deseje que as células com valores NULL sejam as primeiras a serem retornadas no SELECT, utilizamos o ORDER BY ... NULLS FIRST.

SELECT * FROM peca ORDER BY preco NULLS FIRST;

Resultado:

Junções (Comando JOIN)

Junção é a possibilidade de se criar relacionamentos entre tabelas, de forma a poder recuperar dados de mais de uma tabela em uma única consulta. Um jeito melhor e nativo de se juntar mais de uma tabela, diferente do método apresentado no capítulo Seleção com Junção.

Isso é possível atravez do comando JOIN (junção), que é usado para combinar linhas de duas ou mais tabelas com base em uma relação entre elas, e recuperando esses dados usando apenas um SELECT.

É importante utilizá-lo, porque tira da cláusula WHERE condições que são estritamente das junções (chave primária igual a chave estrangeira, por exemplo).

Existem as variações de junções internas e externas.

Internas: INNER JOIN e NATURAL JOIN
Externas: OUTER JOIN (LEFT, RIGHT, FULL)

Como funciona o comando JOIN?

Quando um comando SELECT especifica campos de duas tabelas sem nenhumas restrição ou filtro, o resultado será um número de linhas iguais à multiplicação do total de linhas da primeira tabela ($N$) pela segunda tabela ($M$), ou seja $M\times N$.

Isso ocorre devido ao fato de que, para cada linha da primeira tabela, todas as linhas da segunda são processadas. Operações de junção toma duas relações, e têm como resultado uma outra relação.

Porém, o benefício do JOIN só será sentido quando as chaves $PK$ e $FK$ forem equivalentes, ou seja, $id = id\_curso$, usando a palavra reservada ON (será explicado mais a frente).

A operção de junção tem algumas variantes, e cada uma dessas variações consiste em um tipo de junção e uma condição de junção.

Sintaxe

SELECT coluna1, coluna2, ...
FROM tabela1 <tipo> JOIN tabela2
ON <condição>;

Exemplo

SELECT nome, nasc, experiencia
FROM tecnicos INNER JOIN experiencia
ON tecnicos.num_tec = experiencia.num_tecnico;

TIPO DE JUNÇÃO (INNER ou OUTER)
Define como as tuplas em cada relação que não possuam nenhuma conrrespondência com as tuplas da outra relação deve ser tratadas.

CONDIÇAO DE JUNÇAO (ON)
Definem quais tuplas das duas relações apresentam correspondência e quais atributos são apresentados de uma junção.

INNER JOIN

O INNER JOIN (Ou somente JOIN) é uma junção interna, que junta os registros de uma tabela que tiver um correspondente na outra tabela, através da chave primária e estrangeira.

Por exemplo, a tabela abaixo é resultado de um JOIN de 2 outras tabelas. A segunda tabela possui chaves estrangeiras apontando para valores nulos, e a primeira possui tuplas sem conrrespondência na segunda tabela. O resultado do INNER JOIN será apenas as linhas que possuem corrêspondência nas duas tabelas:

Sintaxe

SELECT coluna1, coluna2, ...
FROM (tabela1 INNER JOIN tabela2
      ON tabela1.chave_primaria = tabela2.chave_estrangeira);

SELECT coluna1, coluna2, ...
FROM (tabela1 JOIN tabela2
      ON tabela1.chave_primaria = tabela2.chave_estrangeira);

Usando o INNER JOIN com 2 tabelas

Exemplo: Liste o nome dos técnicos que possuem experiência em Lavadora

Tabelas

Query

SELECT nome
FROM (tecnicos INNER JOIN experiencia
      ON tecnicos.num_tec = experiencia.num_tecnico)
WHERE experiencia.tipo = 'Lavadora';

Resultado

Usando o INNER JOIN com 3 tabelas

Exemplo: Liste o nome dos técnicos e sua experiência em aparelhos da categoria 1

Tabelas

Query

SELECT nome, anos_exp
FROM ((tecnicos INNER JOIN experiencia
       ON num_tec = num_tecnico) INNER JOIN Tipos
       ON tipo = tipo)
WHERE categoria = 1;

Resultado

NATURAL JOIN

Com ele você não precisa identificar quais colunas serão comparadas, pois ele fará a comparação entre campos com o mesmo nome.

Sintaxe

SELECT coluna1, coluna2...
FROM tabela1 NATURAL JOIN tabela2;

Exemplo

SELECT * FROM (tecnicos NATURAL JOIN experiencia);

Repare que não precisamos passar a condição de junção com ON, já que ambas as tabelas possuem a coluna tipo.

O mesmo não da pra ser feito entre as tabelas tecnicos e tipos, pois não possuem colunas com o mesmo nome.

OUTER JOIN

O OUTER JOIN é a variação de junção externa, e é composto por 3 tipos:

• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

Diferente das junções internas, como o INNER JOIN, as junções externas podem retornar valores mesmo quando não há conrrespondência entre as tabelas (valores NULL).

LEFT OUTER JOIN

No LEFT OUTER JOIN (ou simplesmente LEFT JOIN), a prioridade é da tabela da esquerda, isto é, todos os registros da primeira tabela serão mostrados independente se houver correspondente nas outra tabela, após a equivalência das chaves usando o ON.

Sintaxe

SELECT coluna1, coluna2...
FROM tabela1 LEFT OUTER JOIN tabela2;

Exemplo

SELECT * FROM (tecnicos LEFT OUTER JOIN experiencia);

RIGHT OUTER JOIN

No RIGHT OUTER JOIN (ou simplesmente RIGHT JOIN), a prioridade é da tabela da direita, isto é, todos os registros da segunda tabela serão mostrados independente se houver correspondente na outra tabela.

Pega todos os atributos da relação que está à direita, verifica se existe algum correspondente à esquerda, caso afirmativo, retorna os atributos da esquerda, e caso negativo, coloca o valor nulo nos atributos.

Sintaxe

SELECT coluna1, coluna2...
FROM tabela1 RIGHT OUTER JOIN tabela2;

Exemplo

SELECT * FROM (tecnicos RIGHT OUTER JOIN experiencia);

FULL OUTER JOIN

Faz o RIGHT e o LEFT ao mesmo tempo, ou seja, vai retornar dados que não tem em comum nas duas tabelas, e não só na tabela da direita ou da esquerda.

Sintaxe

SELECT coluna1, coluna2...
FROM tabela1 FULL OUTER JOIN tabela2;

Exemplo

SELECT * FROM (tecnicos FULL OUTER JOIN experiencia);