64 - Varias tablas (left join)

Hemos visto cómo usar registros de una tabla para encontrar registros de otra tabla, uniendo ambas tablas con "join" y enlazándolas con una condición "on" en la cual colocamos el campo en común. O sea, hacemos un "join" y asociamos registros de 2 tablas usando el "on", buscando coincidencia en los valores del campo que tienen en comun ambas tablas.

Trabajamos con las tablas de una librería:

-libros: codigo (clave primaria), titulo, autor, codigoeditorial, precio, cantidad y

-editoriales: codigo (clave primaria), nombre.

Queremos saber de qué editoriales no tenemos libros.

Para averiguar qué registros de una tabla no se encuentran en otra tabla necesitamos usar un "join" diferente.

Necesitamos determinar qué registros no tienen correspondencia en otra tabla, cuáles valores de la primera tabla (de la izquierda) no están en la segunda (de la derecha).

Para obtener la lista de editoriales y sus libros, incluso de aquellas editoriales de las cuales no tenemos libros usamos:

 select * from editoriales

  left join libros

  on editoriales.codigo=libros.codigoeditorial;

Un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con otra tabla (derecha), pero, 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 seteados a "null".

Entonces, la sintaxis es la siguiente: se nombran ambas tablas, una a la izquierda del "join" y la otra a la derecha, y la condición para enlazarlas, es decir, el campo por el cual se combinarán, se establece luego de "on". 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. Por lo tanto, estos "join" no son iguales:

 select * from editoriales

 left join libros

 on editoriales.codigo=libros.codigoeditorial;

 select * from libros

  left join editoriales

  on editoriales.codigo=libros.codigoeditorial;

La primera sentencia opera así: por cada valor de codigo de "editoriales" busca coincidencia en la tabla "libros", si no encuentra coincidencia para algún valor, genera una fila seteada a "null".

La segunda sentencia opera de modo inverso: por cada valor de "codigoeditorial" de "libros" busca coincidencia en la tabla "editoriales", si no encuentra coincidencia, setea la fila a "null".

Usando registros de la tabla de la izquierda se encuentran registros en la tabla de la derecha.

Luego del "on" se especifican los campos que se asociarán; no se deben colocar condiciones en la parte "on" para restringir registros que deberían estar en el resultado, para ello hay que usar la cláusula "where".

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:

 select e.nombre,l.titulo

  from editoriales as e

  left join libros as l

  on e.codigo=l.codigoeditorial

  where l.codigoeditorial is not null;

El anterior "left join" muestra los valores de la tabla "editoriales" que están presentes en la tabla de la derecha ("libros").

También podemos mostrar las editoriales que no están presentes en "libros":

 select e.nombre,l.titulo from editoriales as e

  left join libros as l

  on e.codigo=l.codigoeditorial

  where l.codigoeditorial is null;

El anterior "left join" muestra los valores de la tabla "editoriales" que no encuentran correspondencia en la tabla de la derecha, "libros".

PROBLEMA RESUELTO

Trabajamos con las tablas "libros" y editoriales" de una librería.

Eliminamos dichas tablas, si existen:

 drop table if exists libros, editoriales;

Creamos las siguientes tablas:

 create table libros(

  codigo int unsigned auto_increment,

  titulo varchar(40) not null,

  autor varchar(30) not null default 'Desconocido',

  codigoeditorial tinyint unsigned not null,

  precio decimal(5,2) unsigned,

  cantidad tinyint unsigned default 0,

  primary key (codigo)

 );

 create table editoriales(

  codigo tinyint unsigned auto_increment,

  nombre varchar(20) not null,

  primary key(codigo)

 );

Cargamos algunos registros en la tabla "editoriales":

 insert into editoriales (nombre) values('Paidos');

 insert into editoriales (nombre) values('Emece');

 insert into editoriales (nombre) values('Planeta');

 insert into editoriales (nombre) values('Sudamericana');

