iberck
10/1/2015 - 7:13 PM
Joins
Joins
Formas de expresar joins
Explicita: Utiliza la sentencia join para indicar las tablas a cruzar y on para indicar la condición.
Implicita: Utiliza "," para separar las tablas a cruzar y where para indicar la condición.
Ambas formas producen el mismo resultado y tienen el mismo performance, sin embargo no es buena práctica utilizar la sintaxis implicita porque puede dar como resultado un cross join si no se expecifica el filtro en la cláusula where.
Cross join (cartesian join)
Cross join realiza el producto cartesiano de todos los registros de dos tablas, es decir combina cada fila en la tabla A contra cada fila en la tabla B. Las tablas no necesitan relacionarse en nada.
Tabla CUSTOMERS:
| ID | NAME | AGE | ADDRESS | SALARY |
|----|:------:|----:|-----------|--------|
| 1 | CARLOS | 33 | CALLE 317 | 12.30 |
| 2 | ALDO | 35 | JARDINES | 11.02 |
Tabla NOSE:
| ID | VERSION | FORMA |
|----|---------|------------|
| 1 | 0 | PUNTIAGUDA |
| 2 | 0 | AFILADA |
| 3 | 0 | AGUILEÑA |
Cross join:
SELECT *
FROM empleado CROSS JOIN departamento
-- Forma implicita
-- SELECT *
-- FROM empleado, departamento;
RESULTADO:
| ID | NAME | AGE | ADDRESS | SALARY | ID | VERSION | FORMA |
|----|:------:|----:|-----------|--------|----|---------|------------|
| 1 | CARLOS | 33 | CALLE 317 | 12.30 | 1 | 0 | PUNTIAGUDA |
| 1 | CARLOS | 33 | CALLE 317 | 12.30 | 2 | 0 | AFILADA |
| 1 | CARLOS | 33 | CALLE 317 | 12.30 | 3 | 0 | AGUILEÑA |
| 2 | ALDO | 35 | JARDINES | 11.02 | 1 | 0 | PUNTIAGUDA |
| 2 | ALDO | 35 | JARDINES | 11.02 | 2 | 0 | AFILADA |
| 2 | ALDO | 35 | JARDINES | 11.02 | 3 | 0 | AGUILEÑA |
Este tipo de join es poco utilizado en operaciones prácticas.
Este tipo de join no puede ser representado con un diagrama de venn.
Inner join (join)
Un inner join compara cada fila de A con cada fila de B para buscar todos los pares de filas que satisfagan la condición del join. Cuando se satisface la condición del join haciendo match entre los valores no nulos, los valores de las columnas de cada par de filas son combinados en una misma fila.
Conceptualmente esta operación se calcula realizando el producto cruzado de todos los registros, así cada fila de la tabla A es combinada con cada fila de la tabla B; pero solo permanecen aquellos registros que satisfagan las condiciones especificadas. Sin embargo las implementaciones actuales de SQL utilizan algoritmos más avanzados ya que cruzar todos los registros es un proceso costoso.
Es necesario tener especial cuidado cuando se combinan columnas con valores nulos NULL, ya que el valor nulo no se combina con otro valor o con otro nulo, excepto cuando se le agregan condiciones tales como IS NULL o IS NOT NULL.
Tabla Empleado:
| Apellido | IDDepartamento |
|-----------|----------------|
| Andrade | 31 |
| Jordán | 33 |
| Steinberg | 33 |
| Róbinson | 34 |
| Zolano | 34 |
| Gaspar | 36 |
Tabla Departamento:
| NombreDepartamento | IDDepartamento |
|--------------------|----------------|
| Ventas | 31 |
| Ingeniería | 33 |
| Producción | 34 |
| Mercadeo | 35 |
SELECT *
FROM Empleado
INNER JOIN Departamento
ON Empleado.IDDepartamento = Departamento.IDDepartamento
-- Versión implicita
-- SELECT *
-- FROM Empleado, Departamento
-- WHERE Empleado.IDDepartamento = Departamento.IDDepartamento
Resultado:
| Apellido | IDDepartamento | NombreDepartamento | IDDepartamento |
|----------|----------------|--------------------|----------------|
| Zolano | 34 | Producción | 34 |
| Jordán | 33 | Ingeniería | 33 |
| Róbinson | 34 | Producción | 34 |
| Steinberg| 33 | Ingeniería | 33 |
| Andrade | 31 | Ventas | 31 |
El empleado Gaspar y el departamento de Mercadeo no son presentados en los resultados ya que ninguno de éstos tiene registros correspondientes en la otra tabla. No existe un departamento con número 36 ni existe un empleado con número de departamento 35.
Outer joins
Mediante esta operación no se requiere que cada registro en las tablas a tratar tenga un registro equivalente en la otra tabla. El registro es mantenido en la tabla combinada si no existe otro registro que le corresponda.
Este tipo de operación se subdivide dependiendo de la tabla a la cual se le admitirán los registros que no tienen correspondencia, ya sean de tabla izquierda (left outer join), de tabla derecha (right outer join) o combinación completa (full outer join).
No existe una forma implicita para definir outer joins.
Left outer join (left join)
El resultado de un left outer join para las tablas A y B siempre contiene todas las filas de la tabla de la izquierda (A), aún si la condición join no encuentra ninguna coincidencia en la tabla de la derecha (B).
Esto significa que si una fila de la tabla de la izquierda (A) no tiene coincidencia con ninguna fila en la tabla B retornará una fila con los datos de la fila de la tabla de la izquierda (A) pero con valor NULL en cada columna de la tabla B. Un left outer join retorna todos los valores de un inner join más todos los valores en la tabla de la izquierda que no hagan match con la tabla de la derecha, (incluyendo filas con valor NULL en el campo de coincidencia.
Tabla Author:
| ID | VERSION | NAME |
|----|---------|----------------------|
| 1 | 0 | carlos gomez montiel |
| 2 | 0 | aldo diaz soto |
| 3 | 0 | Pozole campos |
| 4 | 0 | ERIKA GM |
Tabla Book
| ID | VERSION | AUTHOR_ID | TITLE |
|----|---------|-----------|-----------------|
| 1 | 0 | 1 | java |
| 2 | 0 | 1 | c# |
| 3 | 0 | 1 | groovy |
| 4 | 0 | 2 | fuma cigarros |
| 5 | 0 | 3 | acomodar coches |
Setencia:
-- (la primera tabla que se menciona en la consulta es la tabla left)
SELECT * FROM Author
LEFT OUTER JOIN Book ON Author.ID=Book.AUTHOR_ID
Resultado:
| ID | VERSION | NAME | ID | VERSION | AUTHOR_ID | TITLE |
|----|---------|----------------------|------|---------|-----------|-----------------|
| 1 | 0 | carlos gomez montiel | 1 | 0 | 1 | java |
| 1 | 0 | carlos gomez montiel | 2 | 0 | 1 | c# |
| 1 | 0 | carlos gomez montiel | 3 | 0 | 1 | groovy |
| 2 | 0 | aldo diaz soto | 4 | 0 | 2 | fuma cigarros |
| 3 | 0 | Pozole campos | 5 | 0 | 3 | acomodar coches |
| 4 | 0 | ERIKA GM | null | null | null | null |
Right outer join (right join)
Un right outer join retorna todos los valores de la tabla de la derecha y los valores que hacen match de la tabla de la izquierda (NULL en el caso de que no haga match la condición del join)
Full outer join
Esta operación presenta los resultados de tabla izquierda y tabla derecha aunque no tengan correspondencia en la otra tabla. La tabla combinada contendrá, entonces, todos los registros de ambas tablas y presentará valores nulos NULLs para registros sin pareja.
Si se tiene la tabla EMPLEADOS con 5 filas (4 de ellos con departamento) y la tabla DEPARTAMENTO CON 3 filas (1 de ellas sin empleados) dará como resultado 6 filas.
Diagramas de venn
Con los siguientes diagramas se entienden claramente los joins:
A continuación se detalla lo que hace cada índice del diagrama:
- 1: selecciona TODO el lado izquierdo (A), incluye lo que se intersecta y lo que no de A con B.
- 2: selecciona TODO el lado derecho (B), incluye lo que se intersecta y lo que no de B con A.
- 3: Sólo obtiene los elementos que se intersectan de A y B.
- 4: Con el query sin el where obtiene todo el lado izquierdo (A), luego con el where obtiene los B.id null es decir lo que no esté intersectado con B.
- 5: Lo mismo que 4 pero del lado derecho.