left - outer join mysql

Diferencia entre INNER JOIN y LEFT SEMI JOIN

Question

¿Cuál es la diferencia entre INNER JOIN y LEFT SEMI JOIN ?

En el siguiente escenario, ¿por qué obtengo dos resultados diferentes?

El conjunto de resultados de INNER JOIN es mucho más grande. ¿Alguien puede explicar? Estoy tratando de obtener los nombres dentro de la table_1 que solo aparecen en la table_2 .

SELECT name
FROM table_1 a
    INNER JOIN table_2 b ON a.name=b.name

SELECT name
FROM table_1 a
    LEFT SEMI JOIN table_2 b ON (a.name=b.name)

Answer 1

Probé en Hive y obtuve la siguiente salida.

tabla 1

1, wqe, chennai, india

2, stu, salem, india

3, mia, bangalore, india

4, yepie, newyork, USA

Tabla 2

1, wqe, chennai, india

2, stu, salem, india

3, mia, bangalore, india

5, capilla, Los Ángeles, USA

Unir internamente

SELECT * FROM table1 INNER JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india 1 wqe chennai india

2 stu salem india 2 stu salem india

3 mia bangalore india 3 mia bangalore india

Unirse a la izquierda

SELECT * FROM table1 LEFT JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india 1 wqe chennai india

2 stu salem india 2 stu salem india

3 mia bangalore india 3 mia bangalore india

4 yepie newyork USA NULL NULL NULL NULL

Unirse a la izquierda

SELECT * FROM table1 IZQUIERDA SEMI JOIN table2 ON (table1.id = table2.id);

1 wqe chennai india

2 stu salem india

3 mia bangalore india

nota: solo se muestran los registros en la tabla izquierda mientras que para la combinación izquierda se muestran los registros de la tabla

Answer 2

Supongamos que hay 2 tablas TableA y TableB con solo 2 columnas (Id, Data) y los siguientes datos:

Tabla A:

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataA11 |
|  1 | DataA12 |
|  1 | DataA13 |
|  2 | DataA21 |
|  3 | DataA31 |
+----+---------+

TablaB:

+----+---------+
| Id |  Data   |
+----+---------+
|  1 | DataB11 |
|  2 | DataB21 |
|  2 | DataB22 |
|  2 | DataB23 |
|  4 | DataB41 |
+----+---------+

La combinación interna en la columna Id devolverá las columnas de ambas tablas y solo los registros coincidentes:

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
''----''---------''----''---------''

La combinación izquierda (o la combinación externa izquierda) en la columna Id devolverá columnas de ambas tablas y registros coincidentes con registros de la tabla izquierda (valores nulos de la tabla derecha):

.----.---------.----.---------.
| Id |  Data   | Id |  Data   |
:----+---------+----+---------:
|  1 | DataA11 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA12 |  1 | DataB11 |
:----+---------+----+---------:
|  1 | DataA13 |  1 | DataB11 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB21 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB22 |
:----+---------+----+---------:
|  2 | DataA21 |  2 | DataB23 |
:----+---------+----+---------:
|  3 | DataA31 |    |         |
''----''---------''----''---------''

La combinación correcta (o la combinación externa derecha) en la columna Id devolverá las columnas de ambas tablas y los registros coincidentes con los registros de la tabla derecha (valores nulos de la tabla izquierda):

┌────┬─────────┬────┬─────────┐
│ Id │  Data   │ Id │  Data   │
├────┼─────────┼────┼─────────┤
│  1 │ DataA11 │  1 │ DataB11 │
│  1 │ DataA12 │  1 │ DataB11 │
│  1 │ DataA13 │  1 │ DataB11 │
│  2 │ DataA21 │  2 │ DataB21 │
│  2 │ DataA21 │  2 │ DataB22 │
│  2 │ DataA21 │  2 │ DataB23 │
│    │         │  4 │ DataB41 │
└────┴─────────┴────┴─────────┘

La unión externa completa en la columna Id devolverá las columnas de las tablas y los registros coincidentes con los registros de la tabla izquierda (valores nulos de la tabla derecha) y los registros de la tabla derecha (valores nulos de la tabla izquierda):

╔════╦═════════╦════╦═════════╗
║ Id ║  Data   ║ Id ║  Data   ║
╠════╬═════════╬════╬═════════╣
║  - ║         ║    ║         ║
║  1 ║ DataA11 ║  1 ║ DataB11 ║
║  1 ║ DataA12 ║  1 ║ DataB11 ║
║  1 ║ DataA13 ║  1 ║ DataB11 ║
║  2 ║ DataA21 ║  2 ║ DataB21 ║
║  2 ║ DataA21 ║  2 ║ DataB22 ║
║  2 ║ DataA21 ║  2 ║ DataB23 ║
║  3 ║ DataA31 ║    ║         ║
║    ║         ║  4 ║ DataB41 ║
╚════╩═════════╩════╩═════════╝

Unir a la izquierda en la columna Id devolverá las columnas solo de la tabla izquierda y los registros coincidentes solo de la tabla izquierda:

┌────┬─────────┐
│ Id │  Data   │
├────┼─────────┤
│  1 │ DataA11 │
│  1 │ DataA12 │
│  1 │ DataA13 │
│  2 │ DataA21 │
└────┴─────────┘

Answer 3

Un INNER JOIN devuelve las columnas de ambas tablas. Un LEFT SEMI JOIN solo devuelve los registros de la tabla de la izquierda. Es equivalente a (en SQL estándar):

SELECT name
FROM table_1 a
WHERE EXISTS(
    SELECT * FROM table_2 b WHERE (a.name=b.name))

Si hay varias filas coincidentes en la columna de la derecha, un INNER JOIN devolverá una fila para cada columna coincidente, mientras que un LEFT SEMI JOIN solo devolverá las filas de la tabla de la izquierda. Es por eso que estás viendo un número diferente de filas en tu resultado.

Estoy tratando de obtener los nombres dentro de la tabla_1 que solo aparecen en la tabla_2.

Entonces, una LEFT SEMI JOIN es la consulta apropiada para usar.