Diferença entre a notação plus (+) do Oracle e a notação ansi JOIN?

Qual é a diferença entre o uso do oracle’s plus notation (+) sobre a notação de join padrão ansi?

Existe diferença no desempenho?

A notação de mais é descontinuada?

AFAIK, a notação (+) está presente apenas para compatibilidade com versões anteriores, porque a Oracle a lançou antes que o padrão ANSI para junções fosse colocado em prática. Ele é específico para a Oracle e você deve evitar usá-lo em um novo código quando houver uma versão equivalente compatível com os padrões disponíveis.

Edit: Parece que existem diferenças entre os dois, e a notação (+) tem restrições que a syntax de junit ANSI não possui. O próprio Oracle recomenda que você não use a notação (+) . Descrição completa aqui no Oracle® Database SQL Language Reference 11g Release 1 (11.1) :

A Oracle recomenda que você use a syntax OUTER JOIN cláusula FROM em vez do operador Oracle join. As consultas de junit externa que usam o operador de associação da Oracle (+) estão sujeitas às seguintes regras e restrições, que não se aplicam à cláusula FROM OUTER JOIN :

  • Você não pode especificar o operador (+) em um bloco de consulta que também contenha a syntax de junit de cláusula FROM .
  • O operador (+) pode aparecer apenas na cláusula WHERE ou, no contexto da correlação à esquerda (ao especificar a cláusula TABLE ) na cláusula FROM , e pode ser aplicado somente a uma coluna de uma tabela ou exibição.
  • Se A e B forem unidos por várias condições de junit, você deverá usar o operador (+) em todas essas condições. Se você não fizer isso, o Oracle Database retornará apenas as linhas resultantes de uma junit simples, mas sem um aviso ou erro para avisá-lo de que você não possui os resultados de uma junit externa.

  • O operador (+) não produz uma junit externa se você especificar uma tabela na consulta externa e a outra tabela em uma consulta interna.

  • Você não pode usar o operador (+) para unir externamente uma tabela a si mesmo, embora as auto-associações sejam válidas.

Por exemplo, a seguinte instrução não é válida:

 SELECT employee_id, manager_id FROM employees WHERE employees.manager_id(+) = employees.employee_id; 

No entanto, a união automática a seguir é válida:

 SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id; 
  • O operador (+) pode ser aplicado apenas a uma coluna, não a uma expressão arbitrária. No entanto, uma expressão arbitrária pode conter uma ou mais colunas marcadas com o operador (+) .

  • Uma condição WHERE que contém o operador (+) não pode ser combinada com outra condição usando o operador lógico OR .

  • Uma condição WHERE não pode usar a condição de comparação IN para comparar uma coluna marcada com o operador (+) com uma expressão.

Se a cláusula WHERE contiver uma condição que compara uma coluna da tabela B com uma constante, o operador (+) deve ser aplicado à coluna para que o Oracle retorne as linhas da tabela A para as quais gerou nulos para essa coluna. Caso contrário, o Oracle retornará apenas os resultados de uma junit simples.

Em uma consulta que executa junções externas de mais de dois pares de tabelas, uma única tabela pode ser a tabela gerada nula para apenas uma outra tabela. Por esse motivo, você não pode aplicar o operador (+) a colunas de B na condição de associação para A e B e a condição de associação para B e C. Consulte SELECT para obter a syntax de uma associação externa.

A notação ainda é suportada no Oracle 10 (e acredito 11). Seu uso é considerado “antiquado” e também não é tão portável quanto a syntax ANSI JOIN. Também é considerado muito menos legível, embora se você vier do + background se acostumar ao ANSI JOIN pode levar um pouco de tempo. O importante a saber antes de arremessar brickbats na Oracle é que eles desenvolveram sua syntax + antes que o comitê ANSI tivesse completado as definições para as junções.

Não há diferença de desempenho; eles estão expressando a mesma coisa.

Edit: Por “não tão portátil” eu deveria ter dito “apenas suportado no Oracle SQL”

Concordo com a resposta de Tony Miller e gostaria de acrescentar que também há algumas coisas que você NÃO pode fazer com a syntax (+):

  • Você não pode FULL OUTER JOIN duas tabelas, você tem que fazê-lo manualmente com um UNION ALL de duas junções,
  • Você não pode OUTER JOIN uma tabela para duas ou mais tabelas, você tem que criar manualmente uma subconsulta (ou seja: b.id = a.id (+) AND c.id = a.id (+) não é uma cláusula aceitável)

A resposta mais abrangente, obviamente, é a de nagul .

