En la lección anterior aprendimos como insertar registros en una tabla y consultarlos. Sin embargo, cuando trabajas con bases de datos, lo más común es obtener información de varias tablas relacionadas entre sí. Una de las maneras más eficientes de lograr esto es a través del operador JOIN.
Pero antes de conocer este operador, veamos los datos de nuestra base de datos:
Sucusales:
MariaDB [storage]> SELECT
id, name, start_date
FROM
branches;
+----+------------+------------+
| id | name | start_date |
+----+------------+------------+
| 1 | SUCURSAL A | 2020-07-16 |
| 2 | SUCURSAL B | 2020-08-16 |
| 3 | SUCURSAL C | 2020-09-16 |
| 4 | SUCURSAL D | 2020-10-16 |
+----+------------+------------+
4 rows in set (0.000 sec)
Empleados:
MariaDB [storage]> SELECT
id, branch_id, dni, first_name, last_name, birth_date
FROM
employees;
+----+-----------+----------+------------+-----------+------------+
| id | branch_id | dni | first_name | last_name | birth_date |
+----+-----------+----------+------------+-----------+------------+
| 1 | NULL | 15024001 | Harry | Smith | 2000-01-01 |
| 2 | 1 | 15024002 | Jacob | Johnson | 2000-01-02 |
| 3 | 2 | 15024003 | Ryan | Gold | 2020-01-03 |
| 4 | NULL | 15024004 | Liam | Evans | 2020-01-04 |
| 5 | 1 | 15024005 | Charlie | Adams | 2020-01-05 |
| 6 | 2 | 15024006 | Amelia | Miller | 2020-01-06 |
| 7 | 1 | 15024007 | Mia | Owen | 2020-01-07 |
| 8 | NULL | 15024008 | Aurora | Graham | 2020-01-08 |
| 9 | 3 | 15024009 | Isabella | Stone | 2020-01-09 |
| 10 | 1 | 15024010 | Elizabeth | Bennett | 2020-01-10 |
+----+-----------+----------+------------+-----------+------------+
10 rows in set (0.001 sec)
Operador JOIN
La operador JOIN nos permite enlazar dos tablas por medio de un campo en común, el cual puede ser un campo clave o no.
Como resultado, obtendremos todos los registros de ambas tablas donde el campo en común tengan el mismo valor. Veamos esto con un ejemplo.
Supongamos que queremos obtener todos los empleados que están asignados a una sucursal:
MariaDB [storage]> SELECT
E.id, E.dni, E.first_name, E.last_name, E.branch_id,
B.name AS branch
FROM
employees AS E
JOIN branches AS B ON E.branch_id = B.id;
La cláusula AS nos permite asignar un «apodo» a los nombres de campos y tablas. Esto resulta muy útil para colocar un nombre alternativo a campos/tablas o evitar conflictos con nombres de campos repetidos.
El operador JOIN se puede usar con el comando SELECT después de la cláusula FROM. Va seguido del nombre de la tabla que se desea «enlazar», luego la cláusula ON, y por último se comparan los campos en común.
En otras palabras, la tabla employees se enlaza con la tabla branches por medio de los campos branch_id e id.
Los nombres de campos utilizados en las cláusulas JOIN y WHERE no pueden ser los «apodos» asignados con la cláusula AS.
La consulta anterior nos devolverá un resultado como el siguiente:
+----+----------+------------+-----------+-----------+------------+ | id | dni | first_name | last_name | branch_id | branch | +----+----------+------------+-----------+-----------+------------+ | 2 | 15024002 | Jacob | Johnson | 1 | SUCURSAL A | | 3 | 15024003 | Ryan | Gold | 2 | SUCURSAL B | | 5 | 15024005 | Charlie | Adams | 1 | SUCURSAL A | | 6 | 15024006 | Amelia | Miller | 2 | SUCURSAL B | | 7 | 15024007 | Mia | Owen | 1 | SUCURSAL A | | 9 | 15024009 | Isabella | Stone | 3 | SUCURSAL C | | 10 | 15024010 | Elizabeth | Bennett | 1 | SUCURSAL A | +----+----------+------------+-----------+-----------+------------+ 7 rows in set (0.001 sec)
El alias en la consulta colocó el apodo branch al campo name de la tabla branches
Recuerda que los datos pueden variar dependiendo de los empleados/sucursales que tengas registrados.
Nota que en la consulta hemos especificado tanto los campos, como el orden en que deseamos mostrarlos, pero ¿qué pasa si usamos el comodín (*)?
Al usar el comodín, se mostrarán todos los campos de la tabla izquierda y luego todos los campos de la tabla derecha.
La tabla izquierda (lado izquierdo) es la que se define antes del operador JOIN y la tabla derecha (lado derecho) es la que se define después del mismo.
Por ejemplo, si ejecutamos la siguiente consulta:
MariaDB [storage]> SELECT
*
FROM
employees AS E
JOIN branches AS B ON E.branch_id = B.id;
La lista de campos de nuestro resultado estará ordenada con todos los campos de la tabla employees primero y luego con todos los campos de la tabla branches.
Recuerda, te recomendamos siempre especificar el nombre de tus campos al construir consultas.
Cláusula LEFT JOIN
Al igual que el operador JOIN, la cláusula LEFT JOIN nos permite enlazar dos tablas, pero el resultado de la consulta incluirá todos los registros del lado izquierdo.
En otras palabras, la cláusula LEFT JOIN retornará todos los registros del lado izquierdo (aunque el campo en común no tenga coincidencias), y todos los registros del lado derecho donde coincida el campo en común.
Veamos un ejemplo. Vamos a repetir nuestra consulta inicial usando la cláusula LEFT JOIN
MariaDB [storage]> SELECT
E.id, E.dni, E.first_name, E.last_name, E.branch_id,
B.name AS branch
FROM
employees AS E
LEFT JOIN branches AS B ON E.branch_id = B.id;
Esto nos devolverá el siguiente resultado:
+----+----------+------------+-----------+-----------+------------+ | id | dni | first_name | last_name | branch_id | branch | +----+----------+------------+-----------+-----------+------------+ | 1 | 15024001 | Harry | Smith | NULL | NULL | | 2 | 15024002 | Jacob | Johnson | 1 | SUCURSAL A | | 3 | 15024003 | Ryan | Gold | 2 | SUCURSAL B | | 4 | 15024004 | Liam | Evans | NULL | NULL | | 5 | 15024005 | Charlie | Adams | 1 | SUCURSAL A | | 6 | 15024006 | Amelia | Miller | 2 | SUCURSAL B | | 7 | 15024007 | Mia | Owen | 1 | SUCURSAL A | | 8 | 15024008 | Aurora | Graham | NULL | NULL | | 9 | 15024009 | Isabella | Stone | 3 | SUCURSAL C | | 10 | 15024010 | Elizabeth | Bennett | 1 | SUCURSAL A | +----+----------+------------+-----------+-----------+------------+ 10 rows in set (0.001 sec)
Nota que ahora obtenemos todos los empleados, aunque no tengan sucursales asignadas. Además, dado que el campo en común tiene el valor NULL en el lado izquierdo, se completa con valores NULL los campos del lado derecho.
Esta cláusula resulta bastante útil cuando deseamos agregar a nuestros resultados los registros del lado izquierdo que no tienen coincidencias en el lado derecho. Vamos a verlo con un ejemplo.
Supongamos que nos solicitan un reporte con los empleados que no están asignados a una sucursal, pero además, este reporte debe incluir los empleados que pertenecen a sucursales cuya fecha de apertura sea desde el 01 de septiembre de 2020.
Podemos lograr esto ejecutando la siguiente consulta:
MariaDB [storage]> SELECT
E.id, E.dni, E.first_name, E.last_name, E.branch_id,
B.name AS branch, B.start_date
FROM
employees AS E
LEFT JOIN branches AS B ON E.branch_id = B.id
WHERE
E.branch_id IS NULL
OR B.start_date >= '2020-09-01';
Hemos incluido las cláusulas WHERE y OR para filtrar nuestros resultados por los empleados que no tienen sucursal (E.branch_id IS NULL) o (OR) cuya sucursal tiene fecha de apertura mayor o igual al 01 de Septiembre del 2020 (B.start_date >= '2020-09-01').
La cual nos mostrará el siguiente resultado:
+----+----------+------------+-----------+-----------+------------+-----------+ | id | dni | first_name | last_name | branch_id | branch | start_date| +----+----------+------------+-----------+-----------+------------+-----------+ | 1 | 15024001 | Harry | Smith | NULL | NULL | NULL | | 4 | 15024004 | Liam | Evans | NULL | NULL | NULL | | 8 | 15024008 | Aurora | Graham | NULL | NULL | NULL | | 9 | 15024009 | Isabella | Stone | 3 | SUCURSAL C | 2020-09-16| +----+----------+------------+-----------+-----------+------------+-----------+ 4 rows in set (0.000 sec)
Cláusula RIGHT JOIN
La cláusula RIGHT JOIN es similar a la cláusula LEFT JOIN, pues también nos permite enlazar dos tablas, pero el resultado de la consulta incluirá todos los registros del lado derecho.
Es decir, la cláusula RIGHT JOIN retornará todos los registros del lado derecho (aunque el campo en común no tenga coincidencias), y todos los registros del lado izquierdo donde coincida el campo en común.
Veamos un ejemplo. Vamos a repetir nuestra consulta inicial usando la cláusula RIGHT JOIN:
MariaDB [storage]> SELECT
E.id, E.dni, E.first_name, E.last_name, E.branch_id,
B.name AS branch
FROM
employees AS E
RIGHT JOIN branches AS B ON E.branch_id = B.id;
Esto nos devolverá el siguiente resultado:
+------+----------+------------+-----------+-----------+------------+ | id | dni | first_name | last_name | branch_id | branch | +------+----------+------------+-----------+-----------+------------+ | 2 | 15024002 | Jacob | Johnson | 1 | SUCURSAL A | | 5 | 15024005 | Charlie | Adams | 1 | SUCURSAL A | | 7 | 15024007 | Mia | Owen | 1 | SUCURSAL A | | 10 | 15024010 | Elizabeth | Bennett | 1 | SUCURSAL A | | 3 | 15024003 | Ryan | Gold | 2 | SUCURSAL B | | 6 | 15024006 | Amelia | Miller | 2 | SUCURSAL B | | 9 | 15024009 | Isabella | Stone | 3 | SUCURSAL C | | NULL | NULL | NULL | NULL | NULL | SUCURSAL D | +------+----------+------------+-----------+-----------+------------+ 8 rows in set (0.001 sec)
Nota que ahora obtenemos todas las sucursales aunque no tengan empleados, y así como con la cláusula LEFT JOIN, se completa con valores NULL los campos del lado izquierdo donde no hay coincidencias.
Veamos la utilidad de esta cláusula con el siguiente ejemplo. Supongamos que deseamos obtener todas las sucursales que no tienen empleados.
Podemos lograr esto con la siguiente consulta:
MariaDB [storage]> SELECT
B.id, B.name AS branch, B.start_date
FROM
employees AS E
RIGHT JOIN branches AS B ON E.branch_id = B.id
WHERE
E.id IS NULL;
La cual nos devolverá el siguiente resultado:
+----+------------+------------+ | id | branch | start_date | +----+------------+------------+ | 4 | SUCURSAL D | 2020-10-16 | +----+------------+------------+ 1 row in set (0.001 sec)
Puedes aprender mucho más sobre cómo construir consultas con estas cláusulas leyendo nuestro libro Aprende SQL con MySQL y MariaDB.
Regístrate hoy en Styde y obtén acceso a todo nuestro contenido.
Lección anterior Insertar y Consultar datos en MySQL / MariaDB Lección siguiente Actualizar y Eliminar datos en MySQL / MariaDB