tablas - SQL JOIN y diferentes tipos de JOINs

tipos de join oracle

Question

¿Qué es un SQL JOIN y cuáles son los diferentes tipos?

Answer 1

¿Qué es SQL JOIN ?

SQL JOIN es un método para recuperar datos de dos o más tablas de bases de datos.

¿Cuáles son los diferentes SQL JOIN s?

Hay un total de cinco JOIN s. Son :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. UNIRSE o INNER JOIN:

En este tipo de JOIN , obtenemos todos los registros que coinciden con la condición en ambas tablas, y los registros en ambas tablas que no coinciden no se informan.

En otras palabras, INNER JOIN se basa en el hecho único de que: SOLAMENTE las entradas coincidentes en AMBAS tablas deben estar listadas.

Tenga en cuenta que una JOIN sin ninguna otra JOIN clave JOIN (como INNER , OUTER , LEFT , etc.) es una INNER JOIN . En otras palabras, JOIN es un azúcar sintáctico para INNER JOIN (ver: Diferencia entre JOIN y INNER JOIN ).

2. ÚNETE EXTERNO:

OUTER JOIN recupera

O bien, las filas coincidentes de una tabla y todas las filas en la otra tabla O, todas las filas en todas las tablas (no importa si existe o no una coincidencia).

Hay tres tipos de unión externa:

2.1 JUNTA EXTERNA IZQUIERDA o ÚNICA IZQUIERDA

Esta unión devuelve todas las filas de la tabla izquierda junto con las filas coincidentes de la tabla derecha. Si no hay columnas que coincidan en la tabla de la derecha, devuelve valores NULL .

2.2 JUNTA EXTERIOR DERECHA o ÚNICA DERECHA

Esta JOIN devuelve todas las filas de la tabla derecha junto con las filas coincidentes de la tabla izquierda. Si no hay columnas que coincidan en la tabla de la izquierda, devuelve valores NULL .

2.3 UNIÓN COMPLETA EXTERNA o UNIÓN COMPLETA

Este JOIN combina LEFT OUTER JOIN y RIGHT OUTER JOIN . Devuelve filas de cualquiera de las tablas cuando se cumplen las condiciones y devuelve un valor NULL cuando no hay coincidencia.

En otras palabras, OUTER JOIN se basa en el hecho de que: SOLO las entradas coincidentes en UNA de las tablas (DERECHA o IZQUIERDA) o AMBOS de las tablas (COMPLETAS) DEBEN estar en la lista.

Note that `OUTER JOIN` is a loosened form of `INNER JOIN`.

3. UNIÓN NATURAL:

Se basa en las dos condiciones:

1. La JOIN se realiza en todas las columnas con el mismo nombre para la igualdad.
2. Elimina columnas duplicadas del resultado.

Esto parece ser más de naturaleza teórica y como resultado (probablemente) la mayoría de los DBMS ni siquiera se molestan en apoyar esto.

4. ÚNASE CRUZADA:

Es el producto cartesiano de las dos tablas involucradas. El resultado de un CROSS JOIN no tendrá sentido en la mayoría de las situaciones. Además, no necesitaremos esto (o lo que menos necesitamos, para ser precisos).

5. SELF JOIN:

No es una forma diferente de JOIN , sino que es un JOIN ( INNER , OUTER , etc.) de una tabla para sí mismo.

Uniones basadas en operadores

Dependiendo del operador utilizado para una cláusula JOIN , puede haber dos tipos de JOIN s. Son

1. Equi ÚNETE
2. Theta ÚNETE

1. Equi ÚNETE:

Para cualquier tipo de INNER ( INNER , OUTER , etc.), si usamos SOLAMENTE el operador de igualdad (=), entonces decimos que la EQUI JOIN es una EQUI JOIN .

2. Theta ÚNETE:

Esto es lo mismo que EQUI JOIN pero permite a todos los demás operadores como>, <,> = etc.

Muchos consideran que tanto EQUI JOIN como Theta JOIN similares a INNER , OUTER etc JOIN s. Pero creo firmemente que es un error y hace que las ideas sean vagas. Debido a que INNER JOIN , OUTER JOIN etc., están todos conectados con las tablas y sus datos, mientras que EQUI JOIN y THETA JOIN solo están conectados con los operadores que usamos en el primero.

