Cuadro comparativo

fuente www.wordpress.com

Ejemplos de aplicacion de cada uno de los join

Un join es una combinación de dos o más tablas de una base de datos relacional, es una instrucción de lo más imprescindible si queremos realizar una aplicación que realice un uso correcto e intensivo de un gestor de Bases de datos cómo MySQL. Y es que gracias a los JOIN podemos leer datos de diferentes bases de datos en una única consulta. Lo que nos permitirá diseñar fácilmente tablas relacionadas entre ellas, veamos un ejemplo: Figura 1: En este ejemplo podemos ver una relación entre las tablas de empleados y de despachos, y la representación de un join entre ambas MySQL soporta muchas sintaxis diferentes para la instrucción JOIN en comandos de SELECT, DELETE y UPDATE. Escribiremos la instrucción de JOIN en el apartado de referéncia de tablas ( el WHERE ) de nuestra consulta SQL, escojeremos la sintaxis de JOIN de las mencionadas anteriormente según el tipo de JOIN que queramos realizar. A continuación se explican, con sus ejemplos en MySQL, los principales tipos de JOIN. ArribaTabla de contenido · INNER JOIN o , · LEFT (RIGHT) JOIN · NATURAL JOIN ArribaINNER JOIN o , Este tipo de JOIN une los resultados coincidentes en ambas tablas, no devolverá un registro de una tabla si no coincide con otro de la otra.   000  SELECT * FROM empleados,despachos WHERE empleados.despacho_id = despachos.id  Seleccionar   SQL ArribaLEFT (RIGHT) JOIN LEFT JOIN retorna los registros de la primera tabla aunque no coincidan con ninguno de la otra. RIGHT JOIN realiza lo mismo pero en base a la segunda tabla.   000  SELECT * FROM empleados LEFT JOIN despachos ON empleados.despacho_id = despachos.id  Seleccionar   SQL ArribaNATURAL JOIN Este JOIN retorna los registros de ambas tablas que coincidan en todos los campos con el mismo nombre ( en el ejemplo empleados.nombre y despachos.nombre ). RIGHT JOIN realiza lo mismo pero en base a la segunda tabla.   000  SELECT * FROM empleados NATURAL JOIN despachos  Seleccionar   SQL

Definicion de cada uno de los join

El LEFT JOIN y RIGHT JOIN

El LEFT JOIN y RIGHT JOIN son otro tipo de composición de tablas, también denominada composición externa. Son una extensión del INNER JOIN. Las composiciones vistas hasta ahora (el producto cartesiano y el INNER JOIN) son composiciones internas ya que todos los valores de las filas del resultado son valores que están en las tablas que se combinan. Con una composición interna sólo se obtienen las filas que tienen al menos una fila de la otra tabla que cumpla la condición, veamos un ejemplo: Queremos combinar los empleados con las oficinas para saber la ciudad de la oficina donde trabaja cada empleado, si utilizamos un producto cartesiano tenemos: SELECT empleados.*,ciudad FROM empleados, oficinas WHERE empleados.oficina = oficinas.oficina Observar que hemos cualificado el nombre de columna oficina ya que ese nombre aparece en las dos tablas de la FROM. Con esta sentencia los empleados que no tienen una oficina asignada (un valor nulo en el campo oficina de la tabla empleados)no aparecen en el resultado ya que la condición empleados.oficina = oficinas.oficina será siempre nula para esos empleados. Si utilizamos el INNER JOIN: SELECT empleados.*, ciudad FROM empleados INNER JOIN oficinas ON empleados.oficina = oficinas.oficina Nos pasa lo mismo, el empleado 110 tiene un valor nulo en el campo oficina y no aparecerá en el resultado. Pues en los casos en que queremos que también aparezcan las filas que no tienen una fila coincidente en la otra tabla,utilizaremos el LEFT o RIGHT JOIN.  La sintaxis del LEFT JOIN es la siguiente:

La descripción de la sintaxis es la misma que la del INNER JOIN (ver página anterior), lo único que cambia es la palabra INNERpor LEFT (izquierda en inglés). Esta operación consiste en añadir al resultado del INNER JOIN las filas de la tabla de la izquierda que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la derecha con valores nulos. Ejemplo: SELECT * FROM empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y el empleado 110 que no tiene oficina aparece con sus datos normales y los datos de su oficina a nulos.  La sintaxis del RIGHT JOIN es la siguiente: La sintaxis es la misma que la del INNER JOIN (ver página anterior), lo único que cambia es la palabra INNER por RIGHT (derechaen inglés). Esta operación consiste en añadir al resultado del INNER JOIN las filas de la tabla de la derecha que no tienen correspondencia en la otra tabla, y rellenar en esas filas los campos de la tabla de la izquierda con valores nulos. Ejemplo: SELECT * FROM empleados RIGHT JOIN oficinas ON empleados.oficina = oficinas.oficina Con el ejemplo anterior obtenemos una lista de los empleados con los datos de su oficina, y además aparece una fila por cada oficina que no está asignada a ningún empleado con los datos del empleado a nulos. Una operación LEFT JOIN o RIGHT JOIN se puede anidar dentro de una operación INNER JOIN, pero una operación INNERJOIN no se puede anidar dentro de LEFT JOIN o RIGHT JOIN. Los anidamientos de JOIN de distinta naturaleza no funcionan siempre, a veces depende del orden en que colocamos las tablas, en estos casos lo mejor es probar y si no permite el anudamiento, cambiar el orden de las tablas ( y por tanto de los JOINs) dentro de la cláusula FROM. Por ejemplo podemos tener: SELECT * FROM clientes INNER JOIN (empleados LEFT JOIN oficinas ON empleados.oficina = oficinas.oficina) ON clientes.repclie = empleados.numclie Combinamos empleados con oficinas para obtener los datos de la oficina de cada empleado, y luego añadimos los clientes de cada representante, así obtenemos los clientes que tienen un representante asignado y los datos de la oficina del representante asignado. Si hubiéramos puesto INNER en vez de LEFT no saldrían los clientes que tienen el empleado 110 (porque no tiene oficina y por tanto no aparece en el resultado del LEFT JOIN y por tanto no entrará en el cálculo del INNER JOIN con clientes).

