SQLite Unión: Natural izquierda exterior, interior, cruz con tablas

SQLite soporta diferentes tipos de SQL Uniones, como INNER JOIN, LEFT OUTER JOIN y CROSS JOIN. Cada tipo de JOIN se utiliza para una situación diferente como veremos en este tutorial.

Introducción a los SQLite Cláusula UNIRSE

Cuando trabaja en una base de datos con varias tablas, a menudo necesita obtener datos de estas múltiples tablas.

Con la cláusula JOIN, puede vincular dos o más tablas o subconsultas uniéndolas. Además, puedes definir por qué columna necesitas vincular las tablas y por qué condiciones.

Cualquier cláusula JOIN debe tener la siguiente sintaxis:

Cada cláusula de unión contiene:

• Una tabla o una subconsulta que es la tabla de la izquierda; la tabla o la subconsulta antes de la cláusula de unión (a la izquierda de la misma).
• Operador JOIN: especifica el tipo de unión (INNER JOIN, LEFT OUTER JOIN o CROSS JOIN).
• Restricción JOIN: después de especificar las tablas o subconsultas para unir, debe especificar una restricción de unión, que será una condición en la que se seleccionarán las filas coincidentes que coincidan con esa condición según el tipo de unión.

Tenga en cuenta que, para todos los siguientes SQLite Ejemplos de UNIR tablas, debes ejecutar sqlite3.exe y abrir una conexión a la base de datos de muestra como se muestra a continuación:

Paso 1) En este paso, abra Mi PC y navegue hasta el siguiente directorio “C:\sqlite” y luego abra “sqlite3.exe”:

Paso 2) Abra la base de datos “TutorialsSampleDB.db” con el siguiente comando:

Ahora está listo para ejecutar cualquier tipo de consulta en la base de datos.

SQLite INNER JOIN

La cláusula INNER JOIN devuelve únicamente las filas que cumplen la condición de unión y elimina todas las demás filas que no la cumplen.

Ejemplo

En el siguiente ejemplo, uniremos las dos tablas “Estudiantes” y “Departamentos” con DepartmentId para obtener el nombre del departamento de cada estudiante, como sigue:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicación del código

La unión interna funciona de la siguiente manera:

• En la cláusula Seleccionar, puede seleccionar las columnas que desee de las dos tablas a las que se hace referencia.
• La cláusula INNER JOIN se escribe después de la primera tabla a la que se hace referencia con la cláusula "De".
• Luego, la condición de unión se especifica con ON.
• Se pueden especificar alias para las tablas a las que se hace referencia.
• La palabra INTERIOR es opcional, puedes simplemente escribir UNIRSE.

Resultado

La operación INNER JOIN genera los registros de ambas tablas (estudiantes y departamento) que cumplen la condición “Students.DepartmentId = Departments.DepartmentId”. Las filas que no coinciden se ignoran y no se incluyen en el resultado.

Por eso, esta consulta solo arrojó 8 de los 10 estudiantes de los departamentos de informática, matemáticas y física. Los estudiantes "Jena" y "George" no se incluyeron porque tienen un ID de departamento nulo, que no coincide con la columna departmentId de la tabla departments. A continuación:

SQLite ÚNETE… USANDO

INNER JOIN se puede escribir usando la cláusula "USING" para evitar redundancia, por lo que en lugar de escribir "ON Students.DepartmentId = Departments.DepartmentId", puede simplemente escribir "USING(DepartmentID)".

Puede utilizar "UNIRSE... USANDO" siempre que las columnas que comparará en la condición de unión tengan el mismo nombre. En tales casos, no es necesario repetirlos usando la condición on y simplemente indique los nombres de las columnas y SQLite detectará eso.

La diferencia entre INNER JOIN y JOIN .. USING:

Con “JOIN … USING” no se escribe una condición de unión, simplemente se escribe la columna de unión que es común entre las dos tablas unidas, en lugar de escribir table1 “INNER JOIN table2 ON table1.cola = table2.cola” lo escribimos como “table1 JOIN table2 USING(cola)”.

Ejemplo

