sql - operator - join en oracle

¿Diferencia entre la notación más(+) de Oracle y la anotación? JOIN?

Question

¿Cuál es la diferencia entre usar la notación más de oráculo (+) sobre la notación de join estándar ansi?

¿Hay alguna diferencia en el rendimiento?

¿La notación plus está obsoleta?

Answer 1

1. Usar uniones explícitas en lugar de implícitas (independientemente de si son uniones externas o no) es que es mucho más fácil crear accidentalmente un producto cartesiano con las combinaciones implícitas. Con los JOINs explícitos no puedes crear "uno por accidente". Mientras más mesas estén involucradas, mayor es el riesgo de que pierda una condición de unión.
2. Básicamente (+) es muy limitado en comparación con las uniones ANSI. Además, solo está disponible en Oracle, mientras que la sintaxis de unión ANSI es compatible con todos los principales DBMS.
3. SQL no comenzará a funcionar mejor después de la migración a la sintaxis ANSI; es simplemente una sintaxis diferente.
4. Oracle recomienda encarecidamente que utilice la sintaxis de combinación de la cláusula FROM más flexible que se muestra en el ejemplo anterior. En el pasado, existían algunos errores con sintaxis ANSI, pero si vas con los últimos 11.2 o 12.1 que ya deberían estar corregidos.
5. El uso de los operadores JOIN garantiza que su código SQL sea compatible con ANSI y, por lo tanto, permitiría que una aplicación de front-end se transfiera más fácilmente a otras plataformas de bases de datos.
6. Las condiciones de unión tienen una selectividad muy baja en cada tabla y una alta selectividad en las tuplas en el producto cruzado teórico. Las condiciones en la declaración where generalmente tienen una selectividad mucho más alta.
7. Oracle convierte internamente la sintaxis ANSI a la sintaxis (+), puede ver que esto sucede en la sección Información del predicado del plan de ejecución.

Answer 2

AFAIK, la notación (+) solo está presente para la compatibilidad con versiones anteriores porque Oracle la presentó antes de que se estableciera el estándar ANSI para las uniones. Es específico de Oracle y debe evitar usarlo en un nuevo código cuando haya disponible una versión equivalente que cumpla con los estándares.

Editar: parece que hay diferencias entre los dos, y la notación (+) tiene restricciones que la sintaxis de unión ANSI no tiene. Oracle mismo recomienda que no use la notación (+) . Descripción completa aquí en Oracle® Database SQL Language Reference 11g Release 1 (11.1) :

Oracle recomienda que utilice la sintaxis OUTER JOIN cláusula FROM lugar del operador de unión de Oracle. Las consultas de unión externa que utilizan el operador de unión de Oracle (+) están sujetas a las siguientes reglas y restricciones, que no se aplican a la sintaxis OUTER JOIN cláusula FROM :

• No puede especificar el operador (+) en un bloque de consulta que también contiene la sintaxis de unión de la cláusula FROM .
• El operador (+) solo puede aparecer en la cláusula WHERE o, en el contexto de la correlación izquierda (cuando se especifica la cláusula TABLE ) en la cláusula FROM , y se puede aplicar solo a una columna de una tabla o vista.

• Si A y B se unen por múltiples condiciones de unión, entonces debes usar el operador (+) en todas estas condiciones. Si no lo hace, Oracle Database devolverá solo las filas resultantes de una unión simple, pero sin una advertencia o error para avisarle que no tiene los resultados de una combinación externa.

• El operador (+) no produce una combinación externa si especifica una tabla en la consulta externa y la otra tabla en una consulta interna.

• No puede usar el operador (+) para vincular externamente una tabla a sí mismo, aunque las autoinscripciones son válidas.

Por ejemplo, la siguiente declaración no es válida:

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

Sin embargo, la siguiente auto unión es válida:

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

• El operador (+) se puede aplicar a una columna, no a una expresión arbitraria. Sin embargo, una expresión arbitraria puede contener una o más columnas marcadas con el operador (+) .

• Una condición WHERE que contiene el operador (+) no se puede combinar con otra condición utilizando el operador lógico OR .

• Una condición WHERE no puede usar la condición de comparación IN para comparar una columna marcada con el operador (+) con una expresión.

Si la cláusula WHERE contiene una condición que compara una columna de la tabla B con una constante, entonces el operador (+) debe aplicarse a la columna para que Oracle devuelva las filas de la tabla A para la que ha generado nulos para esta columna. De lo contrario, Oracle solo devuelve los resultados de una unión simple.

En una consulta que realiza uniones externas de más de dos pares de tablas, una sola tabla puede ser la tabla generada nula solo para otra tabla. Por esta razón, no puede aplicar el operador (+) a las columnas de B en la condición de unión para A y B y la condición de unión para B y C. Consulte SELECT para la sintaxis de una unión externa.

Answer 3

Estoy de acuerdo con la respuesta de Tony Miller y me gustaría añadir que también hay algunas cosas que NO se pueden hacer con la sintaxis (+):

• No puedes completar FULL OUTER JOIN dos tablas, tienes que hacerlo manualmente con un UNION TODAS las dos uniones,
• No puede OUTER UNIRSE a una tabla de dos o más tablas, debe crear manualmente una subconsulta (es decir: b.id = a.id (+) AND c.id = a.id (+) no es una cláusula aceptable)

Answer 4

La notación aún se admite a partir de Oracle 10 (y creo que 11). Su uso se considera "pasado de moda", y tampoco es tan portable como la sintaxis ANSI JOIN. También se considera mucho menos legible, aunque si vienes del fondo + acostumbrarse a ANSI JOIN puede tomar un poco de tiempo. Lo importante de saber antes de arrojar ladrillos en Oracle es que desarrollaron su sintaxis + antes de que el comité de ANSI completara las definiciones de las uniones.

No hay diferencia de rendimiento; están expresando lo mismo.

Editar: por "no tan portátil" debería haber dicho "solo compatible con Oracle SQL"

Answer 5

La respuesta más completa obviamente es la de nagul .

Una adición para aquellos que buscan una traducción / mapeo rápido a la sintaxis de 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(+);

Answer 6

Una de las buenas razones para usar la sintaxis ANSI sobre la antigua sintaxis Oracle es que no hay posibilidades de crear accidentalmente un producto cartesiano . Con más tablas, existe la posibilidad de perder una unión implícita con la sintaxis de unión de Oracle anterior; sin embargo, con la sintaxis ANSI no se puede perder ninguna combinación ya que debe mencionarlas explícitamente .

Diferencia entre la sintaxis de unión externa de Oracle y la sintaxis ANSI / ISO .

IZQUIERDA COMBINACIÓN EXTERNA -

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

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

UNIÓN EXTERIOR COMPLETA -

Antes del soporte nativo de hash full outer join en 11gR1, Oracle convertiría internamente FULL OUTER JOIN de la siguiente manera:

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

Echa un vistazo a this .

Answer 7

Uso la notación (+) porque casi todas las consultas relacionadas con Oracle Apps r12 se basan en eso. No he visto una sola consulta SQL con una expresión de "combinación" estándar en consultas de Oracle APPS (incluso las proporcionadas por Oracle). Si no me crees, simplemente busca en google cualquier información relacionada con las aplicaciones de Oracle. Por ejemplo: consultas relacionadas con activos fijos

Answer 8

La notación Oracle (+) solo se usa en Oracle, que es específico del proveedor . Y, la notación de Unión unida por ANSI se puede usar en cualquier RDBMS (como Sql Server, MySql, etc.) . De lo contrario, no hay diferencia entre la notación de Oracle (+) y la notación de Unión unida a ANSI.

Si está utilizando la notación Unida estándar de ANSI en su consulta SQL, puede usar la misma consulta en cualquier RDBMS. Y, si está transfiriendo su base de datos de Oracle a cualquier otro RDBMS en esa condición, debe usar la sintaxis ANSI .