sql - Cláusula Inner Join vs Natural Join vs USING: ¿hay alguna ventaja?

Question

Imagine que tengo dos tablas simples, como:

CREATE TABLE departments(dept INT PRIMARY KEY, name);
CREATE TABLE employees(id PRIMARY KEY, fname, gname,
    dept INT REFERENCES departments(dept));

(simplificado, por supuesto).

Podría tener cualquiera de las siguientes declaraciones:

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;
SELECT * FROM employees e NATURAL JOIN departments d;
SELECT * FROM employees e JOIN departments d USING(dept);

Puede encontrar un ejemplo de trabajo aquí: SQL Fiddle: http://sqlfiddle.com/#!15/864a5/13/10

Todos dan casi los mismos resultados, ciertamente las mismas filas.

Siempre he preferido la primera forma debido a su flexibilidad, legibilidad y previsibilidad: usted define claramente qué está conectado a qué.

Ahora, aparte del hecho de que la primera forma tiene una columna duplicada, ¿hay alguna ventaja real para las otras dos formas? ¿O son solo azúcar sintáctico?

Puedo ver que la desventaja en las últimas formas es que se espera que hayas nombrado tus claves principales y externas de la misma manera, lo que no siempre es práctico.

Answer 1

Ahora, aparte del hecho de que la primera forma tiene una columna duplicada, ¿hay alguna ventaja real para las otras dos formas? ¿O son solo azúcar sintáctico?

TL; DR NATURAL JOIN se utiliza en un cierto estilo de programación relacional que es más simple que el estilo SQL habitual. (Aunque cuando está incrustado en SQL, está cargado con el resto de la sintaxis de consulta SQL). Esto se debe a que 1. utiliza directamente los operadores simples de lógica de predicados , el lenguaje de precisión en ingeniería (incluida la ingeniería de software), la ciencia (incluida la informática) y matemáticas, y además 2. simultáneamente y alternativamente , utiliza directamente los operadores simples de álgebra relacional .

La queja común sobre NATURAL JOIN es que, dado que las columnas compartidas no son explícitas, después de un cambio de esquema puede producirse un emparejamiento de columna inapropiado. Y ese puede ser el caso en un entorno de desarrollo particular. Pero en ese caso se requería que solo se unieran ciertas columnas y NATURAL JOIN sin PROJECT no era apropiado . Entonces, estos argumentos suponen que NATURAL JOIN se está utilizando de manera inapropiada. Además, los defensores ni siquiera son conscientes de que están ignorando los requisitos. Tales quejas son engañosas. (Además, los principios de diseño de ingeniería de software de sonido conducen a no tener interfaces con tales especificaciones específicas).

Otra queja engañosa errónea relacionada del mismo campo es que "NATURAL JOIN ni siquiera tiene en cuenta las relaciones de claves extranjeras" . Pero cualquier combinación está ahí debido a los significados de la tabla , no a las restricciones . Las restricciones no son necesarias para consultar. Si se agrega una restricción, la consulta sigue siendo correcta. Si se elimina una restricción, una consulta que se basa en ella se vuelve incorrecta y debe cambiarse a una fraseología que no se base en ella y que no hubiera tenido que cambiar . Esto no tiene nada que ver con NATURAL JOIN.

Ha descrito la diferencia en efecto: solo se devuelve una copia de cada columna común.

¿Existe alguna regla general para construir una consulta SQL a partir de una descripción legible por humanos? :

Resulta que las expresiones de lenguaje natural y las expresiones lógicas y las expresiones de álgebra relacional y las expresiones SQL (un híbrido de las dos últimas) se corresponden de una manera bastante directa.

Por ejemplo, de Codd 1970 :

La relación representada se llama componente . [...] El significado del componente ( x , y , z ) es que la parte x es un componente inmediato (o subconjunto) de la parte y , y se necesitan las unidades z de la parte x para ensamblar una unidad de la parte y .

De esta respuesta :

Cada tabla base tiene una plantilla de declaración, también conocida como predicado , parametrizada por nombres de columna, mediante la cual colocamos una fila o la dejamos fuera.

Al conectar una fila a un predicado se obtiene una proposición, también conocida como proposición. Las filas que hacen una proposición verdadera van en una tabla y las filas que hacen una proposición falsa quedan fuera. (Entonces, una tabla establece la proposición de cada fila presente y NO la proposición de cada fila ausente).

Pero cada valor de expresión de tabla tiene un predicado por su expresión. El modelo relacional está diseñado de modo que si las tablas T y U contienen filas donde T (...) y U (...) (respectivamente), entonces:

