Cómo usar uniones en SQL

Introducción

Muchos diseños de bases de datos separan la información en diferentes tablas según las relaciones entre puntos de datos específicos. Incluso en casos como este, es probable que alguien quiera recuperar información de más de una tabla a la vez.

Una forma común de acceder a datos de varias tablas en una operación de Lenguaje de Consulta Estructurado (SQL) es combinar tablas con una cláusula JOIN. Basándose en la operación de unión del álgebra relacional, una cláusula JOIN combina tablas independientes haciendo coincidir las filas de cada tabla que estén relacionadas entre sí. Normalmente, esta relación se basa en un par de columnas (una de cada tabla) que comparten valores, como la clave externa de una tabla y la clave principal de otra tabla a la que hace referencia la clave externa.

Esta guía explica cómo crear diversas consultas SQL que incluyen una cláusula JOIN. También muestra los diferentes tipos de cláusulas JOIN, cómo combinar datos de varias tablas y cómo crear alias en columnas para simplificar la escritura de operaciones JOIN.

Requisitos previos

Para seguir esta guía, necesitará una computadora con algún tipo de sistema de gestión de bases de datos relacionales (SGBDR) que utilice SQL. Las instrucciones y ejemplos de esta guía se han verificado en el siguiente entorno:

• Un servidor que ejecuta Ubuntu 20.04, con un usuario no root con privilegios administrativos y un firewall configurado con UFW
• MySQL está instalado y protegido en el servidor.
• También necesitará una base de datos con algunas tablas cargadas con datos de ejemplo que pueda usar para practicar las operaciones JOIN. Le recomendamos consultar la sección "Conectarse a MySQL y configurar una base de datos de ejemplo" a continuación para obtener más información sobre cómo conectarse al servidor MySQL y crear la base de datos de prueba utilizada en los ejemplos de esta guía.

Conexión a MySQL y configuración de una base de datos de ejemplo

Si su sistema de base de datos SQL se ejecuta en un servidor remoto, conéctese a su servidor mediante SSH desde su máquina local:

ssh sammy@your_server_ip

A continuación, abre la consola del servidor MySQL y sustituye Sami por tu nombre de usuario de MySQL:

mysql -u sammy -p

Crea una base de datos llamada joinsDB:

CREATE DATABASE joinsDB;

Si la base de datos se crea correctamente, recibirá el siguiente resultado:

Output
Query OK, 1 row affected (0.01 sec)

Para seleccionar la base de datos joinsDB, ejecute la siguiente declaración USE:

USE joinsDB;

Output
Database changed

Tras seleccionar joinsDB, cree varias tablas dentro de ella. Para los ejemplos de esta guía, imagine que dirige una fábrica y ha decidido registrar la información sobre su línea de producción, el equipo de ventas y las ventas de su empresa en una base de datos SQL. Comenzará con tres tablas, la primera de las cuales almacenará información sobre sus productos. Decide que esta primera tabla necesita tres columnas:

• ID de producto: El número de identificación de cada producto, expresado con el tipo de dato int. Esta columna actúa como clave principal de la tabla, lo que significa que cada valor actúa como un identificador único para la fila correspondiente. Dado que cada valor de una clave principal debe ser único, esta columna también tiene aplicada la restricción UNIQUE.
• productName: El nombre de cada producto se expresa utilizando el tipo de datos varchar con un máximo de 20 caracteres.
• Precio: El precio de cada producto, expresado en decimal. Esto especifica que cada valor de esta columna tiene un límite máximo de cuatro dígitos, dos de los cuales se ubican a la derecha de la coma decimal. Por lo tanto, el rango de valores permitidos en esta columna es de -99,99 a 99,99.

Cree una tabla llamada productos que tenga estas tres columnas:

CREATE TABLE products (
productID int UNIQUE,
productName varchar(20),
price decimal (4,2),
PRIMARY KEY (productID)
);

