ANSI OUTER JOINS: parecido no es igual.

¿Qué es un ‘outer join‘? Si estáis leyendo esto, seguramente ya lo sabeis. Un ‘outer join’ es un ‘join’ en el que las filas de la tabla principal son mantenidas en el resultado aunque no existan filas que les correspondan en la tabla con la que se combina.

La sintaxis ANSI es:

SELECT …
FROM Tabla1
(LEFT) OUTER JOIN Tabla2 ON Tabla1.Col1 = Tabla2.Col1
[AND Tabla1.Col2 = Tabla2.Col2 …]

La teoría es fácil y la sintaxis también, pero a veces sentencias ‘muy parecidas’ que en un primer vistazo asumimos como idénticas pueden no serlo tanto y meternos en problemas al devolver resultados inesperados.

Vamos a verlo en Teradata, pero primero las tablas de rigor:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA01 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

CREATE SET TABLE MY_DB.PRUEBA02 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

CREATE SET TABLE MY_DB.PRUEBA02 ,
     NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT
     (
      ID    INTEGER     NOT NULL,
      CTXT  VARCHAR(10) NOT NULL )
PRIMARY INDEX ( ID );

 *** Table has been created.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1, 'UNO');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (1, 'UNO');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (2, 'DOS');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (2, 'DOS');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (3, 'TRES');

INSERT INTO MY_DB.PRUEBA01 (ID, CTXT) VALUES (3, 'TRES');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (1, 'ONE');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (1, 'ONE');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (2, 'TWO');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (2, 'TWO');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (3, 'THREE');

INSERT INTO MY_DB.PRUEBA02 (ID, CTXT) VALUES (3, 'THREE');

 *** Insert completed. One row added.
 *** Total elapsed time was 1 second.

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Las tablas quedan tal que así:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
  FROM MY_DB.PRUEBA01;

SELECT *
  FROM MY_DB.PRUEBA01;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT
-----------  ----------
          2  DOS
          3  TRES
          1  UNO

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT *
  FROM MY_DB.PRUEBA02;

SELECT *
  FROM MY_DB.PRUEBA02;

 *** Query completed. 3 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT
-----------  ----------
          2  TWO
          3  THREE
          1  ONE

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

Ahora vamos con la query: un ‘OUTER JOIN’ ANSI con una condición ‘WHERE’:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
 WHERE a.ID > 1;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
 WHERE a.ID > 1;

 *** Query completed. 2 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE

OK. Ahora vamos a escribir la misma ‘query’ de otra manera para obtener los mismos resultados:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
   AND a.ID > 1;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON a.ID = b.ID
   AND a.ID > 1;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE
          1  UNO         (null)

 BTEQ -- Enter your DBC/SQL request or BTEQ command:

¿Cómo? ¡Aparece una última línea que no debería aparecer! ¿Qué es esto? ¿Acaso un ‘bug’ de Teradata? No. No hay nada errónero en el resultado, lo erróneo está en nuestra cabeza y lo que esperamos ver. Quizá apereca más claro si lo escribimos así:

 BTEQ -- Enter your DBC/SQL request or BTEQ command:
SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON ( a.ID = b.ID
                  AND
                  a.ID > 1 )
;

SELECT a.ID,
       a.CTXT,
       b.CTXT
  FROM MY_DB.PRUEBA01 a
  LEFT OUTER JOIN MY_DB.PRUEBA02 b
             ON ( a.ID = b.ID
                  AND
                  a.ID > 1 )
;

 *** Query completed. 3 rows found. 3 columns returned.
 *** Total elapsed time was 1 second.

         ID  CTXT        CTXT
-----------  ----------  ----------
          2  DOS         TWO
          3  TRES        THREE
          1  UNO         (null)

Ahora se ilumina una lucecilla y empezamos a ver qué es lo que está pasando: Teradata tiene razón y está simplemente siguiendo las reglas y mecanismos de los ‘outer joins’. En el primer ejemplo efectúa un ‘outer join’ con la condición ‘ON a.ID = b.ID’ y a este resultado le aplica el filtro del ‘where’: ‘WHERE a.ID > 1’, donde se elimina la fila correspondiente a a.ID = 1.

En el segundo ejemplo combina todas las filas de la tabla MY_DB.PRUEBA01 a con las de la tabla MY_DB.PRUEBA02 b y las que no cumplen la condición (aquí es donde está la trampa) ‘AND a.ID > 1’ les asigna filas ‘nulas’ de MY_DB.PRUEBA02 b. De ahí que aparezca la fila “1, ‘UNO’, (null)”: No hay filtro que elimine luego esa fila.

Así que las partículas ‘WHERE a.ID > 1’ y ‘AND a.ID > 1’ aunque parezcan iguales, no lo son.

Esto no es algo propio de Teradata. Oracle funciona de la misma manera:

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Jun 3 11:33:20 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@XE.localhost
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

CARLOS@XE.localhost> CREATE TABLE PRUEBA01(IDN INTEGER NOT NULL,
  2                        CTXT  VARCHAR2(10) NOT NULL,
  3                        CONSTRAINT PRUEBA01_PK PRIMARY KEY(IDN)
  4                        )
  5  ;

Tabla creada.

CARLOS@XE.localhost> CREATE TABLE PRUEBA02(IDN INTEGER NOT NULL,
  2                        CTXT  VARCHAR2(10) NOT NULL,
  3                        CONSTRAINT PRUEBA02_PK PRIMARY KEY(IDN)
  4                        )
  5  ;

Tabla creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (1,'UNO');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (2,'DOS');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA01(IDN, CTXT) VALUES (3,'TRES');

1 fila creada.

CARLOS@XE.localhost>
CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (1,'ONE');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (2,'TWO');

1 fila creada.

CARLOS@XE.localhost> INSERT INTO PRUEBA02(IDN, CTXT) VALUES (3,'THREE');

1 fila creada.

CARLOS@XE.localhost> commit;

Confirmación terminada.

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a
  5    LEFT OUTER JOIN PRUEBA02 b
  6               ON a.IDN = b.IDN
  7   WHERE a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         2 DOS        TWO
         3 TRES       THREE

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a
  5    LEFT OUTER JOIN PRUEBA02 b
  6               ON a.IDN = b.IDN
  7     AND a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         1 UNO
         2 DOS        TWO
         3 TRES       THREE

Por último, los ‘oracleros’ pueden ver a cual de las dos sintaxis ANSI corresponde su sintaxis nativa:

CARLOS@XE.localhost> SELECT a.IDN,
  2         a.CTXT,
  3         b.CTXT
  4    FROM PRUEBA01 a,
  5         PRUEBA02 b
  6   WHERE a.IDN = b.IDN(+)
  7     AND a.IDN > 1;

       IDN CTXT       CTXT
---------- ---------- ----------
         2 DOS        TWO
         3 TRES       THREE

CARLOS@XE.localhost>

En efecto: con la ‘buena’: aquí el ‘AND’ se considera en el ‘WHERE’.

Saludos.

Carlos.

Anuncios

One Response to ANSI OUTER JOINS: parecido no es igual.

  1. […] OUTER JOINS: parecido no es igual (II) Ya vimos que las ‘ANSI OUTER JOINS’ tienen sus particularidades. A raíz de esa entrada recibí un confuso ‘mail’ que intentaba rebatir el […]

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: