Composición (JOINS)

La composición en SQL nos permite combinar datos de diferentes tablas para obtener información más completa y útil.

Piensa en dos tablas como estas:

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS messages;
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL
);

CREATE TABLE messages(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content TEXT NOT NULL,
    author INTEGER NOT NULL,
    FOREIGN KEY (author) REFERENCES users(id)
);

INSERT INTO users (name) VALUES 
('admin'),
('user0'),
('user1'),
('user2');

INSERT INTO messages (content, author) VALUES 
('hola mundo!', 1),
('estamos probando los mensajes', 1),
('parece que esta funcionando', 2),
('a mi me funciona', 3);

Al hacer esto:

sqlite3 users.db "SELECT * FROM messages" --table

Obtenemos lo siguiente:

+----+-------------------------------+--------+
| id |            content            | author |
+----+-------------------------------+--------+
| 1  | hola mundo!                   | 1      |
| 2  | estamos probando los mensajes | 1      |
| 3  | parece que esta funcionando   | 2      |
| 4  | a mi me funciona              | 3      |
+----+-------------------------------+--------+

Es poco útil, ya que no sabemos quién escribió cada mensaje. Para solucionarlo, podemos usar una consulta de composición (JOIN), en la que combinamos las dos tablas basándonos en la relación entre ellas (el campo author en messages y el campo id en users):

SELECT users.name, messages.content
FROM users
JOIN messages ON author = users.id;

Esto nos da el siguiente resultado:

+-------+-------------------------------+
| name  |            content            |
+-------+-------------------------------+
| admin | hola mundo!                   |
| admin | estamos probando los mensajes |
| user0 | parece que esta funcionando   |
| user1 | a mi me funciona              |
+-------+-------------------------------+

Vamos a diseccionar la sentencia:

SELECT users.name, messages.content: Aquí especificamos las columnas que queremos ver en el resultado final.
FROM users: Indicamos la tabla principal de la que queremos obtener datos, podrría ser cualquiera de las dos tablas, en este caso partimos de users.

Si solo lees estas dos sentencias, parece un SELECT normal, pero al que no podremos llegar sin hacer algo mas, ya que los mensajes se encuentran en otra tabla.

JOIN messages ON author = users.id: Aquí es donde ocurre la magia de la composición. Estamos diciendo que queremos combinar la tabla users con la tabla messages, y la condición para hacerlo es que el campo author en messages debe coincidir con el campo id en users.

Para que esto pueda funcionar, es clave que exista una relación entre las dos tablas, en este caso, la clave foránea author en la tabla messages apunta a la clave primaria id en la tabla users, si no hubiese relación entre ambas tablas, no podríamos combinarlas de manera efectiva.

Abstracto de composición

Puedes seguir la siguiente formula para crear consultas de composición:

SELECT tabla1.columnaA, tabla2.columnaB, ...
FROM tabla1
JOIN tabla2 ON tabla1.columnaX = tabla2.columnaY;

Si necesitas combinar más de dos tablas, puedes encadenar múltiples JOIN:

SELECT tabla1.columnaA, tabla2.columnaB, tabla3.columnaC, ...
FROM tabla1
JOIN tabla2 ON tabla1.columnaX = tabla2.columnaY
JOIN tabla3 ON tabla2.columnaZ = tabla3.columnaW;

Tipos de JOIN

Hasta ahora, el tipo de JOIN que hemos visto es un INNER JOIN, que devuelve solo las filas que tienen coincidencias en ambas tablas. Existen otros tipos de JOIN que pueden ser útiles en diferentes situaciones:

LEFT JOIN

Un LEFT JOIN devuelve todas las filas de la tabla de la izquierda (la primera tabla mencionada), y las filas coincidentes de la tabla de la derecha. Si no hay coincidencia, los resultados de la tabla de la derecha serán NULL.

SELECT users.name, messages.content
FROM users
LEFT JOIN messages ON author = users.id;

Esto devuelve:

+-------+-------------------------------+
| name  |            content            |
+-------+-------------------------------+
| admin | estamos probando los mensajes |
| admin | hola mundo!                   |
| user0 | parece que esta funcionando   |
| user1 | a mi me funciona              |
| user2 |                               |
+-------+-------------------------------+

El user2 no tiene mensajes, pero aún así aparece en el resultado con un valor NULL en la columna content.

RIGHT JOIN

Un RIGHT JOIN es similar al LEFT JOIN, pero devuelve todas las filas de la tabla de la derecha y las filas coincidentes de la tabla de la izquierda. Si no hay coincidencia, los resultados de la tabla de la izquierda serán NULL.

SELECT users.name, messages.content
FROM users
RIGHT JOIN messages ON author = users.id;

Esto devuelve:

+-------+-------------------------------+
| name  |            content            |
+-------+-------------------------------+
| admin | hola mundo!                   |
| admin | estamos probando los mensajes |
| user0 | parece que esta funcionando   |
| user1 | a mi me funciona              |
+-------+-------------------------------+

En este caso no hay filas en messages sin un autor correspondiente en users, por lo que el resultado es el mismo que con un INNER JOIN, si nuestro schema lo permitiera, podríamos ver filas con NULL en la columna name.

JOIN con ALIAS

Para hacer nuestras consultas más legibles, especialmente cuando trabajamos con múltiples tablas, podemos usar alias para las tablas. Un alias es un nombre temporal que le damos a una tabla en nuestra consulta.

SELECT u.name, m.content
FROM users AS u
JOIN messages AS m ON m.author = u.id;