MySQL
Se une

Sintaxis

• INNER y OUTER son ignorados.

• FULL no está implementado en MySQL.

• "commajoin" ( FROM a,b WHERE ax=by ) está mal visto; use FROM a JOIN b ON ax=by lugar.

• FROM a JOIN b ON ax = by incluye filas que coinciden en ambas tablas.

• DESDE una UNIÓN IZQUIERDA b ON ax = by incluye todas las filas de a , más los datos coincidentes de b , o NULLs si no hay una fila coincidente.

Ejemplos de unión

Consulta para crear tabla en db

CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

Ya que estamos usando tablas InnoDB y sabemos que user.course y course.id están relacionados, podemos especificar una relación de clave externa:

ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;

Consulta de unión (unión interna)

SELECT user.name, course.name
FROM `user`
INNER JOIN `course` on user.course = course.id;

ÚNETE con la subconsulta (tabla "Derivado")

SELECT x, ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN tbl  ON tbl.x = a.y
    WHERE ...

Esto evaluará la subconsulta en una tabla temporal, luego JOIN a tbl .

Antes de 5.6, no podía haber un índice en la tabla temporal. Por lo tanto, esto fue potencialmente muy ineficiente:

SELECT ...
    FROM ( SELECT y, ... FROM ... ) AS a
    JOIN ( SELECT x, ... FROM ... ) AS b  ON b.x = a.y
    WHERE ...

Con 5.6, el optimizador calcula el mejor índice y lo crea sobre la marcha. (Esto tiene algo de sobrecarga, por lo que aún no es "perfecto").

Otro paradigma común es tener una subconsulta para inicializar algo:

SELECT 
        @n := @n + 1,
        ...
    FROM ( SELECT @n := 0 ) AS initialize
    JOIN the_real_table
    ORDER BY ...

(Nota: esto es técnicamente un CROSS JOIN (producto cartesiano), como lo indica la falta de ON . Sin embargo, es eficiente porque la subconsulta devuelve solo una fila que debe coincidir con las n filas en the_real_table ).

Recuperar clientes con pedidos - variaciones en un tema

Esto obtendrá todos los pedidos para todos los clientes:

SELECT c.CustomerName, o.OrderID
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    ORDER BY c.CustomerName, o.OrderID;

Esto contará el número de pedidos para cada cliente:

SELECT c.CustomerName, COUNT(*) AS 'Order Count'
    FROM Customers AS c
    INNER JOIN Orders AS o
        ON c.CustomerID = o.CustomerID
    GROUP BY c.CustomerID;
    ORDER BY c.CustomerName;

Además, cuenta, pero probablemente más rápido:

SELECT  c.CustomerName,
        ( SELECT COUNT(*) FROM Orders WHERE CustomerID = c.CustomerID ) AS 'Order Count'
    FROM Customers AS c
    ORDER BY c.CustomerName;

Listar solo el cliente con pedidos.

SELECT  c.CustomerName,
    FROM Customers AS c
    WHERE EXISTS ( SELECT * FROM Orders WHERE CustomerID = c.CustomerID )
    ORDER BY c.CustomerName;

Unión externa completa

MySQL no es compatible con FULL OUTER JOIN , pero hay formas de emular una.

Configurando los datos

