Consultas com Outer Join no Oracle

Uma das dúvidas mais freqüentes, especialmente para quem está começando a utilizar a linguagem SQL, refere-se à criação de consultas com o operador de outer join quando existem condições de seleção sobre as tabelas. Neste artigo, discutiremos questões relativas à sintaxe e ao modo de utilização do outer join. Faremos também uma comparação de algumas diferenças existentes entre o outer join definido na ANSI e no Oracle.

Nota: os exemplos em SQL ANSI foram testados no Oracle 9i e no MySQL 4.0. Já os exemplos que utilizam a sintaxe da Oracle foram testados no Oracle 8i e 9i. Se você utiliza outro banco de dados, verifique se ele suporta a sintaxe ANSI do operador outer join, já que a maioria dos bancos de dados atuais não implementa a especificação do SQL ANSI em sua totalidade. Por exemplo, a sintaxe ANSI para o operador outer join não é suportada no Oracle 8i.

Para demonstrar as operações exemplificadas neste artigo, analisaremos as tabelas DEPARTAMENTO, PESSOA e HOBBY, cujos dados são apresentados nas Tabelas 1, 2 e 3, respectivamente. Resumidamente, estas tabelas nos informam que uma pessoa pode estar alocada em um departamento e ter vários hobbies.

Tabela 1 - Estrutura e dados da tabela Departamento.

Tabela 2 - Estrutura e dados da tabela Pessoa.

Tabela 3 - Estrutura e dados da tabela Hobby.

Operação de Outer Join

A operação de outer join é usada em comandos SELECT do SQL para combinar linhas de duas ou mais tabelas, como, por exemplo, duas tabelas A e B. Para utilizar a operação de outer join, é necessário definir uma delas como principal (A) e a outra secundária (B). O resultado de outer join é o conjunto de todas as linhas da tabela principal A acrescidas das linhas de B, segundo a regra: caso exista uma ou mais linhas de B que atendam à condição de join, será feita a junção dessas linhas com a linha de A. Caso contrário, o operador juntará a linha de A com uma linha montada a partir das colunas de B preenchidas com nulos.

Para facilitar o entendimento, vamos exemplificar primeiro um inner join entre as tabelas PESSOA e HOBBY. A Listagem 1 apresenta a consulta que retorna apenas as pessoas que possuem algum hobby, bem como a descrição destes.

Listagem 1 - Inner join simples entre as tabelas Pessoa e Hobby.

select p.id, p.nome, h.nome_hobby

from pessoa p,

  hobby h

where p.id = h.id_pessoa;

Já para listarmos todas as pessoas no resultado, incluindo as que não têm hobby, devemos usar outer join no lugar do inner join. A sintaxe desta operação será diferente no SQL ANSI e no SQL da Oracle. A sintaxe para o ANSI é apresentada na Listagem 2.

Nota: a diferença entre outer join e inner join está no fato de que, na primeira, todas as linhas da tabela principal aparecem no resultado.

Listagem 2 - Sintaxe ANSI da outer join.

SELECT colunas

FROM tabela_primaria LEFT OUTER JOIN

  tabela_secundaria ON

  (tabela_primaria.coluna =

  tabela_secundaria.coluna)

A sintaxe de outer join da Oracle (ver Listagem 3) acrescenta o operador (+) na condição de join (cláusula where) ao lado do nome das colunas da tabela escolhida como secundária.

Listagem 3 - Sintaxe do outer join da Oracle.

SELECT colunas

FROM tabela_primaria, tabela_secundaria

WHERE (tabela_primaria.coluna =

   tabela_secundaria.coluna(+))

A Listagem 4 apresenta as consultas no formato ANSI e, em seguida, no formato Oracle. Estas fazem um outer join entre as tabelas PESSOA e HOBBY. Nestas consultas, a tabela PESSOA é definida como principal, uma vez que o resultado deverá apresentar todas as pessoas registradas, independentemente de elas terem algum hobby associado.

Listagem 4

Outer join entre as tabelas Pessoa e Hobby.

-- Sintaxe ANSI

select p.id, p.nome, h.nome_hobby

from pessoa p LEFT OUTER JOIN

  hobby h ON (h.id_pessoa = p.id);

-- Sintaxe Oracle

select p.id, p.nome, h.nome_hobby

from pessoa p,

  hobby h

where p.id = h.id_pessoa (+);

Escrevendo condições de seleção em consultas com Outer Join