Una vez más, hay muchos que consideran NATURAL JOIN como una especie de EQUI JOIN "peculiar". De hecho, es cierto, debido a la primera condición que mencioné para NATURAL JOIN . Sin embargo, no tenemos que restringir eso simplemente a NATURAL JOIN s solo. INNER JOIN s, OUTER JOIN s, etc. también podría ser un EQUI JOIN .

Answer 2

Curiosamente la mayoría de las otras respuestas sufren de estos dos problemas:

• Se enfocan en formas básicas de unirse solamente
• Ellos (ab) usan los diagramas de Venn, que son una herramienta inexacta para visualizar uniones (son mucho mejores para las uniones) .

Recientemente escribí un artículo sobre el tema: Una guía probablemente incompleta y completa de las muchas formas diferentes de UNIRSE a las tablas en SQL , que resumiré aquí.

Lo primero y más importante: los JOIN son productos cartesianos.

Esta es la razón por la que los diagramas de Venn los explican de manera tan inexacta, ya que un JOIN crea un producto cartesiano entre las dos tablas unidas. Wikipedia lo ilustra muy bien:

La sintaxis SQL para productos cartesianos es CROSS JOIN . Por ejemplo:

SELECT *

-- This just generates all the days in January 2017
FROM generate_series(
  ''2017-01-01''::TIMESTAMP,
  ''2017-01-01''::TIMESTAMP + INTERVAL ''1 month -1 day'',
  INTERVAL ''1 day''
) AS days(day)

-- Here, we''re combining all days with all departments
CROSS JOIN departments

Que combina todas las filas de una tabla con todas las filas de la otra tabla:

Fuente:

+--------+   +------------+
| day    |   | department |
+--------+   +------------+
| Jan 01 |   | Dept 1     |
| Jan 02 |   | Dept 2     |
| ...    |   | Dept 3     |
| Jan 30 |   +------------+
| Jan 31 |
+--------+

Resultado:

+--------+------------+
| day    | department |
+--------+------------+
| Jan 01 | Dept 1     |
| Jan 01 | Dept 2     |
| Jan 01 | Dept 3     |
| Jan 02 | Dept 1     |
| Jan 02 | Dept 2     |
| Jan 02 | Dept 3     |
| ...    | ...        |
| Jan 31 | Dept 1     |
| Jan 31 | Dept 2     |
| Jan 31 | Dept 3     |
+--------+------------+

Si solo escribimos una lista de tablas separadas por comas, obtendremos lo mismo:

-- CROSS JOINing two tables:
SELECT * FROM table1, table2

UNIÓN INTERNA (Theta-JOIN)

Un INNER JOIN es solo un CROSS JOIN filtrado donde el predicado del filtro se llama Theta en el álgebra relacional.

Por ejemplo:

SELECT *

-- Same as before
FROM generate_series(
  ''2017-01-01''::TIMESTAMP,
  ''2017-01-01''::TIMESTAMP + INTERVAL ''1 month -1 day'',
  INTERVAL ''1 day''
) AS days(day)

-- Now, exclude all days/departments combinations for
-- days before the department was created
JOIN departments AS d ON day >= d.created_at

Tenga en cuenta que la palabra clave INNER es opcional (excepto en MS Access).

( mira el artículo para ver ejemplos de resultados )

EQUI JOIN

Un tipo especial de Theta-JOIN es equi JOIN, el que más utilizamos. El predicado une la clave principal de una tabla con la clave externa de otra tabla. Si usamos la base de datos de Sakila para ilustrar, podemos escribir:

SELECT *
FROM actor AS a
JOIN film_actor AS fa ON a.actor_id = fa.actor_id
JOIN film AS f ON f.film_id = fa.film_id

Esto combina a todos los actores con sus películas.

O también, en algunas bases de datos:

SELECT *
FROM actor
JOIN film_actor USING (actor_id)
JOIN film USING (film_id)

La sintaxis de USING() permite especificar una columna que debe estar presente en cualquiera de los lados de las tablas de una operación JOIN y crea un predicado de igualdad en esas dos columnas.

Unirse natural