Uma adição para aqueles que estão procurando por tradução / mapeamento rápido para a syntax ANSI:

 -- -- INNER JOIN -- SELECT * FROM EMP e INNER JOIN DEPT d ON d.DEPTNO = e.DEPTNO; -- Synonym in deprecated oracle (+) syntax SELECT * FROM EMP e, DEPT d WHERE d.DEPTNO = e.DEPTNO; -- -- LEFT OUTER JOIN -- SELECT * FROM EMP e LEFT JOIN DEPT d ON d.DEPTNO = e.DEPTNO; -- Synonym in deprecated oracle (+) syntax SELECT * FROM EMP e, DEPT d WHERE d.DEPTNO (+) = e.DEPTNO; -- -- RIGHT OUTER JOIN -- SELECT * FROM EMP e RIGHT JOIN DEPT d ON d.DEPTNO = e.DEPTNO; -- Synonym in deprecated oracle (+) syntax SELECT * FROM EMP e, DEPT d WHERE d.DEPTNO = e.DEPTNO(+); -- -- CROSS JOIN -- SELECT * FROM EMP e CROSS JOIN DEPT d; -- Synonym in deprecated oracle (+) syntax SELECT * FROM EMP e, DEPT d; -- -- FULL JOIN -- SELECT * FROM EMP e FULL JOIN DEPT d ON d.DEPTNO = e.DEPTNO; -- Synonym in deprecated oracle (+) syntax !NOT WORKING! SELECT * FROM EMP e, DEPT d WHERE d.DEPTNO (+) = e.DEPTNO(+); 

A notação Oracle (+) é usada apenas no Oracle, que é específico do fornecedor . E, ANSI standared Join notation pode ser usado em qualquer RDBMS (como Sql Server, MySql etc.) . Caso contrário, não haverá diferença entre a notação Oracle (+) e a notação de Junção padrão ANSI.

Se você estiver usando o ANSI standared Join notation em sua consulta SQL, você pode usar a mesma consulta em qualquer RDBMS. E, se você estiver portando seu banco de dados da Oracle para qualquer outro RDBMS nessa condição, precisará usar a syntax ANSI .

Eu uso (+) notação, porque quase todas as consultas relacionadas ao Oracle Apps r12 são baseadas nisso. Eu não vi uma única consulta SQL com uma expressão “join” padrão em consultas Oracle APPS (mesmo aquelas fornecidas pelo próprio Oracle). Se você não acredita em mim, simplesmente pesquise qualquer informação relacionada aos aplicativos Oracle. Por exemplo: consultas relacionadas a ativos fixos

Uma das boas razões para usar a syntax ANSI sobre a antiga syntax de associação do Oracle é que há chances nulas de criar acidentalmente um produto cartesiano . Com mais número de tabelas, há uma chance de perder uma junit implícita com a syntax mais antiga do Oracle, no entanto, com a syntax ANSI, você não pode perder nenhuma junit, como você deve explicitamente mencioná-las.

Diferença entre a syntax de junit externa do Oracle e a Sintaxe ANSI / ISO .

JOGO EXTERIOR ESQUERDO –

 SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+); SELECT e.last_name, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id); 

DIREITO EXTERIOR –

 SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; SELECT e.last_name, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id); 

FULL OUTER JOIN –

Antes do suporte nativo do hash full outerjoin em 11gR1, a Oracle converteria internamente o FULL OUTER JOIN da seguinte maneira –

 SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id(+) UNION ALL SELECT NULL, d.department_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); SELECT e.last_name, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id); 

Dê uma olhada nisso .

  1. Use JOINs explícitos em vez de implícitos (independentemente de serem junções externas ou não) é que é muito mais fácil criar acidentalmente um produto cartesiano com as junções implícitas. Com JOINs explícitas, você não pode “por acidente” criar um. Quanto mais tabelas estiverem envolvidas, maior o risco de você perder uma condição de junit.
  2. Basicamente (+) é severamente limitado em comparação com junções ANSI. Além disso, só está disponível no Oracle, enquanto a syntax de junit ANSI é suportada por todos os principais DBMS
  3. O SQL não começará a funcionar melhor após a migration para a syntax ANSI – é apenas uma syntax diferente.
  4. A Oracle recomenda enfaticamente que você use a syntax de junit de cláusula FROM mais flexível mostrada no exemplo anterior. No passado, havia alguns bugs com syntax ANSI, mas se você for com o mais recente 11.2 ou 12.1, isso deve ser corrigido já.
  5. Usando os operadores JOIN, certifique-se de que seu código SQL seja compatível com ANSI e, assim, permita que um aplicativo front-end seja mais facilmente portado para outras plataformas de database.
  6. As condições de junit têm uma seletividade muito baixa em cada tabela e uma alta seletividade nas tuplas no produto teórico cruzado. As condições na declaração where geralmente têm uma seletividade muito maior.
  7. A Oracle converte internamente a syntax ANSI para a syntax (+), você pode ver isso acontecendo na seção Informações do Predicado do plano de execução.