É possível escrever condições de seleção nas colunas de tabelas definidas como secundárias em uma consulta de outer join. Essas condições podem ser especificadas de forma que o filtro seja imposto: (1) sobre as linhas da tabela secundária (antes da operação de outer join) ou (2) sobre o conjun to de linhas resultante da operação de outer join (após o outer join). Os exemplos a seguir demonstram as duas abordagens.

As consultas descritas na Listagem 5 relacionam todos os departamentos, apresentando as pessoas alocadas a cada um deles. Neste caso, a tabela DEPARTAMENTO foi definida como principal, e a tabela PESSOA, como secundária.

Listagem 5 - Outer join entre as tabelas Departamento e Pessoa.

-- Sintaxe ANSI

select d.cod_depto, d.nome_depto, p.id, p.nome

from departamento d LEFT OUTER JOIN pessoa p on

  ( d.cod_depto = p.cod_depto);

-- Sintaxe Oracle

select d.cod_depto, d.nome_depto, p.id, p.nome

from    departamento d, pessoa p

where  d.cod_depto = p.cod_depto (+);

Agora vamos impor uma condição sobre as linhas da tabela PESSOA. Faremos uma consulta que apresente todos os departamentos que possuam pelo menos uma pessoa de determinada UF. Para isso, incluímos a condição p.uf = ‘DF’ na cláusula where das consultas da Listagem 5. A Listagem 6 mostra as consultas alteradas e seu resultado.

Listagem 6 - Outer join entre as tabelas departamento e pessoa com condicional no where.

-- Sintaxe ANSI

select d.cod_depto, d.nome_depto, p.id, p.nome, p.uf

from departamento d LEFT OUTER JOIN pessoa p on

  (d.cod_depto = p.cod_depto)

where p.uf = ‘DF’;

-- Sintaxe Oracle

select d.cod_depto, d.nome_depto, p.id, p.nome, p.uf

from   departamento d, pessoa p

where d.cod_depto = p.cod_depto (+)

 and p.uf = ‘DF’;

Da maneira como foi escrita, a condição filtra as linhas do conjunto resultante do outer join. Para impor um filtro sobre as linhas da tabela antes da operação de outer join, é preciso alterar o comando SQL. Na forma ANSI, para que uma condição seja imposta antes da operação de outer join, é preciso defini-la na cláusula ON (ver Listagem 7). Já na sintaxe do Oracle é necessário sinalizar com o operador (+) de outer join ao lado do nome da coluna usada na condição de seleção (ver Listagem 7).

Listagem 7 - Outer join entre as tabelas Departamento e Pessoa com condicional no outer join.

-- Sintaxe ANSI

select d.cod_depto, d.nome_depto, p.id, p.nome, p.uf

from departamento d left outer join pessoa p on

  (d.cod_depto = p.cod_depto and p.uf = ‘DF’);

-- Sintaxe Oracle

select d.cod_depto, d.nome_depto, p.id, p.nome, p.uf

from    departamento d, pessoa p

where d.cod_depto = p.cod_depto (+)

and     p.uf(+) = ‘DF’;

Na Listagem 7 todos os departamentos foram apresentados e apenas as pessoas do DF foram consideradas. Da forma como foram escritas, as condições impõem um filtro sobre a tabela PESSOA e selecionam apenas as linhas que poderão participar da operação de outer join.

Otimizando a execução do NOT IN com outer join

A possibilidade de filtrar linhas da tabela secundária ou linhas do conjunto resultante da operação de outer join nos permite realizar operações complexas de modo eficiente. Veremos isto na prática comparando os resultados de uma consulta com NOT IN e uma busca que retorne os mesmos resultados utilizando, porém, o outer join. Para isto, vejamos a seguinte consulta: Que pessoas não têm futebol como hobby? A Listagem 8 apresenta a versão sem a utilização do outer join.

Listagem 8 - Uso do not in para listar pessoas que não têm Futebol como Hobby.

select p.id, p.nome

from    pessoa p

where  p.id not in

(select id_pessoa

from    hobby

where  nome_hobby = ‘Futebol’);

No entanto, é necessário ter cuidado, pois essa construção requer a leitura de todas as linhas da tabela das queries principal (PESSOA) e secundária (HOBBY), o que pode fazer com que a consulta fique lenta se as tabelas tiverem um volume grande de dados. Uma forma mais eficiente de fazer essa pesquisa é utilizar um outer join com a condicional no outer join e filtrar as colunas resultantes de valor null (ver Listagem 9). Note que as consultas das Listagens 8 e 9 mostram o mesmo resultado exibido na Listagem 10.