La segunda tabla almacena información sobre los empleados del equipo de ventas de su empresa. Usted decide que esta tabla también necesita tres columnas:

• empID: Similar a la columna productID, esta columna contiene un número de identificación único para cada empleado del equipo de ventas, expresado como un tipo de dato int. Por lo tanto, esta columna tendrá una restricción UNIQUE y actuará como clave principal de la tabla team.
• empName: El nombre de cada vendedor expresado utilizando el tipo de datos varchar con un máximo de 20 caracteres.
• ProductSpecialty: A cada miembro de tu equipo de ventas se le asigna un producto como especialidad. Pueden vender cualquier producto de tu empresa, pero su enfoque principal se centra en el producto en el que se especializan. Para representar esto en la tabla, crea esta columna que contiene el valor de productID de cada producto en el que se especializa cada empleado.

Para garantizar que la columna productSpecialty contenga únicamente valores que representen números de ID de producto válidos, decide aplicar una restricción de clave externa a la columna que hace referencia a la columna productID de la tabla Products. Una restricción de clave externa es una forma de expresar una relación entre dos tablas que exige que los valores de la columna a la que se aplica existan en la columna a la que hace referencia. En la siguiente instrucción CREATE TABLE, la restricción FOREIGN KEY exige que cualquier valor añadido a la columna productSpecialty de la tabla team exista en la columna productID de la tabla Products.

Crea una tabla llamada Equipo con estas tres columnas:

CREATE TABLE team (
empID int UNIQUE,
empName varchar(20),
productSpecialty int,
PRIMARY KEY (empID),
FOREIGN KEY (productSpecialty) REFERENCES products (productID)
);

La última tabla que cree contendrá los registros de ventas de la empresa. Esta tabla tendrá cuatro columnas:

• ID de venta: Al igual que las columnas ID de producto y ID de emp, esta columna contiene un número de identificación único para cada venta, expresado como un tipo de dato entero. Esta columna también tiene una restricción UNIQUE para que pueda actuar como clave principal de la tabla de ventas.
• Cantidad: El número de unidades vendidas de cada producto se expresa con el tipo de dato int.
• ID del producto: El número de identificación del producto vendido, expresado como un int.
• Vendedor: Número de identificación del empleado que realizó la venta.

Al igual que con la columna productSpecialty de la tabla team, decide aplicar restricciones FOREIGN KEY a las columnas productID y salesperson. Esto garantiza que estas columnas solo contengan valores presentes en las columnas productID de la tabla products y empID de la tabla team, respectivamente.

Crea una tabla llamada Ventas con estas cuatro columnas:

CREATE TABLE sales (
saleID int UNIQUE,
quantity int,
productID int,
salesperson int,
PRIMARY KEY (saleID),
FOREIGN KEY (productID) REFERENCES products (productID),
FOREIGN KEY (salesperson) REFERENCES team (empID)
);

Luego, cargue la tabla Productos con datos de muestra ejecutando la siguiente operación INSERT INTO:

INSERT INTO products
VALUES
(1, 'widget', 18.99),
(2, 'gizmo', 14.49),
(3, 'thingamajig', 39.99),
(4, 'doodad', 11.50),
(5, 'whatzit', 29.99);

A continuación, cargue la tabla del equipo con datos de muestra:

INSERT INTO team
VALUES
(1, 'Florence', 1),
(2, 'Mary', 4),
(3, 'Diana', 3),
(4, 'Betty', 2);

Cargue también la tabla de ventas con datos de muestra:

INSERT INTO sales
VALUES
(1, 7, 1, 1),
(2, 10, 5, 4),
(3, 8, 2, 4),
(4, 1, 3, 3),
(5, 5, 1, 3);

Finalmente, imagine que su empresa realiza algunas ventas sin la intervención de ningún miembro del equipo de ventas. Para registrarlas, ejecute la siguiente operación para agregar tres filas a la tabla Ventas que no tengan un valor en la columna Vendedor:

INSERT INTO sales (saleID, quantity, productID)
VALUES
(6, 1, 5),
(7, 3, 1),
(8, 4, 5);

Una vez hecho esto, estará listo para seguir el resto de la guía y comenzar a aprender cómo unir tablas en SQL.

Comprender la sintaxis de la operación JOIN

Las cláusulas JOIN se pueden usar en diversas sentencias SQL, incluyendo operaciones UPDATE y DELETE. Sin embargo, a modo de ejemplo, los ejemplos de esta guía utilizan consultas SELECT para demostrar su funcionamiento.

El siguiente ejemplo muestra la sintaxis general de una declaración SELECT que incluye una cláusula JOIN:

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
ON search_condition;

Esta sintaxis comienza con una instrucción SELECT que devuelve dos columnas de dos tablas distintas. Tenga en cuenta que, dado que las cláusulas JOIN comparan el contenido de más de una tabla, esta sintaxis de ejemplo especifica de qué tabla se seleccionará cada columna, precediendo el nombre de la columna con el nombre de la tabla y un punto. Esto se conoce como una referencia de columna completa.

Puede usar referencias de columna completas como estas en cualquier operación, pero técnicamente solo es necesario en operaciones donde dos columnas de tablas diferentes tienen el mismo nombre. Sin embargo, es recomendable usarlas al trabajar con varias tablas, ya que pueden facilitar la lectura y la comprensión de las operaciones JOIN.

Después de SELECT viene la cláusula FROM. En cualquier consulta, la cláusula FROM define el conjunto de datos que se debe buscar para obtener los datos deseados. La única diferencia es que la cláusula FROM consta de dos tablas separadas por la palabra clave JOIN. Una forma útil de planificar las consultas es recordar seleccionar las columnas que se devolverán de la tabla que se desea consultar.

Luego, existe una cláusula ON que explica cómo la consulta debe unir las dos tablas mediante la definición de una condición de búsqueda. Una condición de búsqueda es un conjunto de una o más declaraciones o expresiones que pueden evaluarse como "verdadero", "falso" o "desconocido" para una condición específica. Puede ser útil considerar la operación JOIN como la combinación de todas las filas de ambas tablas y la devolución de todas las filas para las que la condición de búsqueda de la cláusula ON se evalúa como "verdadero".

En una cláusula ON, suele ser conveniente incluir una condición de búsqueda que compruebe si dos columnas relacionadas (como la clave externa de una tabla y la clave principal de otra tabla a la que hace referencia la clave externa) tienen valores iguales. Esto se conoce a veces como una unión equitativa.

Como ejemplo de cómo equi puede unir datos coincidentes de varias tablas, ejecute la siguiente consulta con los datos de ejemplo que agregó anteriormente. Esto une las tablas Productos y Equipo con una condición de búsqueda que busca valores coincidentes en sus respectivas columnas ID de producto y Especialidad de producto. A continuación, devuelve el nombre de cada miembro del equipo de ventas, el nombre de cada producto en el que se especializa y el precio de dichos productos:

SELECT team.empName, products.productName, products.price
FROM products JOIN team
ON products.productID = team.productSpecialty

Output
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

JOIN Process Example
+-----------+-------------+-------+
| productID | productName | price |
+-----------+-------------+-------+
| 1 | widget | 18.99 |
| 2 | gizmo | 14.49 |
| 3 | thingamajig | 39.99 |
| 4 | doodad | 11.50 |
| 5 | whatzit | 29.99 |
+-----------+-------------+-------+

JOIN Process Example
+-----------+-------------+-------+-------+----------+------------------+
| productID | productName | price | empID | empName | productSpecialty |
+-----------+-------------+-------+-------+----------+------------------+
| 1 | widget | 18.99 | 1 | Florence | 1 |
| 2 | gizmo | 14.49 | 4 | Betty | 2 |
| 3 | thingamajig | 39.99 | 3 | Diana | 3 |
| 4 | doodad | 11.50 | 2 | Mary | 4 |
| 5 | whatzit | 29.99 | | | |
+-----------+-------------+-------+-------+----------+------------------+