• T NATURAL JOIN U contiene filas donde T (...) Y U (...)
• T WHERE condition contiene filas donde T (...) AND condición
• T UNION CORRESPONDING U contiene filas donde T (...) O U (...)
• T EXCEPT CORRESPONDING U contiene filas donde T (...) Y NO U (...)
• SELECT DISTINCT columns to keep SELECT DISTINCT columns to keep FROM T contiene filas donde
  EXISTE columnas para soltar TAL QUE T (...)
• etc.

Mientras que el razonamiento sobre SQL no es ... no "natural":

Una instrucción SQL SELECT puede considerarse algebraicamente como 1. RENOMBRAR implícitamente cada columna C de una tabla con (posiblemente implícito) el nombre de correlación T a TC , luego 2. CROSS JOINing, luego 3. RESTRICTing per INNER ON, luego 4. RESTRICTing per DONDE, luego 5. PROYECCIÓN por SELECCIONAR, luego 6. RENOMBRAR por SELECCIÓN, descartar T. s, luego 7. RENOMBRAR implícitamente para descartar T. s restantes entre los operadores de álgebra T. -RENAMEings también pueden considerarse operadores lógicos y tabla nombres como sus predicados: T JOIN ... vs Employee T.EMPLOYEE has name T.NAME ... AND ... Pero conceptualmente dentro de una instrucción SELECT hay una tabla CROSS JOIN que induce RENAME doble con TC s para nombres de columna, mientras que las tablas externas tienen C s para nombres de columna.

Alternativamente, una declaración SELECT de SQL puede considerarse lógicamente como 1. introducir FORSOME T IN E alrededor de la declaración completa por nombre de correlación T y nombre base o subconsulta E , luego 2. referirse al valor de T cuantificado usando TC para referirse a su Parte C , luego 3. construyendo filas de resultados de TC s por FROM, etc., luego 4. nombrando las columnas de filas de resultados según la cláusula SELECT, luego 4. dejando el alcance de los FORSOME s. Nuevamente, los operadores de álgebra se consideran operadores lógicos y nombres de tablas como sus predicados. Sin embargo, de nuevo, esto conceptualmente tiene TC dentro de SELECT pero C afuera con nombres de correlación que van y vienen.

Estas dos interpretaciones de SQL no son tan sencillas como usar JOIN o AND, etc., indistintamente . (No tiene que aceptar que es más simple, pero esa percepción es la razón por la cual NATURAL JOIN y UNION / EXCEPT CORRESPONDING están ahí). (Los argumentos que critican este estilo fuera del contexto de su uso previsto son engañosos).

USAR es una especie de huérfano de término medio con un pie en el campamento NATURAL JOIN y otro en CROSS JOIN. No tiene un papel real en el primero porque no hay nombres de columna duplicados allí. En el último, se trata más o menos de abreviar condiciones JOIN y cláusulas SELECT.

Puedo ver que la desventaja en las últimas formas es que se espera que hayas nombrado tus claves principales y externas de la misma manera, lo que no siempre es práctico.

PK (claves primarias), FK (claves foráneas) y otras restricciones no son necesarias para realizar consultas. (Saber que una columna es una función de otros permite subconsultas escalares, pero siempre se puede expresar sin ella.) Además, se pueden unir dos tablas de manera significativa. Si necesita dos columnas para tener el mismo nombre con NATURAL JOIN, cambie el nombre mediante SELECT AS.

Answer 2

Al escribir JOIN, se realiza un INNER JOIN de forma predeterminada. Asi que:

SELECT * FROM employees e INNER JOIN departments d USING(dept);

es equivalente a

SELECT * FROM employees e JOIN departments d USING(dept);

y solo tendrá una columna de departamento en el resultado.

Del mismo modo

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;

es equivalente a

SELECT * FROM employees e JOIN departments d ON e.dept=d.dept;

pero tendrá columnas de departamento duplicadas en el resultado.

INNER JOIN es más fácil de leer, especialmente si su consulta tiene otros tipos de combinación (IZQUIERDA, DERECHA o ...) incluidos.

Una NATURAL JOIN asume las columnas con el mismo nombre en ambas coincidencias de tabla. Por lo tanto, no puede hacer una NATURAL JOIN si, por ejemplo, en la tabla de empleados su columna de combinación se denomina "departamento" y en su tabla de departamentos su columna de combinación se denomina "departamento"

Answer 3

De la documentación de Oracle :

Una UNIÓN NATURAL es una operación de UNIÓN que crea una cláusula de unión implícita para usted basada en las columnas comunes en las dos tablas que se están uniendo. Las columnas comunes son columnas que tienen el mismo nombre en ambas tablas.

Una UNIÓN NATURAL puede ser una combinación INTERNA, una combinación EXTERIOR IZQUIERDA o una combinación EXTERIOR DERECHA. El valor predeterminado es INNER join.

La cláusula

TableA JOIN tableB USING(column)

