SQL - JOIN básico

El objetivo del artículo es mostrar básicamente como funciona la sentencia SQL JOIN que a veces cuesta entenderla.

La sentencia SQL JOIN se utiliza para relacionar varias tablas, veremos algunos ejemplos básicos de distintos tipos de JOIN.

Primero creamos dos tablas para las pruebas

Tabla clientes:

mysql> select * from clientes;
+------+--------+----------+
| cid  | nombre | telefono |
+------+--------+----------+
|    1 | jose   | 111      |
|    2 | maria  | 222      |
|    3 | manuel | 333      |
|    4 | jesus  | 4444     |
+------+--------+----------+
4 rows in set (0.00 sec)

Tabla acciones:

mysql> select * from acciones;
+-----+-----+--------+----------+
| aid | cid | accion | cantidad |
+-----+-----+--------+----------+
|   1 |   2 | REDHAT |       10 |
|   2 |   4 | NOVELL |       20 |
|   3 |   4 | SUN    |       30 |
|   4 |   5 | FORD   |      100 |
+-----+-----+--------+----------+
4 rows in set (0.00 sec)

Observaciones de las tablas:

La primer tabla contiene clientes y teléfonos y la segunda la tenencia de acciones de los clientes.

Las dos tablas contienen el campo cid (client id) que es el que nos permitirá realizar coincidencias entre ambas tablas con join.

Hay clientes que no tienen acciones (jose) y otros que tienen más de una especie (jesus).

El ultimo registro de la tabla acciones (aid=4) tiene un cid 5, si miramos la tabla cliente, no exsite un cliente con cid=5.

JOIN

El JOIN nos permitirá obtener un listado de los campos que tienen coincidencias en ambas tablas.

Osea nos dara un lista de los clientes que tienen acciones.

mysql> select nombre, telefono, accion, cantidad from clientes join acciones on clientes.cid=acciones.cid;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| maria  | 222      | REDHAT |       10 |
| jesus  | 4444     | NOVELL |       20 |
| jesus  | 4444     | SUN    |       30 |
+--------+----------+--------+----------+
3 rows in set (0.00 sec)

LEFT JOIN

La sentencia LEFT JOIN nos dará el resultado anterior mas los campos de la tabla clientes que no tienen coincidencias.

Osea un listado de todos los clientes, con sus tenencias y en el caso que no tengan acciones aparecerá NULL (como es el caso de jose).

mysql> select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| jose   | 111      | NULL   |     NULL |
| maria  | 222      | REDHAT |       10 |
| manuel | 333      | NULL   |     NULL |
| jesus  | 4444     | NOVELL |       20 |
| jesus  | 4444     | SUN    |       30 |
+--------+----------+--------+----------+
5 rows in set (0.00 sec)

RIGTH JOIN

Aquí se listarán todas las tenencias de acciones, tengan o no cliente, las que no tengan cliente se verán como NULL (como es el caso de aid=4)

mysql> select nombre, telefono, accion, cantidad from clientes right join acciones on clientes.cid=acciones.cid;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| maria  | 222      | REDHAT |       10 |
| jesus  | 4444     | NOVELL |       20 |
| jesus  | 4444     | SUN    |       30 |
| NULL   | NULL     | FORD   |      100 |
+--------+----------+--------+----------+
4 rows in set (0.00 sec)

INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN

INNER JOIN da el mismo resultado que JOIN (el primer ejemplo)

LEFT OUTER JOIN y RIGHT OUTER JOIN son iguales que LEFT JOIN y RIGHT JOIN respectivamente, se agrega OUTER luego de LEFT o RIGHT para tener compatibilidad con ODBC.

Otros ejemplos

Supongamos que queremos obtener todos los clientes que no tienen acciones.
Un LEFT JOIN no da los cliente que tienen y que no tienen acciones, para obtener solo los que no tienen acciones agregamos al LEFT JOIN un WHERE accion is NULL

mysql> select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid where accion is null;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| jose   | 111      | NULL   |     NULL |
| manuel | 333      | NULL   |     NULL |
+--------+----------+--------+----------+
2 rows in set (0.00 sec)

Para obtener aquellas tenencias de acciones que no tienen clientes, es similar al RIGHT JOIN pero le agregamos un WHERE nombre is NULL

mysql> select nombre, telefono, accion, cantidad from clientes right join acciones on clientes.cid=acciones.cid where nombre is null;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| NULL   | NULL     | FORD   |      100 |
+--------+----------+--------+----------+
1 row in set (0.00 sec)

UNION y UNION ALL

Podemos combinar el resultado de varias sentencias con UNION o UNION ALL.

UNION no nos muestra los resultados duplicados, pero UNION ALL si los muestra.

Por ejemplo queremos un listado de clientes sin tenencia de acciones y tenencia de acciones que no tienen cliente.