En el siguiente ejemplo, uniremos las dos tablas “Estudiantes” y “Departamentos” con DepartmentId para obtener el nombre del departamento de cada estudiante, como sigue:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
INNER JOIN Departments USING(DepartmentId);

Explicación

• A diferencia del ejemplo anterior, no escribimos “ON Students.DepartmentId = Departments.DepartmentId”. Simplemente escribimos “USING(DepartmentId)”.
• SQLite infiere automáticamente la condición de unión y compara el DepartmentId de ambas tablas: Estudiantes y Departamentos.
• Puede utilizar esta sintaxis siempre que las dos columnas que está comparando tengan el mismo nombre.

Resultado

Esto le dará exactamente el mismo resultado que el ejemplo anterior:

SQLite UNIÓN NATURAL

UNA JOIN NATURAL es similar a una JOIN...USING, la diferencia es que automáticamente prueba la igualdad entre los valores de cada columna que existe en ambas tablas.

La diferencia entre UNIÓN INTERNA y UNIÓN NATURAL:

• En INNER JOIN, debes especificar una condición de unión que la operación utiliza para combinar las dos tablas. En cambio, en la unión natural, no se especifica ninguna condición; simplemente se escriben los nombres de las dos tablas sin ninguna condición. La unión natural comprueba automáticamente la igualdad de los valores de cada columna presente en ambas tablas, infiriendo la condición de unión automáticamente.
• En NATURAL JOIN, todas las columnas de ambas tablas con el mismo nombre se compararán entre sí. Por ejemplo, si tenemos dos tablas con dos nombres de columnas en común (las dos columnas existen con el mismo nombre en las dos tablas), entonces la unión natural unirá las dos tablas comparando los valores de ambas columnas y no solo de una. columna.

Ejemplo

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
Natural JOIN Departments;

Explicación

• No necesitamos escribir una condición de unión con nombres de columna (como hicimos en INNER JOIN). Ni siquiera necesitamos escribir el nombre de la columna una sola vez (como hicimos en JOIN USING).
• La unión natural escaneará ambas columnas de las dos tablas. Detectará que la condición debe consistir en comparar el ID de departamento de las dos tablas Estudiantes y Departamentos.

Resultado

La unión natural (NATURAL JOIN) dará exactamente el mismo resultado que los ejemplos de unión interna (INNER JOIN) y unión con cláusula USING (JOIN USING), ya que en nuestro ejemplo las tres consultas son equivalentes. Sin embargo, en algunos casos, el resultado de la unión interna será diferente al de la unión natural. Por ejemplo, si hay varias tablas con el mismo nombre, la unión natural comparará todas las columnas entre sí. En cambio, la unión interna solo comparará las columnas incluidas en la condición de unión.

SQLite IZQUIERDA ÚNICA EXTERNA

El estándar SQL define tres tipos de OUTER JOIN: LEFT, RIGHT y FULL, pero SQLite solo admite la UNIÓN EXTERIOR IZQUIERDA natural.

En LEFT OUTER JOIN, todos los valores de las columnas que selecciones de la tabla izquierda se incluirán en el resultado de la consulta, por lo que, independientemente de si el valor coincide o no con la condición de unión, se incluirá en el resultado.

Si la tabla de la izquierda tiene 'n' filas, los resultados de la consulta también tendrán 'n' filas. Sin embargo, para los valores de las columnas de la tabla de la derecha, si algún valor no coincide con la condición de unión, se mostrará como "nulo".

Entonces, obtendrá una cantidad de filas equivalente a la cantidad de filas en la combinación izquierda. De modo que obtendrá las filas coincidentes de ambas tablas (como los resultados de INNER JOIN), más las filas que no coinciden de la tabla de la izquierda.

Ejemplo

En el siguiente ejemplo, probaremos el “LEFT JOIN” para unir las dos tablas “Estudiantes” y “Departamentos”:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students             -- this is the left table
LEFT JOIN Departments ON Students.DepartmentId = Departments.DepartmentId;

Explicación