Otras respuestas han enumerado este "tipo de unión" por separado, pero eso no tiene sentido. Es solo una forma de sintaxis de azúcar para equi JOIN, que es un caso especial de Theta-JOIN o INNER JOIN. NATURAL JOIN simplemente recopila todas las columnas que son comunes a ambas tablas que se unen y se une a USING() esas columnas. Lo que casi nunca es útil, debido a coincidencias accidentales (como LAST_UPDATE columnas LAST_UPDATE en la base de datos de Sakila ).

Aquí está la sintaxis:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

ÚNETE EXTERNO

Ahora, OUTER JOIN es un poco diferente de INNER JOIN ya que crea una UNION de varios productos cartesianos. Podemos escribir:

-- Convenient syntax:
SELECT *
FROM a LEFT JOIN b ON <predicate>

-- Cumbersome, equivalent syntax:
SELECT a.*, b.*
FROM a JOIN b ON <predicate>
UNION ALL
SELECT a.*, NULL, NULL, ..., NULL
FROM a
WHERE NOT EXISTS (
  SELECT * FROM b WHERE <predicate>
)

Nadie quiere escribir lo último, por lo que escribimos OUTER JOIN (que normalmente está mejor optimizado por las bases de datos).

Al igual que INNER , la palabra clave OUTER es opcional, aquí.

OUTER JOIN viene en tres sabores:

• LEFT [ OUTER ] JOIN : La tabla izquierda de la expresión JOIN se agrega a la unión como se muestra arriba.
• RIGHT [ OUTER ] JOIN : La tabla derecha de la expresión JOIN se agrega a la unión como se muestra arriba.
• FULL [ OUTER ] JOIN : ambas tablas de la expresión JOIN se agregan a la unión como se muestra arriba.

Todos estos se pueden combinar con la palabra clave USING() o con NATURAL ( recientemente tuve un caso de uso en el mundo real para un NATURAL FULL JOIN recientemente )

Sintaxis alternativas

Hay algunas sintaxis históricas en desuso en Oracle y SQL Server, que OUTER JOIN ya antes de que el estándar SQL tuviera una sintaxis para esto:

-- Oracle
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id = fa.actor_id(+)
AND fa.film_id = f.film_id(+)

-- SQL Server
SELECT *
FROM actor a, film_actor fa, film f
WHERE a.actor_id *= fa.actor_id
AND fa.film_id *= f.film_id

Dicho esto, no use esta sintaxis. Solo enumero esto aquí para que pueda reconocerlo en publicaciones antiguas del blog / código heredado.

Particionado

Pocas personas lo saben, pero el estándar SQL especifica OUTER JOIN particionado (y Oracle lo implementa). Puedes escribir cosas como esta:

WITH

  -- Using CONNECT BY to generate all dates in January
  days(day) AS (
    SELECT DATE ''2017-01-01'' + LEVEL - 1
    FROM dual
    CONNECT BY LEVEL <= 31
  ),

  -- Our departments
  departments(department, created_at) AS (
    SELECT ''Dept 1'', DATE ''2017-01-10'' FROM dual UNION ALL
    SELECT ''Dept 2'', DATE ''2017-01-11'' FROM dual UNION ALL
    SELECT ''Dept 3'', DATE ''2017-01-12'' FROM dual UNION ALL
    SELECT ''Dept 4'', DATE ''2017-04-01'' FROM dual UNION ALL
    SELECT ''Dept 5'', DATE ''2017-04-02'' FROM dual
  )
SELECT *
FROM days 
LEFT JOIN departments 
  PARTITION BY (department) -- This is where the magic happens
  ON day >= created_at

Partes del resultado:

+--------+------------+------------+
| day    | department | created_at |
+--------+------------+------------+
| Jan 01 | Dept 1     |            | -- Didn''t match, but still get row
| Jan 02 | Dept 1     |            | -- Didn''t match, but still get row
| ...    | Dept 1     |            | -- Didn''t match, but still get row
| Jan 09 | Dept 1     |            | -- Didn''t match, but still get row
| Jan 10 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 11 | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 12 | Dept 1     | Jan 10     | -- Matches, so get join result
| ...    | Dept 1     | Jan 10     | -- Matches, so get join result
| Jan 31 | Dept 1     | Jan 10     | -- Matches, so get join result

