37 - Combinación externa izquierda (left join)

Vimos que una combinación interna (join) encuentra registros de la primera tabla que se correspondan con los registros de la segunda, es decir, que cumplan la condición del "on" y si un valor de la primera tabla no se encuentra en la segunda tabla, el registro no aparece.

Si queremos saber qué registros de una tabla NO encuentran correspondencia en la otra, es decir, no existe valor coincidente en la segunda, necesitamos otro tipo de combinación, "outer join" (combinación externa).

Las combinaciones externas combinan registros de dos tablas que cumplen la condición, más los registros de la segunda tabla que no la cumplen; es decir, muestran todos los registros de las tablas relacionadas, aún cuando no haya valores coincidentes entre ellas.

Este tipo de combinación se emplea cuando se necesita una lista completa de los datos de una de las tablas y la información que cumple con la condición. Las combinaciones externas se realizan solamente entre 2 tablas.

Hay un tipo de combinación externa que implementa SQLite: "left outer join"; se pueden abreviar con "left join".

Vamos a estudiar esta combinación externa.

Se emplea una combinación externa izquierda para mostrar todos los registros de la tabla de la izquierda. Si no encuentra coincidencia con la tabla de la derecha, el registro muestra los campos de la segunda tabla seteados a "null".

En el siguiente ejemplo solicitamos el título y nombre de la editorial de los libros:

  select titulo,nombre
  from editoriales as e
  left join libros as l on codigoeditorial = e.codigo;

El resultado mostrará el título y nombre de la editorial; las editoriales de las cuales no hay libros, es decir, cuyo código de editorial no está presente en "libros" aparece en el resultado, pero con el valor "null" en el campo "titulo".

Es importante la posición en que se colocan las tablas en un "left join", la tabla de la izquierda es la que se usa para localizar registros en la tabla de la derecha.

Entonces, un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con otra tabla (derecha); si un valor de la tabla de la izquierda no encuentra coincidencia en la tabla de la derecha, se genera una fila extra (una por cada valor no encontrado) con todos los campos correspondientes a la tabla derecha seteados a "null". La sintaxis básica es la siguiente:

  select CAMPOS
  from TABLAIZQUIERDA
  left join TABLADERECHA on CONDICION;

En el siguiente ejemplo solicitamos el título y el nombre de la editorial, la sentencia es similar a la anterior, la diferencia está en el orden de las tablas:

  select titulo,nombre
  from libros as l
  left join editoriales as e on codigoeditorial = e.codigo;

El resultado mostrará el título del libro y el nombre de la editorial; los títulos cuyo código de editorial no está presente en "editoriales" aparecen en el resultado, pero con el valor "null" en el campo "nombre".

Un "left join" puede tener clausula "where" que restringa el resultado de la consulta considerando solamente los registros que encuentran coincidencia en la tabla de la derecha, es decir, cuyo valor de código está presente en "libros":

 select titulo,nombre
  from editoriales as e
  left join libros as l on e.codigo=codigoeditorial
  where codigoeditorial is not null;

También podemos mostrar las editoriales que NO están presentes en "libros", es decir, que NO encuentran coincidencia en la tabla de la derecha:

 select titulo,nombre
  from editoriales as e
  left join libros as l on e.codigo=codigoeditorial
  where codigoeditorial is null;

Problema resuelto

Trabajamos con las tablas "clientes" y "provincias".

Eliminamos las tablas si existen:

drop table if exists clientes;
drop table if exists provincias;

Creamos las tablas con las siguientes estructuras:

 create table clientes (
  codigo integer primary key,
  nombre text,
  domicilio text,
  ciudad text,
  codigoprovincia integer
 );

 create table provincias(
  codigo integer primary key,
  nombre text
 );

Almacenamos los siguientes registros:

 insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');
 
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia) 
   values ('Lopez Marcos','Colon 111','Córdoba',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Ana','San Martin 222','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Garcia Juan','Rivadavia 333','Villa Maria',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Perez Luis','Sarmiento 444','Rosario',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Pereyra Lucas','San Martin 555','Cruz del Eje',1);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Gomez Ines','San Martin 666','Santa Fe',2);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
   values ('Torres Fabiola','Alem 777','Ibera',3);
 insert into clientes(nombre,domicilio,ciudad,codigoprovincia)
  values ('Garcia Luis','Sucre 475','Santa Rosa',5);

Mostramos los datos de todos los clientes junto con la provincia a la que pertenece:

select c.nombre,domicilio,ciudad, p.nombre
  from clientes as c
  join provincias as p
  on codigoprovincia = p.codigo;