-- ----------------------------
-- Table structure for `owners`
-- ----------------------------
DROP TABLE IF EXISTS `owners`;
CREATE TABLE `owners` (
`owner_id` int(11) NOT NULL AUTO_INCREMENT,
`owner` varchar(30) DEFAULT NULL,
PRIMARY KEY (`owner_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of owners
-- ----------------------------
INSERT INTO `owners` VALUES ('1', 'Ben');
INSERT INTO `owners` VALUES ('2', 'Jim');
INSERT INTO `owners` VALUES ('3', 'Harry');
INSERT INTO `owners` VALUES ('6', 'John');
INSERT INTO `owners` VALUES ('9', 'Ellie');
-- ----------------------------
-- Table structure for `tools`
-- ----------------------------
DROP TABLE IF EXISTS `tools`;
CREATE TABLE `tools` (
`tool_id` int(11) NOT NULL AUTO_INCREMENT,
`tool` varchar(30) DEFAULT NULL,
`owner_id` int(11) DEFAULT NULL,
PRIMARY KEY (`tool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of tools
-- ----------------------------
INSERT INTO `tools` VALUES ('1', 'Hammer', '9');
INSERT INTO `tools` VALUES ('2', 'Pliers', '1');
INSERT INTO `tools` VALUES ('3', 'Knife', '1');
INSERT INTO `tools` VALUES ('4', 'Chisel', '2');
INSERT INTO `tools` VALUES ('5', 'Hacksaw', '1');
INSERT INTO `tools` VALUES ('6', 'Level', null);
INSERT INTO `tools` VALUES ('7', 'Wrench', null);
INSERT INTO `tools` VALUES ('8', 'Tape Measure', '9');
INSERT INTO `tools` VALUES ('9', 'Screwdriver', null);
INSERT INTO `tools` VALUES ('10', 'Clamp', null);

¿Qué queremos ver?

Queremos obtener una lista, en la que veamos quién es el propietario de qué herramientas y qué herramientas podrían no tener un propietario.

Las consultas

Para lograr esto, podemos combinar dos consultas utilizando UNION . En esta primera consulta, uniremos las herramientas de los propietarios mediante el uso de un LEFT JOIN . Esto agregará a todos nuestros propietarios a nuestro conjunto de resultados, sin importar si realmente poseen herramientas.

En la segunda consulta, estamos utilizando un RIGHT JOIN para unir las herramientas a los propietarios. De esta manera, conseguimos obtener todas las herramientas en nuestro conjunto de resultados, si no son propiedad de nadie, su columna de propietario simplemente contendrá NULL . Al agregar la CLASE- WHERE que está filtrando por owners.owner_id IS NULL , estamos definiendo el resultado como esos conjuntos de datos, que la primera consulta aún no ha devuelto, ya que solo buscamos los datos en la tabla de la derecha.

Dado que estamos utilizando UNION ALL el conjunto de resultados de la segunda consulta se adjuntará al primer conjunto de resultados de consultas.

SELECT `owners`.`owner`, tools.tool
FROM `owners`
LEFT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
UNION ALL
SELECT `owners`.`owner`, tools.tool
FROM `owners`
RIGHT JOIN `tools` ON `owners`.`owner_id` = `tools`.`owner_id`
WHERE `owners`.`owner_id` IS NULL;

+-------+--------------+
| owner | tool         |
+-------+--------------+
| Ben   | Pliers       |
| Ben   | Knife        |
| Ben   | Hacksaw      |
| Jim   | Chisel       |
| Harry | NULL         |
| John  | NULL         |
| Ellie | Hammer       |
| Ellie | Tape Measure |
| NULL  | Level        |
| NULL  | Wrench       |
| NULL  | Screwdriver  |
| NULL  | Clamp        |
+-------+--------------+
12 rows in set (0.00 sec)

Unión interna para 3 mesas

Supongamos que tenemos tres tablas que se pueden usar para sitios web simples con etiquetas.

• La primera mesa es para postes.
• Segundo para las etiquetas
• Tercero para Tags y Post relación

primera mesa "videojuego"

tabla de "etiquetas"

tabla "tags_meta"

SELECT videogame.id,
    videogame.title,
    videogame.reg_date,
    tags.name,
    tags_meta.post_id
FROM tags_meta
INNER JOIN videogame ON videogame.id = tags_meta.post_id
INNER JOIN tags ON tags.id = tags_meta.tag_id
WHERE tags.name = "elizabeth"
ORDER BY videogame.reg_date

este código puede devolver todas las publicaciones relacionadas con esa etiqueta "#elizabeth"

Uniones visualizadas

Si eres una persona orientada visualmente, este diagrama de Venn puede ayudarte a comprender los diferentes tipos de JOIN que existen dentro de MySQL.