Lecciones de PHP - Lección 3.6 - Trabajo con bases de datos MySQL. Tipos del operador JOIN.
En MySQL, se pueden realizar consultas con JOIN de diferentes maneras. Trataremos de examinar todos estos tipos de consultas. Aquí está la lista de todas las consultas que involucran JOIN:
- INNER JOIN
- LEFT JOIN
- LEFT JOIN sin intersección con la tabla derecha
- RIGHT JOIN
- RIGHT JOIN sin intersección con la tabla izquierda
- FULL OUTER
- FULL OUTER donde la tabla izquierda o derecha está vacía
Aquí hay una ilustración de estos tipos de JOIN:
Adjuntaré archivos a este artículo, entre los cuales habrá un archivo join.php en el que mostraré todos los registros usando diferentes operadores JOIN.
INNER JOIN
Comenzamos con el operador INNER JOIN, porque este se ejecuta por defecto si simplemente escribes JOIN en la consulta. Este operador selecciona todos los registros de dos tablas donde se cumple la condición que sigue a la cláusula ON. Tenemos dos tablas: Files y Messages:
Tabla Messages:
| mid | bodytext | fid |
| 1 | Test | NULL |
| 2 | Hi | 2 |
| 3 | Hello | NULL |
Tabla Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
La consulta JOIN sería la siguiente:
SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid
El resultado será:
| mid | bodytext | fid | path |
| 2 | Hi | 2 | /files/2.png |
Es decir, donde el fid coincide, MySQL devuelve esas filas.
LEFT JOIN
Con LEFT JOIN mostramos todos los registros de la tabla izquierda (en este caso Messages), incluyendo aquellos registros donde el valor de fid existe o no en la tabla Files.
Tabla Messages:
| mid | bodytext | fid |
| 1 | Test | 2 |
| 2 | Hi | NULL |
| 3 | Hello | 3 |
Tabla Files:
| fid | path |
| 1 | /files/1.png |
| 2 | /files/2.png |
| 3 | /files/3.png |
La consulta con LEFT JOIN sería:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid
El resultado será:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
LEFT JOIN es útil cuando queremos mostrar todos los mensajes, independientemente de si tienen archivos adjuntos o no (lo cual se puede verificar luego en PHP).
LEFT JOIN sin intersección con la tabla derecha
LEFT JOIN sin intersección muestra todos los registros de la tabla izquierda que no tienen coincidencia en la tabla derecha.
Consulta:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL
Resultado:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
Este tipo de JOIN es útil cuando queremos mostrar solo los mensajes que no tienen archivos adjuntos.
RIGHT JOIN
RIGHT JOIN devuelve todos los registros de la tabla derecha, y los registros coincidentes de la izquierda si existen.
Consulta:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid
Resultado:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
| 1 | Test | 2 | /files/2.png |
| 3 | Hello | 3 | /files/3.png |
RIGHT JOIN es útil cuando necesitamos ver todos los archivos, estén o no asociados con algún mensaje.
RIGHT JOIN sin intersección
Muestra todos los registros de la tabla derecha que no tienen correspondencia en la tabla izquierda.
Consulta:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL
Resultado:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
Esto es útil para listar todos los archivos que no han sido usados en ningún mensaje.
FULL OUTER JOIN
Aunque SQL define FULL OUTER JOIN, MySQL no lo soporta directamente. Se puede emular combinando LEFT JOIN y RIGHT JOIN con UNION.
Consulta emulada:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid
Resultado:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
| NULL | NULL | 1 | /files/1.png |
Este JOIN puede ser útil si necesitamos combinar completamente ambas tablas, incluso sus valores no coincidentes.
FULL OUTER JOIN sin intersección
También se puede emular un FULL OUTER JOIN que solo muestra los registros no coincidentes de ambas tablas.
Consulta:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL
Resultado:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
| NULL | NULL | 1 | /files/1.png |
Y con eso, terminamos. En las próximas lecciones comenzaremos a trabajar con consultas aún más complejas a múltiples tablas al mismo tiempo.