es, como notó, simplemente azúcar sintáctica para

TableA JOIN tableB ON tableA.column = tableB.column

Answer 4

Una ventaja importante de NATURAL JOIN es que es la única combinación SQL que no genera ''columnas duplicadas''.

Si todos los tipos de unión que no sean NATURAL JOIN fueran eliminados del lenguaje SQL, aún estaría relacionalmente completo. NATURAL JOIN es el único tipo de combinación que necesita.

Sin embargo, NATURAL JOIN no se introdujo en SQL hasta SQL92. En los primeros días de SQL, los diseñadores de lenguaje eligieron otros tipos de unión y, como consecuencia, tuvieron que encontrar una forma de tratar con ''columnas duplicadas''.

Considere el resultado de

employees e CROSS JOIN departments d

implica dos columnas llamadas dept porque cada tabla tiene un dept nombres de columna, es decir, ''columnas duplicadas''.

La solución elegida para el problema de ''columnas duplicadas'' fueron las variables de rango, d en el ejemplo anterior. [La mayoría de la gente SQL los llama ''alias de tabla'', lo cual es confuso porque una variable de rango representa una fila , en lugar de una tabla). El estándar SQL los llama ''nombres de correlación'', aunque no está claro qué significa ''correlación'' en contexto. Chris Date y LINQ llaman variables de rango, ¡y yo también!] Al usar los tipos de unión ''heredados'' y omitir las variables de rango, se usará un nombre de variable de rango igual al nombre de la tabla (quizás la raíz de la falacia de ''tabla aiias'' ?)

Usando el ejemplo del OP:

SELECT * FROM employees e INNER JOIN departments d ON e.dept=d.dept;

la proyección SELECT * contendrá ''columnas duplicadas''. Los nombres de las columnas y su orden en el resultado es una de esas cosas que el estándar SQL no intenta abordar; ya había demasiadas implementaciones divergentes para intentar estandarizar. En SQL Server obtengo nombres duplicados [suspiro].

Probablemente por estas razones, la mayoría de la gente de SQL dice: "Nunca use SELECT * ", etc. El enfoque habitual es especificar cada columna y ''proyectar'' cualquier duplicado. [Creo que una característica SELECT ALL BUT <set of columns> sería buena, pero creo que la gente SQL ha ganado el día.]

Cuando se usa NATURAL JOIN , no hay ''columnas duplicadas'' para ''proyectar'' y no hay predicados de unión, por lo tanto, no se necesitan variables de rango. Entonces, el ejemplo del OP debe reescribirse como:

SELECT * FROM employees NATURAL JOIN departments;

Aunque es cierto que si uno usa NATURAL JOIN exclusivamente y rechaza todos los demás tipos de unión, las variables de rango ya no son necesarias, SQL a veces aún las requiere. Usando el escenario de @ philipxy, "Si necesita dos columnas para tener el mismo nombre con NATURAL JOIN , cambie el nombre a través de SELECT AS " - si su implementación de SQL carece de expresiones de tabla comunes y se ve obligado a cambiar el nombre en una tabla derivada, entonces SQL requiere el tabla derivada para que se le asigne una variable de rango a pesar de que nunca volverá a consultarla.

Lwt''s dice que cada tabla en el ejemplo del OP tenía una columna de guid sin sentido que uno necesita ''proyectar'' para propósitos de NATURAL JOIN :

SELECT *
  FROM ( SELECT dep, name, FROM employees ) e 
       NATURAL JOIN ( SELECT dept, fname, gname departments ) d;

En este caso, sin las variables de rango e y d el SQL no sería válido (obtendría un error de análisis). La implementación de SQL no necesita las variables de rango (¡e incluso si lo hiciera, podría generar marcadores de posición internamente!), Pero el lenguaje SQL los requiere debido a los ''grilletes de compatibilidad'' de SQL donde ninguna característica se elimina o se deja de usar.

Answer 5

NATURAL JOIN no es tan compatible y tampoco JOIN USING (es decir, no en SQL Server)

Hay muchos argumentos para que NATURAL JOIN sea una mala idea. Personalmente, creo que no nombrar explícitamente cosas como las uniones es invitar al desastre.

Por ejemplo, si agrega una columna en una tabla sin darse cuenta de que se ajusta a una ''unión natural'', puede tener fallas inesperadas en el código cuando una unión natural de repente hace algo completamente diferente. Pensaría que agregar una columna no rompería nada, pero puede romper vistas mal escritas y una unión natural.

Cuando está creando un sistema, nunca debe permitir que este tipo de riesgos ingresen. Es lo mismo que crear vistas en varias tablas sin un alias de tabla en cada columna y usar insertar sin una lista de columnas.

Por esas razones, si solo está aprendiendo SQL ahora, deje el hábito de usarlos.