Una cláusula join en SQL combina los registros de 2 tablas en una base de datos relacional y resulta en una nueva (temporal) tabla, también llamada “tabla joined” , SQL especifica dos tipos de joins : interno y externo.

El programador escribe un join predicado para identificar los registros para JOI Ning?. Si el predicado evalúa verdadero, entonces los registros combinados se insertan en la tabla Joined (temporal) ; de otra forma no contribuye. Cualquier predicado Respaldado por SQL puede convertirse en un join-predicado, por ejemplo, las cláusulas Where.

Como un caso especial, una tabla (tabla base, vista, o tabla join) pude acompañar al mismo en una self-join.

Ejemplos de Tablas Todas las explicaciones subsecuentes de los tipos join en este articulo hacen uso de las siguientes dos tablas. Las filas en estas tablas sirven para ilustrar el efecto de los diferentes tipos de joins y los joins-predicados.

Tabla de empleados

Apellido ID Departamento Rafferty 31 Jones 33 Steinberg 33 Robinson 34 Smith 34 Jasper 36

                                                Tabla de Departamento

ID departamento Nombre del Departamento 31 Ventas 33 Ingenieria 34 intendencia 35 Marketing(comercialización)

Nota: El departamento de marketing actualmente no tiene listado de empleados. Por otro lado, el empleado “Jasper”no tiene relación con ningún departamento actualmente valido en la tabla de departamentos.

un join interno esencialmente combina los registros de las dos tablas (A y B) basados en un dado predicado-join. El SQL-engine procesa el producto cruzado de todos los registro en las tablas. Dado que, los procesos combinan cada registro en la tabla A con cada registro en la tabla B. Solo esos registros en la tabla joined que satisfaga el join predicado restante. Este tipo de join ocurre mas comúnmente en aplicaciones, y representa por default el tipo de join.

SQL 2003 especifica 2 tipos de sintaxis diferentes para expresar joins . La primera es llamada “notación join explicita” usa la palabra clave JOIN, mientras que la segunda usa “notación join implícita”. El join implícito enlista las tablas para acompañarlas en la cláusula FORM en una declaración SELECT , usando comas para separarlos. Por lo tanto siempre procesa un cross-join y la clausula WHERE que puede adicionalmente aplicar filtros de predicado. Esos filtros comparado con el join-predicado en la notación explicita.

Uno puede mas adelante clasificar joins internos como equi-joins, como natural joins , o como cross-joins.

Los programadores deben tener especial cuidado al incorporar las tablas en columnas que contengan valores NULL , dado que NULL nunca coincidirá con ningún otro valor, amenos que la condición join use la instrucción IS NULL o IS NOT NULL.

Como ejemplo, la siguiente consulta que toma registros de tablas de empleados y encuentra relación en la tabla de departamento, basado en el join predicado. EL predicado join compara los valores en la columna I Ddepartamento? en ambas tablas. Y si no encuentra relación entonces los registros joined permanecen fuera de la tabla join .

Ejemplo de un explicito join interno

SELECT * FROM empleado

       INNER JOIN departamento 
          ON empleado. I Ddepartamento = departmento.I Ddepartmento?

Ejemplo de un join interno implicito SELECT * FROM empleado, departmento WHERE empleado.I Ddepartamento = departmento.I Ddepartamento

Resultado del join interno:

Empleado.Apellido Empleado.I Ddepartamento? Departamento.Nombre departamento Departamento.I Ddepartamento? Smith 34 Clerical 34 Jones 33 Engineering 33 Robinson 34 Clerical 34 Steinberg 33 Engineering 33 Rafferty 31 Sales 31

Tipos de joins internos

Un equi-join, es un tipo especifico de join comparativo, o theta join, compara igualdades dentro del predicado join. Usando otro operador comparativo (por ejemplo <) descalifica un join como un equi-join. La siguiente consulta contiene un ejemplo de un equi-join:

SELECT * FROM empleado

       INNER JOIN departmento
          ON empleado.Departmento ID? = departmento.Departmento ID

La tabla join resultante contiene 2 columnas llamadas Departamento ID?, una de la tabla de empleados y una de la tabla de departamento. SQL 2003 no especifica sintaxis para expresar equi-joins, pero algunas bases de datos proveen una sintaxis rápida por ejemplo: MySQL respalda USING(Departamento ID) seguido de ON…sintaxis.

Un join natural ofrece posterior especialización en equi-joins. El join predicado surge implícitamente al compara columnas en ambas tablas que tiene el mismo nombre de columna en las tablas join. La tabla join resultante contiene solo una columna por cada de par de columnas que se llamen igual.

El ejemplo de arriba consulta los joins internos que pueden ser expresados como natural joins de la siguiente manera:

SELECT * FROM empleado NATURAL JOIN departamento

El resulta aparece ligeramente diferente, de cualquier modo, solo porque solo una columna Departamento ID se encuentra en la tabla join.

Empleado.Apellido Departamento ID Departamento.nombreDepartamento Smith 34 Intendencia Jones 33 Ingeniería Robinson 34 Intendencia Steinberg 33 Ingeniería Rafferty 31 Ventas

Usando la palabra clave NATURAL JOIN para expresar joins puede sufrir de ambigüedad conveniente, y dejar los sistemas abiertos a problemas si ocurriese algún cambio de esquema en la base de datos. Por ejemplo, algún cambio, adición, al renombrar las columnas cambia la semántica del natural join. Por lo tanto el método mas seguro involucra codificar explícitamente la condición Join usando regularmente un join interno.

El cross join o join cartesiano provee los cimientos sobre los cuales operan todos los tipos de join. Un Cross join regresa un producto cartesiano del conjunto de registros de las dos tablas join. Por lo tanto iguala el join interno donde la condición join siempre evalue verdadero (true).

Si A y B son dos conjuntos entonces el cross join = AxB El codigo SQL para listas cross join son las tablas joining (FROM), pero no incluye ningún filtro join- predicado. Ejemplo de un explicito Cross-join:

SELECT * FROM employee CROSS JOIN department

Ejemplo de un cross join implicito:

SELECT * FROM employee, department;

Outer joins no requieren que cada registro en las 2 tablas join tengan similitud con los registros de la otra tabla. La tabla joined retiene cada registro incluso si no existe registros similares. Joins externos se subdividen en outer joins izquierdos, outer joins derechos, y full outer joins, dependiendo de cual tabla(s) uno retiene las filas(izquierda, derecha o ambas).

Outer join izquierdo

El resultado de un outer join izquierdo para las tablas A y B siempre contienen todos los registros de la tabla izquierda (A), incluso si la condición join no encuentra registros similares en la tabla derecha (B). Esto significa que si una cláusula ON coincide 0(cero) registros en B, el join de todas formas retornara una fila en el resultado.. pero con NULL en cada columna de B.

Un outer join izquierdo retorna todos los valores de la tabla izquierda, además de los valores que coinciden de la tabla derecha (o NULL en el caso de los predicados que no coincidan) Por ejemplo, esto nos permite encontrar el departamento de un empleado, sin embargo nos muestra el empleado incluso si su departamento no existe (al contrario del join interno como se muestra en el ejemplo de arriba, donde los empleados con departamentos no existentes son filtrados)

Ejemplo de un join externo: SELECT * FROM empleado

       LEFT OUTER JOIN departmento 
          ON empleado.Departmento ID = departmento.Departmento ID

Apellido.empleado Departamento ID. empleado Nombredepartamento. departamento Departamento. Departamento ID Jones 33 Ingenieria 33 Rafferty 31 Ventas 31 Robinson 34 Intendencia 34 Smith 34 Intendencia 34 Jasper 36 NULL NULL Steinberg 33 Ingenieria 33

Outer JOIN Derecho

Un outer join derecho se se parece a un outer join izquierdo, con la diferencia de que las tablas estan invertidas. Cada registro de la tabla derecha (B) aparecerá en la tabla join al menos una vez. Si ninguna fila coincidente de la tabla izquierda (A) existe, NULL aparecerá en las columnas de A para aquellos registros que coincidan con A.

Un outer join retorna todos los valores dela tabla izquierda y los valores coincidentes de la tabla inzquierda ( o NULL en el caso de que no haya coincidentes en el join predicado) Ejemplo de un outer join derecho:

SELECT * FROM empleado

       RIGHT OUTER JOIN departmento 
          ON empleado.Departmento ID = departmento.Departmento ID

Apellido.Empleado Empleado. Departamento ID Departamento. Nombredepartamento Departamento. Departamento ID Smith 34 Intendencia 34 Jones 33 Ingenieria 33 Robinson 34 Intendencia 34 Steinberg 33 Ingenieria 33 Rafferty 31 Ventas 31 NULL NULL Marketing 35

Full outer join

Full outer join combina los resultados tanto de un outer join izquierdo como del derecho. La tabla joined contiene todos los registros de ambas tablas, y llena los espacios coincidentes perdidos en cualquiera de los lados. Ejemplo de un Full outer join: SELECT * FROM empleado

       FULL OUTER JOIN departmento 
          ON empleado.Departmento ID = departmento.Departmento ID

Apellido. Empleado Departamento ID. Empleado Nombre Departamento?.Departamento Departamento. Departamento ID Smith 34 Intendencia 34 Jones 33 Ingenieria 33 Robinson 34 Intendencia 34 Jasper 36 NULL NULL Steinberg 33 Ingenieria 33 Rafferty 31 Ventas 31 NULL NULL Marketing 35

