Manejo de Joins

En esta ocasión, por solicitud de una amistad, surgió el tema de cómo se usan las distintas modalidades de los join en Oracle para unir dos o más tablas. Así, aquí abordo el tema.

¿Qué es un join?

Un Join, como su nombre en español lo indica, es una unión; en este caso, entre dos tablas por medio de un campo. En nuestro más común caso basados en el esquema de prueba que se encuentra en ORACLE_HOME\rdbms\admin\utlsampl.sql; unimos la tabla de empleados con sus departamentos:

SQL> select empno, ename, job, mgr, deptno
  2  from   emp;

     EMPNO ENAME      JOB              MGR     DEPTNO
---------- ---------- --------- ---------- ----------
      7369 HETFIELD   CLERK
      7499 ALLEN      SALESMAN        7698         30
      7521 WARD       SALESMAN        7698         30
      7566 JONES      MANAGER         7839         20
      7654 MARTIN     SALESMAN        7698         30
      7698 BLAKE      MANAGER         7839         30
      7782 CLARK      MANAGER         7839         10
      7788 SCOTT      ANALYST         7566         20
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN        7698         30
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST         7566         20
      7934 MILLER     CLERK
      9999 JUAN
      2222 HAMMET     CLERK                        10
      7777 ULRICH     CLERK                        10
      1234 HETFIELD   CLERK                        10
      4321 TRUJILLO   CLERK                        10
      4444 ARAYA      CLERK                        10
      5555 LOMBARDO   CLERK                        10
      6666 ANSELMO    CLERK                        10
      9421 RAMIREZ    CLERK                        10
      9090 GONZALEZ   CLERK                        10

24 rows selected.
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

SQL>
SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp  e,
  3         dept d
  4  where  e.deptno = d.deptno;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      2222 HAMMET     CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      7902 FORD       ANALYST   RESEARCH
      7788 SCOTT      ANALYST   RESEARCH
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7499 ALLEN      SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES

18 rows selected.

Como se puede observar, se unen los datos de la tabla emp con los datos de la tabla dept a través del campo deptno para tener dicha información. El join nos servirá para mostrar información de dos o más tablas unidas por algún campo en común. Para lograr esto, Oracle une dos registros, uno de cada tabla y los compara; a partir de ahí, si son iguales, los muestra. Esto se realiza en la claúsula Where y los campos involucrados no necesitan estar involucrados en la instrucción SQL que se está ejecutando.

Cuando se unen más de dos tablas, Oracle realiza un join a la vez, al inicio con las dos primeras tablas que encuentra, a partir del conjunto de datos resultante, se une con la tercera tabla y así sucesivamente:

((A join B) join C) join D …

Tipos de join

A continuación, se detallan los distintos tipos de Join que se tienen:

Equijoin

Este tipo de Join, tiene involucrado el igual (=) como comparador entre dos campos de dos tablas distintas. Devolverá los registros que cumplan con la condición de ser iguales en ambas tablas. Es el ejemplo que mostré al inicio de este post.

Self join

Es un join de una tabla consigo misma. Se realiza de manera similar a un join normal; la diferencia sustancial, reside en que será comparada y unida por algún campo de la misma tabla. Un ejemplo claro de esto, es cuando se quiere mostrar el jefe de cada empleado en nuestra tabla emp de la cual, ya mostré su contenido al inicio de este post:

SQL> select emp.empno, emp.ename, emp.job, mgr.ename, mgr.job
  2  from   emp emp,
  3         emp mgr
  4  where  emp.mgr = mgr.empno;

     EMPNO ENAME      JOB       ENAME      JOB
---------- ---------- --------- ---------- ---------
      7788 SCOTT      ANALYST   JONES      MANAGER
      7902 FORD       ANALYST   JONES      MANAGER
      7844 TURNER     SALESMAN  BLAKE      MANAGER
      7499 ALLEN      SALESMAN  BLAKE      MANAGER
      7654 MARTIN     SALESMAN  BLAKE      MANAGER
      7521 WARD       SALESMAN  BLAKE      MANAGER
      7782 CLARK      MANAGER   KING       PRESIDENT
      7566 JONES      MANAGER   KING       PRESIDENT
      7698 BLAKE      MANAGER   KING       PRESIDENT

9 rows selected.

Producto cartesiano

El producto cartesiano, se genera cuando se incluyen dos o más tablas en la parte From y no se incluye alguna condición que las una en un join.

Es una de las peores formas de acceder a la información porque por cada registro de una tabla se ponen todos los registros de la otra. Así, si se tienen una tabla de 100 registros y se hace un producto cartesiano con una 10,000, se tendrán 1,000,000 registros.

A menos que el negocio lo requiera, se deberá evitar al máximo este tipo de situaciones. Incluyo un ejemplo a continuación:

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp  e,
  3         dept d;
     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7369 HETFIELD   CLERK     ACCOUNTING
      7499 ALLEN      SALESMAN  ACCOUNTING
      7521 WARD       SALESMAN  ACCOUNTING
      7566 JONES      MANAGER   ACCOUNTING
      7654 MARTIN     SALESMAN  ACCOUNTING
      7698 BLAKE      MANAGER   ACCOUNTING
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   ACCOUNTING
      7839 KING       PRESIDENT ACCOUNTING
      7844 TURNER     SALESMAN  ACCOUNTING

...
      7369 HETFIELD   CLERK     RESEARCH
      7499 ALLEN      SALESMAN  RESEARCH
      7521 WARD       SALESMAN  RESEARCH
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  RESEARCH
      7698 BLAKE      MANAGER   RESEARCH
      7782 CLARK      MANAGER   RESEARCH
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT RESEARCH
      7844 TURNER     SALESMAN  RESEARCH
...
      7369 HETFIELD   CLERK     SALES
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   SALES
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   SALES
      7788 SCOTT      ANALYST   SALES
      7839 KING       PRESIDENT SALES
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK     SALES
...
      7369 HETFIELD   CLERK     OPERATIONS
      7499 ALLEN      SALESMAN  OPERATIONS
      7521 WARD       SALESMAN  OPERATIONS
      7566 JONES      MANAGER   OPERATIONS
      7654 MARTIN     SALESMAN  OPERATIONS
      7698 BLAKE      MANAGER   OPERATIONS
      7782 CLARK      MANAGER   OPERATIONS
      7788 SCOTT      ANALYST   OPERATIONS
      7839 KING       PRESIDENT OPERATIONS
...
96 rows selected.

El cual, tiene el siguiente plan de ejecución con su producto cartesiano incluido:

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    88 |  2464 |   103   (0)| 00:00:02 |
|   1 |  MERGE JOIN CARTESIAN|      |    88 |  2464 |   103   (0)| 00:00:02 |
|   2 |   TABLE ACCESS FULL  | DEPT |     4 |    40 |    22   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |    22 |   396 |    81   (0)| 00:00:02 |
|   4 |    TABLE ACCESS FULL | EMP  |    22 |   396 |    20   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Inner join (Simple join)

Es la unión entre dos tablas que nos muestra nada más aquellos registros que cumplen con la condición del join.

Outer join

Este tipo de join, nos devuelve los registros que cumplen con la condición de join, más aquellos registros de una de las dos tablas involucradas que no cumplieron con la condición.

Ejemplos claros de estos podrían ser:

  1. Mostrar todos los departamentos de la empresa con sus empleados, no importa si no tienen empleados.
  2. Listar los empleados de la empresa con su departamento asignado, aún cuando no tengan departamento asignado.

Para lograr este tipo de resultados, se tienen las siguientes modalidades de outer join:

Left outer join

Es aquel en el que se unen dos tablas A y B. En esta unión se incluyen todos los registros de A aunque no satisfagan la condición. Para hacer esto, se puede usar el Left Outer Join o el operador (+) en todas las columnas de B en la parte Where. Ejemplo:

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp e left outer join dept d
  3  on     e.deptno = d.deptno;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7369 HETFIELD   CLERK
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK
      9999 JUAN
      2222 HAMMET     CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING

24 rows selected.

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp  e,
  3         dept d
  4  where  e.deptno = d.deptno (+);

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7369 HETFIELD   CLERK
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK
      9999 JUAN
      2222 HAMMET     CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING

24 rows selected.

Right outer join

Es aquel en el que se unen dos tablas A y B. En esta unión se incluyen todos los registros de B aunque no satisfagan la condición. Para hacer esto, se puede usar el Right Outer Join o el operador (+) en todas las columnas de A en la parte Where. Ejemplo:

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp e right outer join dept d
  3  on     e.deptno = d.deptno;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      2222 HAMMET     CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      7902 FORD       ANALYST   RESEARCH
      7788 SCOTT      ANALYST   RESEARCH
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7499 ALLEN      SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES
                                OPERATIONS

19 rows selected.

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp  e,
  3         dept d
  4  where  e.deptno (+) = d.deptno;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      2222 HAMMET     CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      7902 FORD       ANALYST   RESEARCH
      7788 SCOTT      ANALYST   RESEARCH
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7499 ALLEN      SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES
                                OPERATIONS

19 rows selected.

Full outer join

Finalmente, este join es la combinación del right outer join y el left outer join; mostrándonos la información de ambas tablas unidas A y B no importando que haya registros que no satisfagan la condición. Para realizar esto, se usa la cláusula Full Outer Join o un Union con los dos queries del operador (+) como se puede apreciar en los ejemplos siguientes:

SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp e full outer join dept d
  3  on     e.deptno = d.deptno;
     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7369 HETFIELD   CLERK
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK
      9999 JUAN
      2222 HAMMET     CLERK     ACCOUNTING
      7777 ULRICH     CLERK     ACCOUNTING
      1234 HETFIELD   CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      9090 GONZALEZ   CLERK     ACCOUNTING
                                OPERATIONS
