- Preparar
- Introducción
- [INNER] JOIN … ON
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
- FULL [OUTER] JOIN
- CROSS JOIN
- NATURAL JOIN
- [INNER] JOIN … USING
- Joins Adicionales
Preparar
Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.
Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.
--DROP TABLE employees PURGE; --DROP TABLE departments PURGE; CREATE TABLE departments ( department_id NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY, department_name VARCHAR2(14), location VARCHAR2(13) ); INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK'); INSERT INTO departments VALUES (20,'RESEARCH','DALLAS'); INSERT INTO departments VALUES (30,'SALES','CHICAGO'); INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON'); COMMIT; CREATE TABLE employees ( employee_id NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY, employee_name VARCHAR2(10), job VARCHAR2(9), manager_id NUMBER(4), hiredate DATE, salary NUMBER(7,2), commission NUMBER(7,2), department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id) ); INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20); INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30); INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30); INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20); INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30); INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30); INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10); INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20); INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10); INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30); INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20); INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30); INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20); INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10); COMMIT;
Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
Introducción
Las uniones se utilizan para combinar datos de varias tablas para formar un único conjunto de resultados. Oracle proporciona dos enfoques para unir tablas, la sintaxis de unión no ANSI y la sintaxis de unión ANSI, que se ven bastante diferentes.
Históricamente, la sintaxis de Join no ANSI ha sido la forma en que realiza las combinaciones en Oracle y sigue siendo muy popular hoy en día. Las tablas que se unirán se enumeran en la cláusula FROM y las condiciones de unión se definen como predicados en la cláusula WHERE. Incluso si no te gusta, tendrás que acostumbrarte, ya que hay mucho código que todavía lo usa. Si no está familiarizado con la sintaxis, tendrá dificultades para corregir errores en cualquier código existente y algunos de los ejemplos en Internet le parecerán bastante misteriosos.
La sintaxis de unión ANSI se introdujo en Oracle 9i. Tiene una serie de ventajas sobre la sintaxis original.
Se parece más al inglés, por lo que es mucho más claro.
Las tablas y las condiciones de Join se mantienen todas juntas en la cláusula FROM, por lo que la cláusula WHERE solo contiene filtros, no condiciones de Join.
La sintaxis hace que sea difícil, si no imposible, olvidar incluir la condición de Join.
Los filtros en columnas de tablas unidas externas se manejan de una manera mucho más clara.
Es más portátil y es compatible con varios motores de bases de datos relacionales.
Proporciona algunas funciones que no son compatibles directamente con la sintaxis de Join que no es ANSI, sin usar un esfuerzo significativamente mayor.
A pesar de todas estas ventajas, muchos desarrolladores de Oracle todavía utilizan la sintaxis de unión no ANSI. En parte, esto se debe solo a la costumbre. En parte, esto se debe a que el optimizador de Oracle transforma la mayoría de la sintaxis de Join ANSI en la sintaxis de Join no ANSI equivalente antes de que se ejecute.
Para un principiante, mi opinión personal es que debe centrarse en la sintaxis de unión ANSI, pero tenga en cuenta el equivalente no ANSI. En este artículo, mostraré la sintaxis ANSI y no ANSI para cada ejemplo, cuando sea relevante.
Algunos métodos de unión son más populares que otros, así que inicialmente concentre su atención en aquellos que es más probable que vea. Las combinaciones más comunes que probablemente verá en el código son las siguientes.
- [INNER] JOIN … ON
- LEFT [OUTER] JOIN
- RIGHT [OUTER] JOIN
Los siguientes son menos comunes.
- FULL [OUTER] JOIN
- CROSS JOIN
- NATURAL JOIN
- [INNER] JOIN … USING
Los siguientes son muy raros en este momento y solo se utilizan a partir de la versión de oracle 12c.
- CROSS APPLY
- OUTER APPLY
Si una palabra está rodeada por «[]», significa que es una palabra clave opcional. Sin ningún otro calificador, una Join es una inner Join, por lo que usar la palabra clave INNER es redundante. Si la Join incluye las palabras LEFT, RIGHT o FULL, por definición es un outer Join, por lo que la palabra clave OUTER es redundante. La elección de incluir o excluir estas palabras es una preferencia personal, así que siga el estándar de su empresa o haga lo que le parezca correcto.
Con todo eso en mente, echemos un vistazo a algunos ejemplos.
[INNER] JOIN … ON
Un INNER JOIN combina datos de dos tablas donde hay una coincidencia en la (s) columna (s) de unión en ambas tablas.
Recuerde, la palabra clave INNER es opcional. En los siguientes ejemplos, devolvemos DEPARTMENT_NAME y EMPLOYEE_NAME para cada empleado. El departamento de OPERACIONES tiene un DEPARTMENT_ID de 40, por lo que no se elimina por la condición de filtro, pero no hay empleados en este departamento, por lo que no hay coincidencia y no se devuelve en el conjunto de resultados.
Aquí hay un ejemplo de ANSI INNER JOIN.
SELECT d.department_name, e.employee_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE d.department_id >= 30 ORDER BY d.department_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD 6 rows selected. SQL>
Aquí está el equivalente no ANSI de la declaración anterior.
SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id AND d.department_id >= 30 ORDER BY d.department_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD 6 rows selected. SQL>
LEFT [OUTER] JOIN
A LEFT [OUTER] JOIN devuelve todas las filas válidas de la tabla en el lado izquierdo de la palabra clave JOIN, junto con los valores de la tabla en el lado derecho, o NULL si no existe una fila coincidente.
Usando el ejemplo anterior, pero cambiando a LEFT OUTER JOIN significa que veremos el departamento de OPERACIONES, aunque no tenga empleados.
Aquí hay un ejemplo de un ANSI LEFT OUTER JOIN.
SELECT d.department_name, e.employee_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id WHERE d.department_id >= 30 ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- OPERATIONS SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD 7 rows selected. SQL>
Aquí está el equivalente no ANSI de la declaración anterior. Observe que el «(+)» se utiliza para indicar el lado de la condición de unión que puede faltar. Para una condición de Join de varias columnas, cada columna debe tener el «(+)» presente. A diferencia de la sintaxis de Join ANSI, la sintaxis de Join no ANSI no se ve afectada por el orden de las tablas.
SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id (+) AND d.department_id >= 30 ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- OPERATIONS SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD 7 rows selected. SQL>
Agregar filtros a las columnas devueltas desde una tabla outer join es una causa común de confusión. Si prueba un valor específico, por ejemplo, «salario> = 2000», pero el valor de la columna SALARIO es NULO porque falta la fila, una condición regular en la cláusula WHERE descartará la fila y, por lo tanto, anulará el objeto de haciendo una Join externa. Tanto los métodos ANSI como los no ANSI tienen una forma de lidiar con esto.
Con la sintaxis de Join ANSI, los filtros de las columnas de la tabla outer join se incluyen en la Join en sí, en lugar de colocarse en la cláusula WHERE.
SELECT d.department_name, e.employee_name FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id AND e.salary >= 2000 WHERE d.department_id >= 30 ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- OPERATIONS SALES BLAKE 2 rows selected. SQL>
Utilizando la sintaxis de Join no ANSI, el «(+)» se usa para indicar que una columna puede tener un valor NULL como resultado de un outer join.
Cabe destacar que para las dos opciones ANSI y no ANSI las sentencias funcionan de igual manera.
SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id (+) AND e.salary (+) >= 2000 AND d.department_id >= 30 ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- OPERATIONS SALES BLAKE 2 rows selected. SQL>
RIGHT [OUTER] JOIN
El RIGHT [OUTER] JOIN es lo opuesto al LEFT [OUTER] JOIN. Devuelve todas las filas válidas de la tabla en el lado derecho de la palabra clave JOIN, junto con los valores de la tabla en el lado izquierdo, o NULL si no existe una fila coincidente. Todos los puntos planteados en la sección anterior también se aplican aquí.
El siguiente ejemplo ha alterado el orden de las tablas, por lo que ahora se requiere un RIGHT [OUTER] JOIN.
SELECT d.department_name, e.employee_name FROM employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id WHERE d.department_id >= 30 ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- OPERATIONS SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD 7 rows selected. SQL>
Recuerde, la sintaxis de outer joins no ANSI no depende del orden de la tabla, por lo que no existe un concepto real de outer joins derecha o izquierda, solo outer joins.
FULL [OUTER] JOIN
UN FULL [OUTER] JOIN combina todas las filas de las tablas en los lados izquierdo y derecho de la Join. Si hay un fósforo convencional, está hecho. Si alguno de los lados tiene datos faltantes, se reemplazan por NULL, en lugar de tirar la fila.
Para ver un ejemplo de trabajo, necesitamos agregar otro empleado que no esté asignado a un departamento.
INSERT INTO employees VALUES (8888,'JONES','DBA',null,to_date('02-1-1982','dd-mm-yyyy'),1300,NULL,NULL); COMMIT; Aquí hay un ejemplo de ANSI FULL OUTER JOIN. SELECT d.department_name, e.employee_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id ORDER BY d.department_name, e.employee_name; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- ACCOUNTING CLARK ACCOUNTING KING ACCOUNTING MILLER OPERATIONS RESEARCH ADAMS RESEARCH FORD RESEARCH JONES RESEARCH SCOTT RESEARCH SMITH SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD JONES 16 rows selected. SQL>
No existe un equivalente directo de full outer join usando la sintaxis de Join no ANSI, pero podemos recrearla combinando dos consultas de Outer Join usando UNION ALL, como se muestra a continuación.
SELECT d.department_name, e.employee_name FROM employees e, departments d WHERE e.department_id = d.department_id (+) UNION ALL SELECT d.department_name, e.employee_name FROM departments d, employees e WHERE d.department_id = e.department_id (+) AND e.employee_name IS NULL ORDER BY 1, 2; DEPARTMENT_NAM EMPLOYEE_N -------------- ---------- ACCOUNTING CLARK ACCOUNTING KING ACCOUNTING MILLER OPERATIONS RESEARCH ADAMS RESEARCH FORD RESEARCH JONES RESEARCH SCOTT RESEARCH SMITH SALES ALLEN SALES BLAKE SALES JAMES SALES MARTIN SALES TURNER SALES WARD JONES 16 rows selected. SQL>
Curiosamente, cuando ejecuta un ANSI FULL OUTER JOIN, el optimizador de Oracle lo reescribe en un equivalente de join no ANSI, por lo que no hay una mejora de rendimiento asociada con él. Simplemente es más agradable a la vista.
Eliminemos ese empleado adicional para que no afecte a ningún otro ejemplo.
DELETE FROM employees WHERE employee_id = 8888; COMMIT;
CROSS JOIN
Un CROSS JOIN es la creación deliberada de un producto cartesiano. No se especifican columnas de unión, por lo que se producen todas las combinaciones posibles de filas entre las dos tablas.
A continuación se muestra un ejemplo de ANSI CROSS JOIN.
SELECT e.employee_name, d.department_name FROM employees e CROSS JOIN departments d ORDER BY e.employee_name, d.department_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS ACCOUNTING ADAMS OPERATIONS ADAMS RESEARCH ADAMS SALES ... Salida modificada por brevedad... WARD ACCOUNTING WARD OPERATIONS WARD RESEARCH WARD SALES 56 rows selected. SQL>
Aquí está el equivalente no ANSI de la declaración anterior. Tenga en cuenta que no hay condiciones de Join en la cláusula WHERE.
SELECT e.employee_name, d.department_name FROM employees e, departments d ORDER BY e.employee_name, d.department_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS ACCOUNTING ADAMS OPERATIONS ADAMS RESEARCH ADAMS SALES ... Salida modificada por brevedad ... WARD ACCOUNTING WARD OPERATIONS WARD RESEARCH WARD SALES 56 rows selected. SQL>
NATURAL JOIN
A NATURAL JOIN es una variante de INNER JOIN. Las columnas de Join se determinan implícitamente, según los nombres de las columnas. Se supone que todas las columnas que comparten el mismo nombre entre las dos tablas son columnas de unión. A continuación, se muestra un ejemplo que utiliza la sintaxis de Join ANSI.
SELECT e.employee_name, d.department_name FROM employees e NATURAL JOIN departments d ORDER BY e.employee_name, d.department_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH ALLEN SALES BLAKE SALES CLARK ACCOUNTING FORD RESEARCH JAMES SALES JONES RESEARCH KING ACCOUNTING MARTIN SALES MILLER ACCOUNTING SCOTT RESEARCH SMITH RESEARCH TURNER SALES WARD SALES 14 rows selected. SQL>
No existe un equivalente no ANSI de esto, ya que se deben especificar todas las condiciones de unión.
Usar un NATURAL JOIN es una mala idea. Si alguien agrega una nueva columna a una de las tablas que tiene el mismo nombre que una columna en la otra tabla, puede romper cualquier Join natural existente. De hecho, es un error que está esperando a suceder.
No puede aplicar filtros con alias a las columnas que se utilizan en joins naturales, como se muestra en el siguiente ejemplo.
SELECT e.employee_name, d.department_name FROM employees e NATURAL JOIN departments d WHERE d.department_id = 20 ORDER BY e.employee_name; WHERE d.department_id = 20 * ERROR at line 5: ORA-25155: column used in NATURAL join cannot have qualifier SQL>
En su lugar, debe eliminar el alias, que en otras circunstancias resultaría en un error de referencia ambiguo.
SELECT e.employee_name, d.department_name FROM employees e NATURAL JOIN departments d WHERE department_id = 20 ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH FORD RESEARCH JONES RESEARCH SCOTT RESEARCH SMITH RESEARCH 5 rows selected. SQL>
[INNER] JOIN … USING
El INNER JOIN … USING es casi una casa a medio camino entre un INNER JOIN convencional y un NATURAL JOIN. La unión se realiza utilizando columnas con nombres coincidentes en cada tabla, pero debe especificar las columnas que se utilizarán, no la condición completa. Esto le permite unirse a un subconjunto de columnas comunes a ambas tablas.
SELECT e.employee_name, d.department_name FROM employees e JOIN departments d USING (department_id) ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH ALLEN SALES BLAKE SALES CLARK ACCOUNTING FORD RESEARCH JAMES SALES JONES RESEARCH KING ACCOUNTING MARTIN SALES MILLER ACCOUNTING SCOTT RESEARCH SMITH RESEARCH TURNER SALES WARD SALES 14 rows selected. SQL>
Esta es una sintaxis de Join seguro, ya que no puede verse afectada por la adición de columnas a ninguna de las tablas. Al igual que en NATURAL JOIN, no puede aplicar ningún filtro con alias a las columnas utilizadas en Join, pero si elimina el alias, funciona.
SELECT e.employee_name, d.department_name FROM employees e JOIN departments d USING (department_id) WHERE d.department_id = 20 ORDER BY e.employee_name; WHERE d.department_id = 20 * ERROR at line 5: ORA-25154: column part of USING clause cannot have qualifier SQL> SELECT e.employee_name, d.department_name FROM employees e JOIN departments d USING (department_id) WHERE department_id = 20 ORDER BY e.employee_name; EMPLOYEE_N DEPARTMENT_NAM ---------- -------------- ADAMS RESEARCH FORD RESEARCH JONES RESEARCH SCOTT RESEARCH SMITH RESEARCH 5 rows selected. SQL>
Joins adicionales
Las combinaciones CROSS APPLY y OUTER APPLY están disponibles en Oracle, pero solo se han admitido para su uso en el código de su aplicación desde Oracle 12c en adelante, por lo que es poco probable que las vea en el código de la aplicación durante algún tiempo.
Pingback: SQL para Principiantes (Parte 2): La Cláusula FROM - DBandTech.com
Avon
Virtual