mysql> select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid where accion is null union select nombre, telefono, accion, cantidad from clientes right join acciones on clientes.cid=acciones.cid where nombre is null;
+--------+----------+--------+----------+
| nombre | telefono | accion | cantidad |
+--------+----------+--------+----------+
| jose   | 111      | NULL   |     NULL |
| manuel | 333      | NULL   |     NULL |
| NULL   | NULL     | FORD   |      100 |
+--------+----------+--------+----------+
3 rows in set (0.02 sec)

Imagen de mifeor
Enviado por mifeor el 12 Julio, 2010 - 01:11.

Gracias por el articulo, bien resumido y bien claro, exitos!!!

Imagen de cnicolas
Enviado por cnicolas el 12 Julio, 2010 - 07:55.

Buen articulo Ariel, si alguien esta acostumbrado a usar Oracle, la sentencia puede ser distinta, transformandola seria asi

select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid

En Oracle la forma comun es la siguiente

select nombre, telefono, accion, cantidad from clientes,  acciones where clientes.cid=acciones.cid(+)

La sentencia del left join funcionaria perfectamente pero no es la forma habitual

Imagen de MinistroPepon
Enviado por MinistroPepon el 14 Julio, 2010 - 07:59.
cnicolas escribió:

Buen articulo Ariel, si alguien esta acostumbrado a usar Oracle, la sentencia puede ser distinta, transformandola seria asi

select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid

En Oracle la forma comun es la siguiente

select nombre, telefono, accion, cantidad from clientes,  acciones where clientes.cid=acciones.cid(+)

La sentencia del left join funcionaria perfectamente pero no es la forma habitual

Pero de la segunda forma, si no me equivoco, no sacará los nulos de la segunda tabla en caso de no haber coincidencias, no?

Imagen de cnicolas
Enviado por cnicolas el 14 Julio, 2010 - 08:14.
MinistroPepon escribió:
cnicolas escribió:

Buen articulo Ariel, si alguien esta acostumbrado a usar Oracle, la sentencia puede ser distinta, transformandola seria asi

select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid

En Oracle la forma comun es la siguiente

select nombre, telefono, accion, cantidad from clientes,  acciones where clientes.cid=acciones.cid(+)

La sentencia del left join funcionaria perfectamente pero no es la forma habitual

Pero de la segunda forma, si no me equivoco, no sacará los nulos de la segunda tabla en caso de no haber coincidencias, no?

Lo he probado con otras tablas y el resultado es equivalente, anmbos casos saca todos los valores de la tabla clientes aunque no tenga relacion con la tabla acciones, si quisieramos un right join deberiamos de cambiar el (+)
de la tabla acciones a la tabla clientes. Personalmente estoy mas acostumbrado a la forma de Oracle que se que no es estandar

Imagen de MinistroPepon
Enviado por MinistroPepon el 14 Julio, 2010 - 09:19.
cnicolas escribió:
MinistroPepon escribió:
cnicolas escribió:

Buen articulo Ariel, si alguien esta acostumbrado a usar Oracle, la sentencia puede ser distinta, transformandola seria asi

select nombre, telefono, accion, cantidad from clientes left join acciones on clientes.cid=acciones.cid

En Oracle la forma comun es la siguiente

select nombre, telefono, accion, cantidad from clientes,  acciones where clientes.cid=acciones.cid(+)

La sentencia del left join funcionaria perfectamente pero no es la forma habitual

Pero de la segunda forma, si no me equivoco, no sacará los nulos de la segunda tabla en caso de no haber coincidencias, no?

Lo he probado con otras tablas y el resultado es equivalente, anmbos casos saca todos los valores de la tabla clientes aunque no tenga relacion con la tabla acciones, si quisieramos un right join deberiamos de cambiar el (+)
de la tabla acciones a la tabla clientes. Personalmente estoy mas acostumbrado a la forma de Oracle que se que no es estandar

Ahh, no sabía que el tema estaba en el "(+)"

Imagen de llantones
Enviado por llantones el 14 Febrero, 2011 - 22:45.

Hola:
Justamente lo que explicas en el blog lo he estado haciendo con la base de datos de OpenOffice. He hecho un ejemplo como el tuyo y probé todas las posibilidades de join para así no meter la pata al usar una base de datos grande. El caso es que al usar UNION para unir dos select y así conseguir el efecto de un full join, me muestra las columnas descolocadas y no encuentro ninguna razón. ¿Alguna idea?
Gracias y saludos

Imagen de makpaga
Enviado por makpaga el 16 Febrero, 2012 - 01:34.

excelente tu blog .muy buen aporte .saludos desde uruguay

Imagen de lord escobar
Enviado por lord escobar el 6 Mayo, 2013 - 19:03.

first eres unico..¡¡ tu aporte me ayudo mucho en mis inicios de los join, no entendia casi nada antes de leer esto, eres muy bueno..de verdad gracias smile