• SQLite La sintaxis de LEFT JOIN es la misma que INNER JOIN; escribe LEFT JOIN entre las dos tablas y luego la condición de unión viene después de la cláusula ON.
• La primera tabla después de la cláusula from es la tabla de la izquierda. Mientras que la segunda tabla especificada después del LEFT JOIN natural es la tabla derecha.
• La cláusula OUTER es opcional; LA UNIÓN EXTERNA natural IZQUIERDA es lo mismo que la UNIÓN IZQUIERDA.

Resultado

Como puede observar, se incluyen todas las filas de la tabla de estudiantes, que suman 10 estudiantes en total. Incluso si el cuarto y el último estudiante, Jena y George, tienen ID de departamento que no existen en la tabla de Departamentos, también se incluyen.

En estos casos, el valor de departmentName tanto para Jena como para George será "nulo" porque la tabla de departamentos no tiene un departmentName que coincida con su valor de departmentId.

Vamos a dar una explicación más detallada a la consulta anterior que utiliza la unión izquierda mediante diagramas de Venn:

La operación LEFT JOIN devolverá todos los nombres de los estudiantes de la tabla de estudiantes, incluso si el ID del departamento del estudiante no existe en la tabla de departamentos. Por lo tanto, la consulta no devolverá solo las filas coincidentes como la operación INNER JOIN, sino que también devolverá las filas no coincidentes de la tabla izquierda, que es la tabla de estudiantes.

Tenga en cuenta que cualquier nombre de estudiante que no tenga un departamento coincidente tendrá un valor "nulo" para el nombre del departamento, porque no hay ningún valor coincidente para él y esos valores son los valores en las filas que no coinciden.

SQLite UNIÓN CRUZADA

UNA UNIÓN CRUZADA proporciona el producto cartesiano para las columnas seleccionadas de las dos tablas unidas, al hacer coincidir todos los valores de la primera tabla con todos los valores de la segunda tabla.

Entonces, para cada valor en la primera tabla, obtendrá 'n' coincidencias de la segunda tabla, donde n es el número de filas de la segunda tabla.

A diferencia de INNER JOIN y LEFT OUTER JOIN, con CROSS JOIN no es necesario especificar una condición de unión, porque SQLite No lo necesita para la UNIÓN CRUZADA.

El SQLite Esto dará como resultado un conjunto de resultados lógicos al combinar todos los valores de la primera tabla con todos los valores de la segunda tabla.

Por ejemplo, si seleccionas una columna de la primera tabla (colA) y otra columna de la segunda tabla (colB). La columna colA contiene dos valores (1,2) y la columna colB también contiene dos valores (3,4).

Entonces el resultado de CROSS JOIN serán cuatro filas:

• Dos filas combinando el primer valor de colA que es 1 con los dos valores de colB (3,4) que serán (1,3), (1,4).
• De igual forma, dos filas combinando el segundo valor de la colA que es 2 con los dos valores de la colB (3,4) que son (2,3), (2,4).

Ejemplo

En la siguiente consulta intentaremos realizar un CROSS JOIN entre las tablas Estudiantes y Departamentos:

SELECT
  Students.StudentName,
  Departments.DepartmentName
FROM Students
CROSS JOIN Departments;

Explicación

• En la sección SQLite seleccione de varias tablas, simplemente seleccionamos dos columnas "nombre de estudiante" de la tabla de estudiantes y "nombre de departamento" de la tabla de departamentos.
• Para la combinación cruzada, no especificamos ninguna condición de combinación, solo las dos tablas combinadas con CROSS JOIN en medio de ellas.

Resultado

Como puede ver, el resultado son 40 filas; 10 valores de la tabla de estudiantes coincidentes con los 4 departamentos de la tabla de departamentos. Como se muestra a continuación:

• Cuatro valores para los cuatro departamentos de la tabla de departamentos coincidieron con el primer estudiante Michel.
• Cuatro valores de los cuatro departamentos de la tabla de departamentos coincidieron con el segundo estudiante, John.
• Cuatro valores para los cuatro departamentos de la tabla de departamentos coincidieron con el tercer estudiante Jack... y así sucesivamente.

Preguntas Frecuentes