Sintaxis de JOIN

Sintaxis de JOIN

MySQL soporta las siguientes sintaxis de JOIN para la parte table_references de comandos SELECT y DELETE y UPDATE de múltiples tablas:

table_reference, table_reference

table_reference [INNER | CROSS] JOIN table_reference [join_condition]

table_reference STRAIGHT_JOIN table_reference

table_reference LEFT [OUTER] JOIN table_reference join_condition

table_reference NATURAL [LEFT [OUTER]] JOIN table_reference

{ ON table_reference LEFT OUTER JOIN table_reference

ON conditional_expr }

table_reference RIGHT [OUTER] JOIN table_reference join_condition

table_reference NATURAL [RIGHT [OUTER]] JOIN table_reference

table_reference se define como:

tbl_name [[AS] alias]

[[USE INDEX (key_list)]

| [IGNORE INDEX (key_list)]

| [FORCE INDEX (key_list)]]

join_condition se define como:

ON conditional_expr | USING (column_list)

Generalmente no debería tener ninguna condición en la parte ON que se usa para restringir qué registros desea en el conjunto de resultados, pero en su lugar especificar esas condiciones en la cláusula WHERE . Hay excepciones a esta regla.

La sintaxis { OJ ... LEFT OUTER JOIN ...} mostrada en la lista precedente existe sólo por compatibilidad con ODBC.

• Puede poner un alias en una referencia de tabla usando tbl_name AS alias_name o tbl_name alias_name:

• mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2

• -> WHERE t1.name = t2.name;

• mysql> SELECT t1.name, t2.salary FROM employee t1, info t2

• -> WHERE t1.name = t2.name;

• El condicional ON es cualquier expresión condicional de la forma que puede usarse en una cláusula WHERE .

• Si no hay ningún registro coincidiente para la tabla de la derecha en la parte ON o USING en un LEFT JOIN, se usa un registro con todos las columnas a NULL para la tabla de la derecha. Puede usar este hecho para encontrar registros en una tabla que no tengan contraparte en otra tabla:

• mysql> SELECT table1.* FROM table1

• -> LEFT JOIN table2 ON table1.id=table2.id

• -> WHERE table2.id IS NULL;

Este ejemplo encuentra todos los registros en table1 con un valor id no presente en table2 (esto es, todos los registros en table1 sin registro correspondiente en table2). Esto asume que table2.id se declara NOT NULL. Consulte Sección 7.2.9, “Cómo optimiza MySQL los LEFT JOIN y RIGHT JOIN”.

• La cláusula USING (column_list) muestra una lista de columnas que deben existir en ambas tablas. Las siguientes dos cláusulas son semánticamente idénticas:

• a LEFT JOIN b USING (c1,c2,c3)

• a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3

• El NATURAL [LEFT] JOIN de dos tablas se define semánticamente equivalente a un INNER JOIN o LEFT JOIN con una cláusula USING que nombra todas las columnas que existen en ambas tablas.

• INNER JOIN y , (coma) son semánticamente equivalentes en la ausencia de una condicicón de join: ambos producen un producto Cartesiano entre las tablas especificadas (esto es, cada registro en la primera tabla se junta con cada registro en la segunda tabla).

• RIGHT JOIN funciona análogamente a LEFT JOIN. Para mantener el código portable entre bases de datos, se recomienda que use LEFT JOIN en lugar de RIGHT JOIN.

• STRAIGHT_JOIN es idéntico a JOIN, excepto que la tabla de la izquierda se lee siempre antes que la de la derecha. Esto puede usarse para aquéllos casos (escasos) en que el optimizador de join pone las tablas en orden incorrecto.

Puede proporcionar pistas de qué índice debe usar MySQL cuando recibe información de una tabla. Especificando USE INDEX (key_list), puede decirle a MySQL que use sólo uno de los posibles índices para encontrar registros en la tabla. La sintaxis alternativa IGNORE INDEX (key_list) puede usarse para decir a MySQL que no use algún índice particular. Estos trucos son útiles si EXPLAIN muestra que MySQL está usando el índice incorrecto de la lista de posibles índices.

También puede usar FORCE INDEX, que actúa como USE INDEX (key_list) pero con la adición que un escaneo de tabla se asume como operación muy cara. En otras palabras, un escaneo de tabla se usa sólo si no hay forma de usar uno de los índices dados para encontrar registros en la tabla.

USE KEY, IGNORE KEY, y FORCE KEY son sinónimos de USE INDEX, IGNORE INDEX, y FORCE INDEX.

Nota: USE INDEX, IGNORE INDEX, y FORCE INDEX sólo afecta los índices usados cuando MySQL decide cómo encontrar registros en la tabla y cómo hacer el join. No afecta si un índice está en uso cuando se resuelve unORDER BY o GROUP BY.

Algunos ejemplos de join:

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;

mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id

-> LEFT JOIN table3 ON table2.id=table3.id;

mysql> SELECT * FROM table1 USE INDEX (key1,key2)

-> WHERE key1=1 AND key2=2 AND key3=3;

mysql> SELECT * FROM table1 IGNORE INDEX (key3)

-> WHERE key1=1 AND key2=2 AND key3=3;

Consulte Sección 7.2.9, “Cómo optimiza MySQL los LEFT JOIN y RIGHT JOIN”.

Optimizar subconsultas

El desarrollo está en marcha, por lo que no hay trucos de optimización fiables a largo plazo. Algunos trucos interesantes que puede usar son:

• Use cláusulas de subconsulta que afecten al número u orden de los registros en la subconsulta. Por ejemplo:

• SELECT * FROM t1 WHERE t1.column1 IN

• (SELECT column1 FROM t2 ORDER BY column1);

• SELECT * FROM t1 WHERE t1.column1 IN

• (SELECT DISTINCT column1 FROM t2);

• SELECT * FROM t1 WHERE EXISTS

• (SELECT * FROM t2 LIMIT 1);

• Reemplace un join con una subconsulta. Por ejemplo, pruebe:

• SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (

• SELECT column1 FROM t2);

En lugar de:

SELECT DISTINCT t1.column1 FROM t1, t2

WHERE t1.column1 = t2.column1;

• Algunas subconsultas pueden transformarse en joins por compatibilidad con versiones anteriores de MySQL que no soportan subconsultas. Sin embargo, en algunos casos, incluso en MySQL 5.0, convertir una subconsulta en un join puede mejorar el rendimiento. Consulte Sección 13.2.8.11, “Re-escribir subconsultas como joins en versiones de MySQL anteriores”.

• Mueva las cláusulas desde fuera

...