Podemos observar que utilizando solo 'join' no aparece el cliente 'Garcia Luis' ya que su codigoprovincia no existe en la tabla 'provincias'.

Mostramos ahora todos los clientes, inclusive aquellos que tienen un código de provincia inexistente:

select c.nombre,domicilio,ciudad, p.nombre
  from clientes as c
  left join provincias as p
  on codigoprovincia = p.codigo;

SQLite Browser left join

Realizamos la misma consulta anterior pero alterando el orden de las tablas:

 select c.nombre,domicilio,ciudad, p.nombre
  from provincias as p
  left join clientes as c
  on codigoprovincia = p.codigo;

No aparece el cliente ''Garcia Luis.

Mostraremos todos los clientes cuyo código de provincia NO existe en "provincias" ordenados por nombre del cliente:

select c.nombre,domicilio,ciudad, p.nombre
  from clientes as c
  left join provincias as p
  on codigoprovincia = p.codigo
  where p.codigo is null
  order by c.nombre;

Se debe mostrar solo 'Garcia Luis'.

Obtenemos ahora todos los datos de los clientes de "Cordoba":

 select c.nombre,domicilio,ciudad, p.nombre
  from clientes as c
  left join provincias as p
  on codigoprovincia = p.codigo
  where p.nombre='Cordoba';

Problema propuesto

Un club dicta clases de distintos deportes. En una tabla llamada "socios" guarda los datos de sus socios y en una tabla denominada "inscriptos" almacena la información necesaria para las inscripciones de los socios a los distintos deportes.

  1. Borrar la tabla "socios" e "inscriptos" si existen.

  2. Crear las tablas:

    create table socios(
  documento text primary key,
  nombre text,
  domicilio text
);

create table inscriptos(
  documento text, 
  deporte text not null,
  año integer,
  matricula text, /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
);

  3. Insertar los siguientes registros en las tablas:

    4.  insert into socios (documento,nombre,domicilio) 
  values('22333444','Juan Perez','Colon 234');
 insert into socios (documento,nombre,domicilio) 
  values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios (documento,nombre,domicilio) 
  values('24333444','Antonio Juarez','Caseros 980');
 insert into socios (documento,nombre,domicilio) 
  values('25333444','Ana Juarez','Sucre 134');
 insert into socios (documento,nombre,domicilio) 
  values('26333444','Sofia Herrero','Avellaneda 1234');
   

 insert into inscriptos(documento,año,deporte,matricula) 
  values ('22333444','natacion','2005','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('22333444','natacion','2006','n');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','natacion','2005','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','tenis','2006','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','natacion','2006','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('25333444','tenis','2006','n');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('25333444','basquet','2006','n');

  4. Muestre el nombre del socio, deporte y año realizando un join con la tabla inscriptos.

  5. Muestre el nombre del socio, deporte y año realizando un left join con la tabla inscriptos.

  6. Muestre los nombres de los socios que no se han inscripto nunca en un deporte.

  7. Omita la referencia a las tablas en la condición "on" para verificar que la sentencia no se ejecuta porque el nombre del campo "documento" es ambiguo (ambas tablas lo tienen)

Solución 
drop table if exists socios;
drop table if exists inscriptos;

create table socios(
  documento text primary key,
  nombre text,
  domicilio text
);

create table inscriptos(
  documento text, 
  deporte text not null,
  año integer,
  matricula text, /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
);

 insert into socios (documento,nombre,domicilio) 
  values('22333444','Juan Perez','Colon 234');
 insert into socios (documento,nombre,domicilio) 
  values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios (documento,nombre,domicilio) 
  values('24333444','Antonio Juarez','Caseros 980');
 insert into socios (documento,nombre,domicilio) 
  values('25333444','Ana Juarez','Sucre 134');
 insert into socios (documento,nombre,domicilio) 
  values('26333444','Sofia Herrero','Avellaneda 1234');
   

 insert into inscriptos(documento,año,deporte,matricula) 
  values ('22333444','natacion','2005','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('22333444','natacion','2006','n');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','natacion','2005','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','tenis','2006','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('23333444','natacion','2006','s');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('25333444','tenis','2006','n');
 insert into inscriptos(documento,año,deporte,matricula) 
  values ('25333444','basquet','2006','n');

 select s.nombre,i.deporte,i.año
  from socios as s
  left join inscriptos as i
  on s.documento=i.documento;
  
 select s.nombre
  from socios as s
  left join inscriptos as i
  on s.documento=i.documento
  where i.documento is null;  
  
 select s.nombre
  from socios as s
  left join inscriptos as i
  on documento=documento;

Ejecución de ejercicios online

Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.

Resultado.....
Retornar