Cargamos algunos registros en la tabla "libros":

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('El Aleph','Borges',3,43.5,200);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Alicia en el pais de las maravillas','Lewis Carroll',2,33.5,100);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Aprenda PHP','Mario Perez',1,55.8,50);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Java en 10 minutos','Juan Lopez',1,88,150);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Alicia a traves del espejo','Lewis Carroll',1,15.5,80);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Cervantes y el quijote','Borges- Bioy Casares',3,25.5,300);

 insert into libros (titulo, autor,codigoeditorial,precio,cantidad)

  values('Aprenda Java en 10 minutos','Lopez Juan',5,28,100);

Para obtener la lista de todas las editoriales y los libros de las mismas, incluso de las cuales 

no tenemos libros usamos:

 select * from editoriales

  left join libros

  on editoriales.codigo=libros.codigoeditorial;

Un "left join" se usa para hacer coincidir registros en una tabla (izquierda) con otra tabla (derecha), 

pero, 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 seteados a "null".

Recuerde que es importante la posición en que se colocan las tablas en un "left join", la tabla de la 

izquierda es la que localiza registros en la tabla de la derecha. Por lo tanto, estos "join" no son iguales:

 select * from editoriales

 left join libros

 on editoriales.codigo=libros.codigoeditorial;

 select * from libros

  left join editoriales

  on editoriales.codigo=libros.codigoeditorial;

La primera sentencia busca coincidencia en la tabla "libros" por cada valor de codigo de "editoriales", 

si no encuentra coincidencia para algún valor, genera una fila seteada a "null". No hay libros de la 

editorial "Sudamericana", entonces esa fila contiene "null" en todos los campos correspondientes 

a "libros".

La segunda sentencia busca coincidencia en la tabla "editoriales" por cada valor de 

"codigoeditorial" de "libros", si no encuentra coincidencia para algún valor, genera una 

fila seteada a "null". El libro "Aprenda java en 10 minutos" tiene código de editorial "5", 

valor inexistente en la tabla "editoriales", por ello, esa fila contiene "null" en 

todos los campos correspondientes a "editoriales".

Para encontrar los valores de código de la tabla "editoriales" que están presentes en la tabla 

"libros" usamos un "where":

 select nombre,titulo

  from editoriales as e

  left join libros as l

  on e.codigo=l.codigoeditorial

  where l.codigoeditorial is not null;

Para mostrar las editoriales que no están presentes en "libros":

 select e.nombre,l.titulo from editoriales as e

  left join libros as l

  on e.codigo=l.codigoeditorial

  where l.codigoeditorial is null;

PROBLEMA PROPUESTO 

Una empresa tiene registrados sus clientes en una tabla llamada 
"clientes", también tiene una tabla "provincias" donde registra 
los nombres de las provincias.
 
1- Elimine las tablas "clientes" y "provincias", si existen:
 
2- Créelas con las siguientes estructuras:
 
 create table clientes (
  codigo int unsigned auto_increment,
  nombre varchar(30) not null,
  domicilio varchar(30),
  ciudad varchar(20),
  codigoProvincia tinyint unsigned,
  telefono varchar(11),
  primary key(codigo)
 );
 
 create table provincias(
  codigo tinyint unsigned auto_increment,
  nombre varchar(20),
  primary key (codigo)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into provincias (nombre) values('Cordoba');
 insert into provincias (nombre) values('Santa Fe');
 insert into provincias (nombre) values('Corrientes');
 insert into provincias (nombre) values('Misiones');
 insert into provincias (nombre) values('Salta');
 insert into provincias (nombre) values('Buenos Aires');
 insert into provincias (nombre) values('Neuquen');
 
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Marcos', 'Colon 111', 'Córdoba',1,'null');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Ana', 'San Martin 222', 'Cruz del Eje',1,'4578585');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Garcia Juan', 'Rivadavia 333', 'Villa Maria',1,'4578445');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Perez Luis', 'Sarmiento 444', 'Rosario',2,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Pereyra Lucas', 'San Martin 555', 'Cruz del Eje',1,'4253685');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Gomez Ines', 'San Martin 666', 'Santa Fe',2,'0345252525');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Torres Fabiola', 'Alem 777', 'Villa del Rosario',1,'4554455');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Carlos', 'Irigoyen 888', 'Cruz del Eje',1,null);
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Ramos Betina', 'San Martin 999', 'Cordoba',1,'4223366');
 insert into clientes (nombre,domicilio,ciudad,codigoProvincia,telefono)
  values ('Lopez Lucas', 'San Martin 1010', 'Posadas',4,'0457858745');
 