25 rows selected.
SQL> select e.empno, e.ename, e.job, d.dname
  2  from   emp  e,
  3         dept d
  4  where  e.deptno (+) = d.deptno
  5  union
  6  select e.empno, e.ename, e.job, d.dname
  7  from   emp  e,
  8         dept d
  9  where  e.deptno = d.deptno (+);
     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      1234 HETFIELD   CLERK     ACCOUNTING
      2222 HAMMET     CLERK     ACCOUNTING
      4321 TRUJILLO   CLERK     ACCOUNTING
      4444 ARAYA      CLERK     ACCOUNTING
      5555 LOMBARDO   CLERK     ACCOUNTING
      6666 ANSELMO    CLERK     ACCOUNTING
      7369 HETFIELD   CLERK
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7566 JONES      MANAGER   RESEARCH
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7777 ULRICH     CLERK     ACCOUNTING
      7782 CLARK      MANAGER   ACCOUNTING
      7788 SCOTT      ANALYST   RESEARCH
      7839 KING       PRESIDENT
      7844 TURNER     SALESMAN  SALES
      7876 ADAMS      CLERK
      7900 JAMES      CLERK
      7902 FORD       ANALYST   RESEARCH
      7934 MILLER     CLERK
      9090 GONZALEZ   CLERK     ACCOUNTING
      9421 RAMIREZ    CLERK     ACCOUNTING
      9999 JUAN
                                OPERATIONS
25 rows selected.

Oracle recomienda el uso de los comandos Outer Join más que el uso del operador (+), ya que este último tiene algunas restricciones que los primeros no.

Antijoin

Este tipo de join es parecido a la operación Minus entre queries. Nos devuelve una cantidad de registros de la parte izquierda del predicado que no pertenecen a la parte derecha. Esto se logra con un Not In como se muestra a continuación:

SQL> select empno, ename, job
  2  from   emp
  3  where  deptno not in (select deptno
  4                        from   dept
  5                        where  dname = 'ACCOUNTING');
     EMPNO ENAME      JOB
---------- ---------- ---------
      7499 ALLEN      SALESMAN
      7521 WARD       SALESMAN
      7566 JONES      MANAGER
      7654 MARTIN     SALESMAN
      7698 BLAKE      MANAGER
      7788 SCOTT      ANALYST
      7844 TURNER     SALESMAN
      7902 FORD       ANALYST
8 rows selected.

Semijoin

Este tipo de join, muestra los registros que coinciden por medio de un subquery que usa la palabra Exists, sin duplicar registros de la parte izquierda del mismo, cuando múltiples registros del lado derecho satisfagan la condición del subquery. Esto lo podemos constatar en el siguiente ejemplo:

SQL> select *
  2  from   dept
  3  where  exists (select *
  4                 from   emp
  5                 where  dept.deptno = emp.deptno
  6                   and  emp.job     = 'CLERK');
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
1 row selected.

En este caso, el Exists le ayuda al query a tener un mejor performance a pesar de que la tabla emp no tiene índice sobre el campo job. Esto se puede ver en lo que muestro enseguida:

SQL> select index_name, column_name
  2  from   user_ind_columns
  3  where  table_name = 'EMP'
  4  order by index_name, column_position;
INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
EMP_01                         EMPNO
                               DEPTNO
EMP_02                         JOB
EMP_03                         DEPTNO
PK_EMP                         EMPNO
5 rows selected.

SQL> explain plan for select *
  2  from   dept
  3  where  exists (select *
  4                 from   emp
  5                 where  dept.deptno = emp.deptno
  6                   and  emp.job     = 'CLERK');

Explained.

SQL> select * from table (dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

Plan hash value: 3215961860

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     3 |    90 |     4  (25)| 00:00:01 |
|   1 |  MERGE JOIN SEMI              |         |     3 |    90 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | DEPT    |     4 |    80 |     1   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN            | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE                 |         |     8 |    80 |     3  (34)| 00:00:01 |
|*  5 |    TABLE ACCESS BY INDEX ROWID| EMP     |     8 |    80 |     2   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | EMP_02  |    11 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
       filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
   5 - filter("EMP"."DEPTNO" IS NOT NULL)
   6 - access("EMP"."JOB"='CLERK')

Nota del Antijoin y Semijoin

Debemos tener cuidado con este tipo de join, ya que no funcionará si el subquery está en un Or en la cláusula Where.

Conclusiones

Los joins son muy útiles para traer información de dos o más tablas y poder combinar la información que reside en ellas. Un abuso o mal uso de ellos sin embargo, puede causar dolores serios de cabeza. Para saber si estamos haciendo o no un buen query, requerimos acudir a nuestro plan de ejecución y/o algún trace para saber si realmente, es correcto lo que estamos construyendo.

Si la información de este post te ha sido de utilidad o quieres que agregue algo más, deja por favor un comentario, contestaré a la brevedad.

Anuncios

2 Responses to Manejo de Joins

  1. bluesoul says:

    ¡Gracias!

  2. Orlando Olguín Olvera says:

    Hola Bluesoul.

    De nada, qué bueno que te sirvió.

    Orlando.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: