Differenza tra notazione + (+) di Oracle e notazione JOIN ansi?

Qual è la differenza tra l’uso della notazione di oracle plus (+) rispetto alla notazione di join standard ansi?

C’è una differenza nelle prestazioni?

La notazione positiva è deprecata?

AFAIK, la notazione (+) è presente solo per la retrocompatibilità perché Oracle ha debuttato prima che lo standard ANSI per i join fosse messo in atto. È specifico di Oracle e dovresti evitare di usarlo nel nuovo codice quando è disponibile una versione conforms agli standard equivalente.

Modifica: Sembra che ci siano differenze tra i due, e la notazione (+) ha restrizioni che la syntax di ANSI ANSI non ha. Lo stesso Oracle consiglia di non usare la notazione (+) . Descrizione completa qui in Oracle® Database SQL Language Reference 11g Release 1 (11.1) :

Oracle consiglia di utilizzare la syntax OUTER JOIN clausola FROM anziché l’operatore Oracle join. Le query di join esterno che utilizzano l’operatore di join Oracle (+) sono soggette alle seguenti regole e restrizioni, che non si applicano alla syntax OUTER JOIN clausola FROM :

  • Non è ansible specificare l’operatore (+) in un blocco di query che contiene anche la syntax di join della clausola FROM .
  • L’operatore (+) può apparire solo nella clausola WHERE o, nel contesto della correlazione a sinistra (quando si specifica la clausola TABLE ) nella clausola FROM , e può essere applicato solo a una colonna di una tabella o vista.
  • Se A e B sono uniti da più condizioni di join, è necessario utilizzare l’operatore (+) in tutte queste condizioni. In caso contrario, Oracle Database restituirà solo le righe risultanti da un join semplice, ma senza un avviso o un errore per avvisare che non si hanno i risultati di un join esterno.

  • L’operatore (+) non produce un join esterno se si specifica una tabella nella query esterna e l’altra tabella in una query interna.

  • Non è ansible utilizzare l’operatore (+) per unire l’esterno di una tabella a se stesso, sebbene gli auto join siano validi.

Ad esempio, la seguente dichiarazione non è valida:

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

Tuttavia, il seguente auto join è valido:

 SELECT e1.employee_id, e1.manager_id, e2.employee_id FROM employees e1, employees e2 WHERE e1.manager_id(+) = e2.employee_id; 
  • L’operatore (+) può essere applicato solo a una colonna, non a un’espressione arbitraria. Tuttavia, un’espressione arbitraria può contenere una o più colonne contrassegnate con l’operatore (+) .

  • Una condizione WHERE contenente l’operatore (+) non può essere combinata con un’altra condizione utilizzando l’operatore logico OR .

  • Una condizione WHERE non può utilizzare la condizione di confronto IN per confrontare una colonna contrassegnata con l’operatore (+) con un’espressione.

Se la clausola WHERE contiene una condizione che confronta una colonna della tabella B con una costante, l’operatore (+) deve essere applicato alla colonna in modo che Oracle restituisca le righe dalla tabella A per cui ha generato valori nulli per questa colonna. In caso contrario, Oracle restituisce solo i risultati di un join semplice.

In una query che esegue join esterni di più di due coppie di tabelle, una singola tabella può essere la tabella generata da null solo per un’altra tabella. Per questo motivo, non è ansible applicare l’operatore (+) alle colonne di B nella condizione di join per A e B e la condizione di join per B e C. Fare riferimento a SELECT per la syntax per un join esterno.

La notazione è ancora supportata a partire da Oracle 10 (e credo 11). Il suo uso è considerato “vecchio stile”, e inoltre non è un database portatile come la syntax ANSI JOIN. È anche considerato molto meno leggibile, sebbene se si provenga dallo sfondo + che si sta abituando ad ANSI JOIN può richiedere un po ‘di tempo. La cosa importante da sapere prima di lanciare brickbats su Oracle è che hanno sviluppato la loro syntax + prima che il comitato ANSI avesse completato le definizioni per i join.

Non c’è differenza di prestazioni; stanno esprimendo la stessa cosa.