4- Queremos saber de qué provincias no tenemos clientes:

  
5- Queremos saber de qué provincias si tenemos clientes, sin repetir el 
nombre de la provincia:

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

Otros problemas: 
A) 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- Elimine las tablas si existen.
 
2- Cree las tablas:
 
 create table socios(
  documento char(8) not null,
  nombre varchar(30),
  domicilio varchar(30),
  primary key(documento)
 );
 
 create table inscriptos(
  documento char(8) not null, 
  deporte varchar(15) not null,
  año year,
  matricula char(1), /*si esta paga ='s' sino 'n'*/
  primary key(documento,deporte,año)
 );
 
3- Ingrese algunos registros para ambas tablas:
 
 insert into socios values('22333444','Juan Perez','Colon 234');
 insert into socios values('23333444','Maria Lopez','Sarmiento 465');
 insert into socios values('24333444','Antonio Juarez','Caseros 980');
 insert into socios values('25333444','Ana Juarez','Sucre 134');
 insert into socios values('26333444','Sofia Herrero','Avellaneda 1234');
 
 insert into inscriptos values ('22333444','natacion','2005','s');
 insert into inscriptos values ('22333444','natacion','2006','n');
 insert into inscriptos values ('23333444','natacion','2005','s');
 insert into inscriptos values ('23333444','tenis','2006','s');
 insert into inscriptos values ('23333444','natacion','2006','s');
 insert into inscriptos values ('25333444','tenis','2006','n');
 insert into inscriptos values ('25333444','basquet','2006','n');
 
4- Muestre el nombre del socio, deporte y año realizando un join:
  
5- Muestre los nombres de los socios que no se han inscripto nunca en un 
deporte:

 
6- 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):
 
 
B) Un club de barrio realiza una rifa anual y guarda los datos de las rifas 
en dos tablas, una denominada "premios" y otra llamada "numerosrifa".
 
1- Elimine las tablas si existen.
 
2- Cree las tablas:
 create table premios(
  posicion tinyint unsigned auto_increment,
  premio varchar(20),
  numeroganador tinyint unsigned,
  primary key(posicion)
 );
 
 create table numerosrifa(
  numero tinyint unsigned not null,
  documento char(8) not null,
  primary key(numero)
 );
 
3- Ingrese algunos registros:
 
 insert into premios values(1,'PC Pentium',205);
 insert into premios values(2,'Televisor 21 pulgadas',29);
 insert into premios values(3,'Microondas',5);
 insert into premios values(4,'Multiprocesadora',15);
 insert into premios values(5,'Cafetera',33);
 
 insert into numerosrifa values(205,'22333444');
 insert into numerosrifa values(200,'23333444');
 insert into numerosrifa values(5,'23333444');
 insert into numerosrifa values(8,'23333444');
 insert into numerosrifa values(1,'24333444');
 insert into numerosrifa values(109,'28333444');
 insert into numerosrifa values(15,'30333444');
 insert into numerosrifa values(29,'29333444');
 insert into numerosrifa values(28,'32333444');
 
4- Muestre todos los números de rifas vendidos ("numerosrifas") y realice un 
"left join" mostrando la posición y el premio:
   
note que la posición "5" no aparece en la lista porque el número ganador de 
esa posición no fue vendido, no se encuentra en la tabla "premios". Y note 
que los números vendidos que no ganaron tiene la fila seteada a "null".
 
5- Muestre los mismos datos anteriores pero teniendo en cuenta los números 
ganadores solamente:

 
6- Realice un "left join" pero en esta ocasión busque los números ganadores 
de la tabla "premios" en la tabla "numerosrifa":

 
Note que el premio de la posición "5" no encuentra coincidencia en la tabla 
"numerosrifa" (porque no fue vendido) y el campo está seteado a "null".
 
7- Realice el mismo "join" anterior pero sin considerar los valores de 
"premios" que no encuentren coincidencia en "numerosrifa".