JOIN Process Example
+----------+-------------+-------+
| empName | productName | price |
+----------+-------------+-------+
| Florence | widget | 18.99 |
| Mary | doodad | 11.50 |
| Diana | thingamajig | 39.99 |
| Betty | gizmo | 14.49 |
+----------+-------------+-------+
4 rows in set (0.00 sec)

SELECT table1.column1, table2.column2
FROM table1 JOIN table2
USING (related_column);

SELECT sales.saleID, sales.quantity, products.productName, products.price
FROM sales JOIN products
USING (productID)
ORDER BY saleID;

Output
+--------+----------+-------------+-------+
| saleID | quantity | productName | price |
+--------+----------+-------------+-------+
| 1 | 7 | widget | 18.99 |
| 2 | 10 | whatzit | 29.99 |
| 3 | 8 | gizmo | 14.49 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 18.99 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 18.99 |
| 8 | 4 | whatzit | 29.99 |
+--------+----------+-------------+-------+
8 rows in set (0.00 sec)

SELECT table1.column1, table2.column2, table3.column3
FROM table1 JOIN table2
ON table1.related_column = table2.related_column
JOIN table3
ON table3.related_column = table1_or_2.related_column;

SELECT sales.saleID,
team.empName,
products.productName,
(sales.quantity * products.price)
FROM products JOIN sales
USING (productID)
JOIN team
ON team.productSpecialty = sales.productID
WHERE team.empID = sales.salesperson
ORDER BY sales.saleID;

Output
+--------+----------+-------------+-----------------------------------+
| saleID | empName | productName | (sales.quantity * products.price) |
+--------+----------+-------------+-----------------------------------+
| 1 | Florence | widget | 132.93 |
| 3 | Betty | gizmo | 115.92 |
| 4 | Diana | thingamajig | 39.99 |
+--------+----------+-------------+-----------------------------------+
3 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName 
FROM sales JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
5 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales LEFT OUTER JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 6 | 1 | NULL | NULL |
| 7 | 3 | NULL | NULL |
| 8 | 4 | NULL | NULL |
+--------+----------+-------------+----------+
8 rows in set (0.00 sec)

SELECT sales.saleID, sales.quantity, sales.salesperson, team.empName
FROM sales RIGHT JOIN team
ON sales.salesperson = team.empID;

Output
+--------+----------+-------------+----------+
| saleID | quantity | salesperson | empName |
+--------+----------+-------------+----------+
| 1 | 7 | 1 | Florence |
| NULL | NULL | NULL | Mary |
| 4 | 1 | 3 | Diana |
| 5 | 5 | 3 | Diana |
| 2 | 10 | 4 | Betty |
| 3 | 8 | 4 | Betty |
+--------+----------+-------------+----------+
6 rows in set (0.00 sec)

SELECT t1.column1, t2.column2
FROM table1 AS t1 JOIN table2 AS t2
ON t1.related_column = t2.related_column;

SELECT S.saleID, S.quantity,
P.productName,
(P.price * S.quantity) AS revenue 
FROM sales AS S JOIN products AS P
USING (productID);

Output
+--------+----------+-------------+---------+
| saleID | quantity | productName | revenue |
+--------+----------+-------------+---------+
| 1 | 7 | widget | 132.93 |
| 2 | 10 | whatzit | 299.90 |
| 3 | 8 | gizmo | 115.92 |
| 4 | 1 | thingamajig | 39.99 |
| 5 | 5 | widget | 94.95 |
| 6 | 1 | whatzit | 29.99 |
| 7 | 3 | widget | 56.97 |
| 8 | 4 | whatzit | 119.96 |
+--------+----------+-------------+---------+
8 rows in set (0.00 sec)

SELECT S.saleID, S.quantity, P.productName, (P.price * S.quantity) revenue 
FROM sales S JOIN products P
USING (productID);