Listagem 9 - Uso do outer join para listar pessoas que não têm Futebol como Hobby.

-- Sintaxe ANSI

select p.id, p.nome

from      pessoa p left outer join hobby h on

(p.id = h.id_pessoa and h.nome_hobby = ‘Futebol’)

where    h.id_pessoa is null;

-- Sintaxe Oracle

select p.id, p.nome

from  pessoa p,

hobby h

where   p.id = h.id_pessoa (+) and

h.nome_hobby (+) = ‘Futebol’ and

h.id_pessoa is null;

Listagem 10 - Resultado dos comandos das listagens 8 e 9.

Para esclarecer por que as consultas da Listagem 9 fornecem os mesmos resultados da Listagem 8, vamos examinar um dos passos intermediários de sua construção, retirando temporariamente a condição de filtragem do campo null (ver Listagem 11).

Listagem 11 - Consultas e resultado da execução sem o “filtro null”.

-- Sintaxe ANSI

select p.id, p.nome, h.id_pessoa, h.nome_hobby

from   pessoa p left outer join hobby h on

(p.id = h.id_pessoa and h.nome_hobby = ‘Futebol’);

-- Sintaxe Oracle

select p.id, p.nome, h.id_pessoa, h.nome_hobby

from      pessoa p,

hobby h

where   p.id = h.id_pessoa (+) and

h.nome_hobby (+) = ‘Futebol’;

Observe na Listagem 11 que as condições on (p.id=h.id_pessoa and h.nome_hobby=’Futebol’), no SQL ANSI, e h.nome_hobby (+) = ‘Futebol’, no Oracle, filtram as linhas da tabela hobby antes da operação de outer join ser executada. Isto permite que apenas as linhas que contêm o hobby ‘Futebol’ participem do join. Nessa operação, poderão ser retornados os valores correspondentes para as colunas ID_PESSOA e NOME_HOBBY como valores nulos (caso a pessoa não tenha futebol como hobby). Então, para eliminar do conjunto resultante do outer join apenas aqueles que gostem de futebol, é utilizada a condição h.id_pessoa is null. Assim, as consultas da Listagem 9 implementam, através do outer join, a mesma lógica correspondente a um operador NOT IN, porém com um desempenho melhor, já que não precisam acessar todas as linhas das tabelas principal e secundária. A Listagem 12 apresenta o plano de execução das duas abordagens e mostra com mais clareza o ganho de desempenho.

Listagem 12 - Plano de execução das consultas com NOT IN e outer join.

--Consulta com NOT IN. Acesso full nas duas tabelas.

--Consulta com outer join. Acesso com índice no final do processo.

Conclusão

Este artigo apresentou, por meio de exemplos simplificados, a sintaxe e o modo de utilização da operação de outer join no ANSI e no Oracle. Mostrou também a utilidade de escrever condições de seleção associadas a colunas de tabelas secundárias de um outer join, e como fazê-lo. Por fim, apresentou uma solução eficiente para implementar o operador NOT IN através de um outer join. Em sistemas reais, onde é mais comum lidarmos com consultas complexas, poderemos nos deparar com situações em que essas técnicas serão bastante úteis.

José Eduardo Thurler Tecles (tecles@formatool.com.br) é formado em Engenharia Eletrônica pelo Instituto Militar de Engenharia - IME, RJ, em 1976 e fez mestrado na Universidade de Maryland, EUA, em 1980. Atua há anos como analista de sistemas e é consultor da Formato Projetos e Desenvolvimento de Sistemas, especializado no desenvolvimento de sistemas corporativos e Data Marts/Data Warehouse. É também instrutor homologado pela Oracle; já ministrou cursos de SQL, PL/SQL, projeto de Data Marts, modelagem multidimensional e projeto de banco de dados para Data Warehousing / Business Intelligence.

Anne Margareth Souza Guedes (anne.margareth@formatool.com.br) é formada em Ciência da Computação pelo Centro de Ensino Unificado de Brasília - CEUB, DF, em 1993. Atua como DBA há 9 anos e é consultora da Formato Projetos e Desenvolvimento de Sistemas. Obteve sua certificação Oracle em 1999 e possui agora as certificações Oracle Certified Professional (OCP) DBA 9i, 8i e 8.0.