Resumen de cuándo utilizar cada operación.

Para saber en cada caso qué tipo de operación se debe utilizar, a continuación tienes un gráfico que indica qué preguntas se tienen que hacer y según la respuesta, qué operación utilizar. Para resumir hemos llamado T1 y T2 las tablas de las que queremos sacar los datos y R la tabla lógica que representa el resultado de consulta. T1 y T2 podrían ser tablas guardadas o consultas. En la última parte cuando se pregunta "En T1 hay filas que no tienen pareja en T2", la pregunta se debe de interpretar como "en alguna de las tablas hay filas que no tienen pareja".

Que son los join

MySQL soporta las siguientes sintaxis para JOIN para ser usadas como la parte de referencia de tabla en sentencias y sentecias y multitabla:

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
\{ OJ 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

Donde table_reference se define como:

tbl_name [[AS] alias]
    [[USE INDEX (key_list)]
      | [IGNORE INDEX (key_list)]
      | [FORCE INDEX (key_list)]]

Y join_condition se define como:

ON conditional_expr | USING (column_list)

Generalmente no será necesario especificar condiciones en la parte ON para restringir qué filas se quieren en el conjunto de resultados, pero es mejor especificar esas condiciones en la cláusula WHERE. Hay algunas excepciones a esta regla.

Hay que tener en cuenta que la sintaxis INNER JOIN permite una join_condition sólo a partir de MySQL 3.23.17. Para JOIN y CROSS JOIN sólo se permite a partir de MySQL 4.0.11.

La sintaxis \ mostrada anteriormente sólo existe por compatibilidad con ODBC.

• Se puede definir un alias para una referencia de tabla usando tbl_name AS alias_name otbl_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 ninguna fila coincidente en la tabla derecha en la parte ON o USING en un LEFT JOIN, se usa una fila con un valor NULL para todas las columnas para la tabla derecha. Se puede usar este comportamiento 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 todas las filas en table1 con un valor de id que no esté presente entable2 (esto es, todas las filas en table1 sin correspondencia en table2). Se asume que table2.idestá declarado como NOT NULL.
• La cláusula USING (column_list) nombra una lista de columnas que deben existir en ambas tablas. Las dos cláusulas siguientes son idénticas semánticamente:
  

  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

• La reunión NATURAL [LEFT] JOIN de dos tablas se define para que sea equivalente semánticamente a un INNER JOIN o a un LEFT JOIN con una cláusula USING que nombre todas las columnas que existan en ambas tablas.
• INNER JOIN y , (coma) son equivalentes semánticamente en ausencia de una condición de reunión: ambas producirán un producto cartesiano entre las tablas especificadas (esto es, todas y cada una de las filas en la primera tabla se reunirán con todas las de las segunda tabla).
• RIGHT JOIN trabaja de forma análoga a LEFT JOIN. Para mantener el código portable a través de las bases de datos, se recomienda usar LEFT JOIN en lugar de RIGHT JOIN.
• STRAIGHT_JOIN es idéntico a JOIN, excepto que la tabla izquierda siempre es leída antes que la tabla derecha. Esto se puede usar para aquellos (pocos) casos para los cuales el optimizador de join coloca las tablas en el orden equivocado.

Desde MySQL 3.23.12, se pueden obtener pistas sobre cuáles son los índices que debe usar MySQL cuando recupere información desde una tabla. Mediante la especificación de USE INDEX (key_list), se puede indicar a MySQL que use sólo uno de los índices posibles para encontrar filas en una tabla. La sintaxis alternativa IGNORE INDEX (key_list) se puede usar para indicar a MySQL que no use algún índice particular. Estas pistas son aconsejable si muestra que MySQL está usando el índice equivocado de una lista de posibles índices.

A partir de MySQL 4.0.9, se puede usar FORCE INDEX. Esto funciona como USE INDEX (key_list)pero con el añadido de que se asumirá que un recorrido secuencial de la tabla será demasiado costoso. En otras palabras, se usará un un recorrido secuencial sólo si no hay modo de usar uno de los índices dados para encontrar filas 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 afectan a aquellos índices usados cuando MySQL decide el modo de encontrar filas en la tabla y cómo hacer la reunión. No afectan a si un índice será usado cuando se resuelva una cláusula ORDER BYo GROUP BY.