Algunos sistemas de bases de datos no apoyan la funcionalidad directa, pero pueden emularlos mediante el uso de los outer joins derechos e izquierdos y uniones. EL mismo ejemplo aparece de la siguiente manera:

SELECT * FROM empleado

       LEFT JOIN departmento 
          ON empleado.Departmento ID = departmento.Departmento ID

UNION SELECT * FROM empleado

       RIGHT JOIN department
          ON empleado.Departmento ID = departmento.Departmento ID

WHERE empleado.Departmento ID IS NULL

Mucho trabajo en sistemas de base de datos han apuntado a una eficiente implementación de joins, porque los sistemas relacionales comúnmente llama por joins, sin embargo existen dificultades en la optimización de una eficiente ejecución. El problema surge a causa de joins(internos) operando ambos conmutativamente y asociativamente. En practica esto significa que la usa de un simple suministro de una lista de tablas para joining y la condicion join para usar, y el sistema de base de datos tendra la tarea de determinar la manera mas eficiente de ejecutar la operación. Un optimizador de consulta determina como ejecutar la consulta que contenga joins. Un optimizador de consulta tiene dos libertades básicas:

1. Join order : Porque los joins funcionan conmutativamente, la ornde en la cual el sistema de tablas join no cambia el conjunto de resultados finales de la consulta. Sin embargo, join-order posee una enorme impacto en el costo de las operaciones join. Asi que el escoger el mejor join se vuelve muy imporante. 2. Join method: dadas so rablas y la condición join, múltiples algoritmos pueden producir el conjunto de resultados del join. Cual algoritmo ejecuta mas eficientemente dependiendo del tamaño de las tablas de entrada, el numero de filas de cada tabla que coincidan con la condición join, y los operadores requeridos por el resto de la consulta.

Muchos algoritmos join tratan sus entradas de forma diferente. Una puede referirse a las entradas de un join como operadores “outer” e “inner” join , o “left” y “right”, respectivamente. EN el caso de los ciclos anidados, por ejemplo, el sistema base de datos puede escanear la relación inner completa para cada fila de la relación “outer”.

Los query plan que involucran join pueden clasificarse de la siguiente manera:

Left- deep: Usando una tabla base de operadores outer (en vez de otro join) como el operador inner para cada plan join. Right-deep: Usando la tabla base como operador outer en cada join plan. Bushy: Ni left-deep ni Right-deep: ambas entradas a un join pueden por si mismas resultar de los joins.

Estos nombres se derivan de la apariencia del plan de consulta si es atraido como arbol, junto con otras relaciones join en la izquierda y relaciones inner en la derecha.

Algoritmos Join Existen tres algoritmos fundamentales para representar las operaciones join.

Ciclos anidados: Usando los ciclos anidados se produce el mas simple algoritmo join. Para cada tupla outer join relacionada, el sistema busca todas las relaciones inner-join y añade cualquier tupla que coincida con la condicion join al resultado del conjunto. Naturalmente, este algoritmo actua ineficientemente cuando se trata de relaciones join largas: inner, outer o ambas. Un indice en las columnas en la relacion inner en el predicado join puede agilizar la accion.

Los “Block nested loops” (BNL) ofrecen una refinamiento de la técnica: para cada bloque de relaciones outer, el sistema escanea toda la relación inner. Para cada una de las coincidencias entre la actual tupla y una de las tuplas en el actual bloque de las relaciones outer, el sistema añade una tupla del conjunto del join-resultado. Esta variable conlleva mas proceso para cada una de las tuplas en el actual bloque de las relaciones outer, pero mucho menos escaneos para la relación inner.

Merge JOIN Si ambas relaciones join viene en orden, sorteados por los atributos join, el sistema puede realizar la trivialidad join: 1. para cada tupla en la relación outer, 1. Considerando el grupo actual de tuplas de la relación inner; un grupo consiste en un conjunto de tuplas continuas en la relación inner con el mismo valor en los atributos join. 2. Para cada tupla coincidente en el grupo inner, añade una tupla a resultado join . una vez que el grupo inner a sido agotado, avanza ambos grupos inner y outter consecuentes del siguiente grupo.

Merge joins ofrecen una razón por la cual muchos optimizadotes mantienen seguimiento del orden de los nodos query- si una o ambas entradas relacionadas a un merge join llega a los atributos join, el sistema no necesita realizar un sorteo adicional. De cualquier manera el DBMS necesita realizar el sorteo, usualmente usando un sorteo externo para evitar consumir mucho espacio en memoria.

Un algorimo hash join puede producir equi-joins. El sistema de base de datos pre-forma el acceso a las tablas ocupandose de construir las tablas Hash con los atributos join.Las tablas hash operan mucho mas rapido en comparación con las que operan a través de árboles de índice. De cualquier manera se pueden comparar valores hashed solo por igualdad y no por otras relaciones.

Presentacion Power Point