¿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.
Escrito por CarlosAL 
CarlosAL 