SQL para Principiantes (Parte 5): JOIN

Base de datos, Curso-SQL, Oracle, SQL
  • 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.

Quizás también te puedas interesar:

3 comentarios en “SQL para Principiantes (Parte 5): JOIN”

  1. Pingback: SQL para Principiantes (Parte 2): La Cláusula FROM - DBandTech.com

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *