orto-grafía

2009-08-13T15:10:00.000-07:00

Será que el oráculo le recortó el presupuesto al sol y a este no le alcanzó para implementar el corrector?

Another MySQL Full Join

2009-08-07T15:05:00.000-07:00

Hace algún tiempo atrás, tuve la necesidad de hacer un listado del cual necesitaba mostrar todos los nulos de ambas tablas en relación a un campo en particular, entonces dije "bien, Full Join, ahora te toca."

Pero no fue tan fácil. Viendo la documentación de mi MySQL, no encontré nada con respecto a Full Joins. Googleando un poco me dí cuenta de que MySQL no soportaba Full Joins.. Así, de una manera nada elegante, pude llegar a una solución (para ese listado en particular).

Nuevamente buscando en google (a ver si me topaba algo más elegante) encontré varios posts al respecto. No era el único desesperado buscando Full Joins en Mysql.. Pero los que encontré, apuntaban a la misma solución que había armado primeramente (Incluso en el sitio de mysql tech resources apuntan esta misma solución):

http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html

Artículo: Joins, Temporary Tables, and Transactions

En otro blog encontré que alguien había hecho lo mismo:
http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql

Todos apuntaban a la misma solución:

SELECT a
LEFT JOIN b
UNION ALL
SELECT a
RIGHT JOIN b

Hace unos días me surgió la necesidad del Full Join nuevamente. Estaba con tres inventarios distintos (de tres personas distintas) y quise "unificar" de alguna manera estos, y pasar este nuevo inventario unificado a cada parte. Pero había recordado lo frustrante que me resultó la experiencia anterior, por lo que, cuando estaba por desistir, se me ocurrió probar una última vez, a lo que llegué a un nuevo query.

Lo que volví a reescribir (no sé si es más efectivo o más agradable estéticamente) me resultó de gran utilidad cuando necesito un Full Join con más de dos tablas! (Todos los ejemplos anteriores mostraban la solución con dos tablas, pero cuando eran más de dos.. uff..)

Bueno, comencemos.

Tengo las tres tablas:


CREATE TABLE `one` (
`idone` tinyint unsigned NOT NULL auto_increment,
`host` varchar(16) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY  (`idone`)
)
;

CREATE TABLE `two` (
`idtwo` tinyint unsigned NOT NULL auto_increment,
`host` varchar(16) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY  (`idtwo`)
)
;

CREATE TABLE `three` (
`idthree` tinyint unsigned NOT NULL auto_increment,
`host` varchar(16) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY  (`idthree`)
)
;

Cargamos los datos


INSERT INTO one (host, ip) VALUES ('linux01', '1.2.3.4');
INSERT INTO one (host, ip) VALUES ('osx2', '1.1.1.1');
INSERT INTO one (host, ip) VALUES ('as400', '-');
INSERT INTO one (host, ip) VALUES ('solaris', '1.1.1.100');

INSERT INTO two (host, ip) VALUES ('linux-01', '-');
INSERT INTO two (host, ip) VALUES ('osx2', '1.1.1.1');
INSERT INTO two (host, ip) VALUES ('win-fw', '1.2.3.100');
INSERT INTO two (host, ip) VALUES ('solaris', '1.1.1.100');

INSERT INTO three (host, ip) VALUES ('win-fw', '1.2.2.100');
INSERT INTO three (host, ip) VALUES ('solaris', '1.1.1.100');
INSERT INTO three (host, ip) VALUES ('osx2', '1.1.1.100');
INSERT INTO three (host, ip) VALUES ('linux01', '1.3.2.4');

Como se ven las tablas


one
+----+---------+-----------+
| id | host    | ip        |
+----+---------+-----------+
|  1 | linux01 | 1.2.3.4   |
|  2 | osx2    | 1.1.1.1   |
|  3 | as400   | -         |
|  4 | solaris | 1.1.1.100 |
+----+---------+-----------+

two
+----+----------+-----------+
| id | host     | ip        |
+----+----------+-----------+
|  1 | linux-01 | -         |
|  2 | osx2     | 1.1.1.1   |
|  3 | win-fw   | 1.2.3.100 |
|  4 | solaris  | 1.1.1.100 |
+----+----------+-----------+

three
+----+---------+-----------+
| id | host    | ip        |
+----+---------+-----------+
|  1 | win-fw  | 1.2.2.100 |
|  2 | solaris | 1.1.1.100 |
|  3 | osx2    | 1.1.1.100 |
|  4 | linux01 | 1.3.2.4   |
+----+---------+-----------+

La idea es tener un solo listado de hosts. Para mi caso, quise tenerlo unificado por nombre. Uniendo las tres tablas, todos los hosts que tenemos en total son:


SELECT DISTINCT a.host
FROM (
 SELECT idone AS id, host, ip
 FROM one
 UNION ALL
 SELECT idtwo AS id, host, ip
 FROM two
 UNION ALL
 SELECT idthree AS id, host, ip
 FROM three
) a
ORDER BY a.host
;

+----------+
| host     |
+----------+
| as400    |
| linux-01 |
| linux01  |
| osx2     |
| solaris  |
| win-fw   |
+----------+

Lo único que necesitamos hacer es LEFT JOINS a este query! De nuevo, colocamos este query en un FROM en forma de subselect:


SELECT b.host,
one.host AS one, one.ip,
two.host AS two, two.ip,
three.host AS three, three.ip
FROM (
SELECT DISTINCT a.host AS host
FROM (
   SELECT idone AS id, host, ip
   FROM one
   UNION ALL
   SELECT idtwo AS id, host, ip
   FROM two
   UNION ALL
   SELECT idthree AS id, host, ip
   FROM three
) a ORDER BY a.host
) b
LEFT JOIN one ON one.host=b.host
LEFT JOIN two ON two.host=b.host
LEFT JOIN three ON three.host=b.host


+----------+---------+-----------+----------+-----------+---------+-----------+
| host     | one     | ip        | two      | ip        | three   | ip        |
+----------+---------+-----------+----------+-----------+---------+-----------+
| as400    | as400   | -         | NULL     | NULL      | NULL    | NULL      |
| linux-01 | NULL    | NULL      | linux-01 | -         | NULL    | NULL      |
| linux01  | linux01 | 1.2.3.4   | NULL     | NULL      | linux01 | 1.3.2.4   |
| osx2     | osx2    | 1.1.1.1   | osx2     | 1.1.1.1   | osx2    | 1.1.1.100 |
| solaris  | solaris | 1.1.1.100 | solaris  | 1.1.1.100 | solaris | 1.1.1.100 |
| win-fw   | NULL    | NULL      | win-fw   | 1.2.3.100 | win-fw  | 1.2.2.100 |
+----------+---------+-----------+----------+-----------+---------+-----------+

Y el listado sale por sí solo.

Si necesitáramos agregar otra tabla más al listado, tan solo bastaría con agregar las columnas, una unión más y un LEFT JOIN más al final..


SELECT b.host,
one.host AS one, one.ip,
two.host AS two, two.ip,
three.host AS three, three.ip,
...

FROM (
SELECT DISTINCT a.host AS host
FROM (
   SELECT idone AS id, host, ip
   FROM one
   UNION ALL
   SELECT idtwo AS id, host, ip
   FROM two
   UNION ALL
   SELECT idthree AS id, host, ip
   FROM three
   UNION ALL
   SELECT idfour AS id, host, ip
   FROM four
    ...
    ..
    .

) a ORDER BY a.host
) b
LEFT JOIN one ON one.host=b.host
LEFT JOIN two ON two.host=b.host
LEFT JOIN three ON three.host=b.host
LEFT JOIN four ON four.host=b.host
...

Probamos agregar una tabla más:


CREATE TABLE `four` (
`idfour` tinyint unsigned NOT NULL auto_increment,
`host` varchar(16) NOT NULL,
`ip` varchar(16) NOT NULL,
PRIMARY KEY  (`idfour`)
)
;

INSERT INTO four (host, ip) VALUES ('solaris', '1.1.1.100');
INSERT INTO four (host, ip) VALUES ('linux', '1.1.2.2');
INSERT INTO four (host, ip) VALUES ('os400', '100.1.1.100');
INSERT INTO four (host, ip) VALUES ('windows', '1.2.2.100');

Y el query queda:


SELECT b.host,
one.host AS one, one.ip,
two.host AS two, two.ip,
three.host AS three, three.ip,
four.host AS four, four.ip
FROM (
SELECT DISTINCT a.host AS host
FROM (
   SELECT idone AS id, host, ip
   FROM one
   UNION ALL
   SELECT idtwo AS id, host, ip
   FROM two
   UNION ALL
   SELECT idthree AS id, host, ip
   FROM three
   UNION ALL
   SELECT idfour AS id, host, ip
   FROM four
) a ORDER BY a.host
) b
LEFT JOIN one ON one.host=b.host
LEFT JOIN two ON two.host=b.host
LEFT JOIN three ON three.host=b.host
LEFT JOIN four ON four.host=b.host

Ahora podemos ver la diferencia entre las cuatro tablas de inventarios de hosts, y en qué difiere cada una:


+----------+---------+-----------+----------+-----------+---------+-----------+---------+-------------+
| host     | one     | ip        | two      | ip        | three   | ip        | four    | ip          |
+----------+---------+-----------+----------+-----------+---------+-----------+---------+-------------+
| as400    | as400   | -         | NULL     | NULL      | NULL    | NULL      | NULL    | NULL        |
| linux    | NULL    | NULL      | NULL     | NULL      | NULL    | NULL      | linux   | 1.1.2.2     |
| linux-01 | NULL    | NULL      | linux-01 | -         | NULL    | NULL      | NULL    | NULL        |
| linux01  | linux01 | 1.2.3.4   | NULL     | NULL      | linux01 | 1.3.2.4   | NULL    | NULL        |
| os400    | NULL    | NULL      | NULL     | NULL      | NULL    | NULL      | os400   | 100.1.1.100 |
| osx2     | osx2    | 1.1.1.1   | osx2     | 1.1.1.1   | osx2    | 1.1.1.100 | NULL    | NULL        |
| solaris  | solaris | 1.1.1.100 | solaris  | 1.1.1.100 | solaris | 1.1.1.100 | solaris | 1.1.1.100   |
| win-fw   | NULL    | NULL      | win-fw   | 1.2.3.100 | win-fw  | 1.2.2.100 | NULL    | NULL        |
| windows  | NULL    | NULL      | NULL     | NULL      | NULL    | NULL      | windows | 1.2.2.100   |
+----------+---------+-----------+----------+-----------+---------+-----------+---------+-------------+

edward.

A revolution from my mind

orto-grafía

Another MySQL Full Join