El punto aquí es que todas las filas desde el lado particionado de la unión terminarán en el resultado, independientemente de si la JOIN coincidió con algo en el "otro lado de la UNIÓN". Larga historia corta: Esto es para llenar los datos dispersos en los informes. ¡Muy útil!

SEMI JOIN

¿Seriamente? ¿Ninguna otra respuesta tiene esto? Por supuesto que no, porque no tiene una sintaxis nativa en SQL, desafortunadamente (como ANTI JOIN a continuación). Pero podemos usar IN() y EXISTS() , por ejemplo, para encontrar a todos los actores que han jugado en películas:

SELECT *
FROM actor a
WHERE EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

El WHERE a.actor_id = fa.actor_id actúa como el predicado de unión parcial. Si no lo cree, consulte los planes de ejecución, por ejemplo, en Oracle. Verá que la base de datos ejecuta una operación SEMI JOIN, no el predicado EXISTS() .

ANTI JOIN

Esto es justo lo contrario de SEMI JOIN ( tenga cuidado de no usar NOT IN , ya que tiene una importante advertencia)

Aquí están todos los actores sin películas:

SELECT *
FROM actor a
WHERE NOT EXISTS (
  SELECT * FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

Algunas personas (especialmente las personas de MySQL) también escriben ANTI JOIN de esta manera:

SELECT *
FROM actor a
LEFT JOIN film_actor fa
USING (actor_id)
WHERE film_id IS NULL

Creo que la razón histórica es el rendimiento.

ÚNETE LATERAL

Dios mío, este es demasiado genial. ¿Soy el único que lo menciona? Aquí hay una consulta genial:

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
LEFT OUTER JOIN LATERAL (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  JOIN inventory AS i USING (film_id)
  JOIN rental AS r USING (inventory_id)
  JOIN payment AS p USING (rental_id)
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f
ON true

Encontrará las 5 mejores películas por actor. Cada vez que necesite una consulta TOP-N-por-algo, LATERAL JOIN será su amigo. Si usted es una persona de SQL Server, entonces conoce este tipo de JOIN bajo el nombre APPLY

SELECT a.first_name, a.last_name, f.*
FROM actor AS a
OUTER APPLY (
  SELECT f.title, SUM(amount) AS revenue
  FROM film AS f
  JOIN film_actor AS fa ON f.film_id = fa.film_id
  JOIN inventory AS i ON f.film_id = i.film_id
  JOIN rental AS r ON i.inventory_id = r.inventory_id
  JOIN payment AS p ON r.rental_id = p.rental_id
  WHERE fa.actor_id = a.actor_id -- JOIN predicate with the outer query!
  GROUP BY f.film_id
  ORDER BY revenue DESC
  LIMIT 5
) AS f

Bien, tal vez eso sea trampa, porque una expresión LATERAL JOIN o APPLY es en realidad una "subconsulta correlacionada" que produce varias filas. Pero si permitimos "subconsultas correlacionadas", también podemos hablar de ...

MULTISET

Esto solo lo implementan Oracle e Informix (que yo sepa), pero se puede emular en PostgreSQL usando arreglos y / o XML y en SQL Server usando XML.

MULTISET produce una subconsulta correlacionada y anida el conjunto resultante de filas en la consulta externa. La siguiente consulta selecciona a todos los actores y para cada actor recopila sus películas en una colección anidada:

SELECT a.*, MULTISET (
  SELECT f.*
  FROM film AS f
  JOIN film_actor AS fa USING (film_id)
  WHERE a.actor_id = fa.actor_id
) AS films
FROM actor

Como ha visto, hay más tipos de ÚNETE que solo el INNER "aburrido", OUTER y CROSS JOIN que normalmente se mencionan. Más detalles en mi artículo . Y por favor, deja de usar los diagramas de Venn para ilustrarlos.

Answer 3

He creado una ilustración que explica mejor que las palabras, en mi opinión:

Answer 4

Una ilustración de W3schools :

Answer 5

Voy a presionar mi mascota: la palabra clave USING.

Si ambas tablas en ambos lados de la UNIÓN tienen sus claves externas con el nombre correcto (es decir, el mismo nombre, no solo el "id"), entonces esto se puede usar:

SELECT ...
FROM customers JOIN orders USING (customer_id)

Me parece muy práctico, legible y no se usa con la frecuencia suficiente.

Answer 6

Definición:

UNIONES es una forma de consultar los datos que se combinaron de varias tablas simultáneamente.

Tipos de UNIONES:

En cuanto a RDBMS, existen 5 tipos de combinaciones:

• Equi-Join: combina registros comunes de dos tablas basadas en la condición de igualdad. Técnicamente, la unión se realizó utilizando el operador de igualdad (=) para comparar los valores de PrimaryKey de una tabla y los valores de Foriegn Key de la tabla antoher, por lo tanto, el conjunto de resultados incluye registros comunes (coincidentes) de ambas tablas. Para implementación ver INNER-JOIN.

• Natural-Join: es una versión mejorada de Equi-Join, en la que la operación SELECT omite la columna duplicada. Para implementación ver INNER-JOIN

• Non-Equi-Join: es inverso a Equi-join donde la condición de unión es un uso distinto del operador igual (=), por ejemplo,! =, <=,> =,>, <O BETWEEN, etc. Para la implementación, vea INNER-JOIN.

• Auto-Join:: Un comportamiento personalizado de join donde una tabla se combinó consigo misma; Esto suele ser necesario para consultar tablas de autorreferencia (o entidad de relación Unaria). Para implementación ver INNER-JOINs.

• Producto cartesiano: combina todos los registros de ambas tablas sin ninguna condición. Técnicamente, devuelve el conjunto de resultados de una consulta sin la cláusula WHERE.

Según la preocupación y el avance de SQL, existen 3 tipos de combinaciones y todas las combinaciones RDBMS se pueden lograr utilizando estos tipos de combinaciones.

1. INNER-JOIN: combina (o combiens) filas combinadas de dos tablas. La coincidencia se realiza en base a columnas comunes de tablas y su operación de comparación. Si se trata de una condición basada en la equidad, entonces: se realizó EQUI-JOIN;

2. ** OUTER-JOIN: ** Combina (o combina) filas coincidentes de dos tablas y filas no coincidentes con valores NULL. Sin embargo, se puede personalizar la selección de filas no coincidentes, por ejemplo, seleccionando una fila no coincidente de la primera tabla o la segunda tabla por subtipos: IZQUIERDA JUNTAS EXTERIORES y JUNTAS DERECHA DERECHA.

   2.1. IZQUIERDA ÚNICA EXTERNA (también conocida como LEFT-JOIN): devuelve filas coincidentes de dos tablas y se desasocia de la tabla IZQUIERDA (es decir, la primera tabla) solamente.

   2.2. RIGHT Outer JOIN (aka, RIGHT-JOIN): devuelve filas coincidentes de dos tablas y no coincide de la tabla RIGHT solamente.

   2.3. FULL OUTER JOIN (también conocido como OUTER JOIN): devuelve coincidencias y no coincidentes de ambas tablas.

3. CROSS-JOIN: esta unión no combina / combiens, en lugar de eso, realiza un producto cartisiano.

Nota: Self-JOIN se puede lograr mediante INNER-JOIN, OUTER-JOIN y CROSS-JOIN en función de los requisitos, pero la tabla debe unirse consigo misma.

Para más información:

Ejemplos:

1.1: INNER-JOIN: Implementación equitativa

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A.<PrimaryKey> =B.<ForeignKey>;

1.2: INNER-JOIN: implementación Natural-JOIN

Select A.*, B.Col1, B.Col2          --But no B.ForiengKyeColumn in Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: INNER-JOIN con implementación sin Eqijoin

Select *
 FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;

1.4: INNER-JOIN con SELF-JOIN

Select *
 FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;

2.1: UNIÓN EXTERNA (unión externa completa)

Select *
 FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2: UNIRSE IZQUIERDO

Select *
 FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;

2.3: ÚNASE A LA DERECHA

Select *
 FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;

3.1: UNIÓN CRUZADA

Select *
 FROM TableA CROSS JOIN TableB;

3.2: CROSS JOIN-Self JOIN

Select *
 FROM Table1 A1 CROSS JOIN Table1 A2;

//O//

Select *
 FROM Table1 A1,Table1 A2;