Modifica: per “non come portatile” avrei dovuto dire “supportato solo in Oracle SQL”

Sono d’accordo con la risposta di Tony Miller e vorrei aggiungere che ci sono anche alcune cose che NON puoi fare con la syntax (+):

  • Non puoi FULL OUTER JOIN in due tabelle, devi farlo manualmente con UNION ALL di due join,
  • Non puoi OUTER JOIN in una tabella a due o più tabelle, devi creare manualmente una sottoquery (es .: b.id = a.id (+) AND c.id = a.id (+) non è una clausola accettabile)

La risposta più completa è ovviamente quella di Nagul .

Un’aggiunta per coloro che cercano una traduzione / mapping rapida alla 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(+); 

La notazione Oracle (+) viene utilizzata solo in Oracle, che è specifica del fornitore . Inoltre, la notazione Join può essere utilizzata in qualsiasi RDBMS (come Sql Server, MySql ecc . ) . In caso contrario, non vi è alcuna differenza tra la notazione Oracle (+) e la norma ANSI Join notation.

Se si utilizza lo standard ANSI Join notation nella query Sql, è ansible utilizzare la stessa query in qualsiasi RDBMS. Inoltre, se si esegue il porting del database da Oracle a qualsiasi altro RDBMS in tale condizione, è necessario utilizzare ANSI Syntax .

Uso la notazione (+), perché quasi tutte le query relative a Oracle Apps r12 si basano su questo. Non ho visto una singola query SQL con un’espressione “join” standard nelle query APPS Oracle (anche quelle fornite da Oracle stesso). Se non mi credi, semplicemente google qualsiasi informazione relativa alle app Oracle. Ad esempio: query relative alle risorse fisse

Uno dei buoni motivi per utilizzare la syntax ANSI rispetto alla vecchia syntax di Oracle join è che ci sono zero possibilità di creare accidentalmente un prodotto cartesiano . Con un numero maggiore di tabelle, è ansible perdere un’unione implicita con la syntax di join Oracle più vecchia, tuttavia, con la syntax ANSI non è ansible mancare alcun join in quanto è necessario menzionarli esplicitamente .

Differenza tra syntax di Oracle outer join e ANSI / ISO Syntax .

LEFT OUTER JOIN –

 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); 

GIUSTO ESTERNO –

 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 –

Prima del supporto nativo di hash full outerjoin in 11gR1, Oracle convertiva internamente il FULL OUTER JOIN nel seguente modo:

 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); 

Dai un’occhiata a questo .

  1. Utilizzare JOIN espliciti piuttosto che impliciti (indipendentemente dal fatto che siano join esterni o meno) è che è molto più facile creare accidentalmente un prodotto cartesiano con i join impliciti. Con JOIN espliciti non puoi “per caso” crearne uno. Più sono coinvolte le tabelle, maggiore è il rischio di perdere una condizione di join.
  2. Fondamentalmente (+) è severamente limitato rispetto ai join ANSI. Inoltre è disponibile solo in Oracle, mentre la syntax di join ANSI è supportata da tutti i principali DBMS
  3. SQL non inizierà a funzionare meglio dopo la migrazione alla syntax ANSI – è solo una syntax diversa.
  4. Oracle consiglia vivamente di utilizzare la syntax di join della clausola FROM più flessibile mostrata nell’esempio precedente. In passato c’erano alcuni bug con la syntax ANSI ma se si va con l’ultima versione 11.2 o 12.1 dovrebbe essere già risolta.
  5. L’utilizzo degli operatori JOIN garantisce che il codice SQL sia conforms ANSI e consentirebbe quindi a un’applicazione front-end di essere portata più facilmente su altre piattaforms di database.
  6. Le condizioni di unione hanno una selettività molto bassa su ciascun tavolo e un’elevata selettività sulle tuple nel prodotto trasversale teorico. Le condizioni nell’istruzione where solitamente hanno una selettività molto più alta.
  7. Oracle converte internamente la syntax ANSI nella syntax (+), è ansible vedere ciò accade nella sezione Informazioni Predicato del piano di esecuzione.