Inner Join vs Natural Join vs USING cláusula: ¿hay alguna ventaja?

Imagina 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); 

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

Todos dan casi los mismos resultados, sin duda 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 el primer formulario tiene una columna duplicada, ¿hay alguna ventaja real para las otras dos forms? ¿O son solo azúcar sintáctico?

Puedo ver la desventaja en las últimas forms es que se espera que haya nombrado sus keys primarias y extranjeras de la misma manera, lo que no siempre es práctico.

Ahora, aparte del hecho de que el primer formulario tiene una columna duplicada, ¿hay alguna ventaja real para las otras dos forms? ¿O son solo azúcar sintáctico?

TL; DR NATURAL JOIN se utiliza en un cierto estilo de progtwigción relacional que es más simple que el estilo SQL habitual. (Aunque cuando está embedded en SQL está cargado con el rest de la syntax de consulta SQL). Esto se debe a que usa directamente los operadores simples de lógica de pnetworkingicados , el lenguaje de precisión en ingeniería (incluida la ingeniería de software), ciencia (incluida informática) y las matemáticas, y además 2. simultánea y alternativamente usa directamente los operadores simples del á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 ocurrir un emparejamiento inapropiado de las columnas. Y ese puede ser el caso en un entorno de desarrollo particular. Pero en ese caso había un requisito de que solo se unieran ciertas columnas y NATURAL JOIN sin PROJECT no era apropiado . Entonces, estos arguments suponen que NATURAL JOIN se está utilizando de manera inapropiada. Además, los argumentadores ni siquiera son conscientes de que están ignorando los requisitos. Tales quejas son engañosas. (Además, los principios de layout de ingeniería de software de sonido conducen a no tener interfaces con tales características específicas).

Otra queja engañosa errónea relacionada del mismo campo es que "NATURAL JOIN ni siquiera toma en count las relaciones de keys extranjeras" . Pero cualquier unión está allí debido a los significados de la tabla , no a las restricciones . Las restricciones no son necesarias para consultar. Si se agrega una restricción, una consulta permanece correcta. Si se elimina una restricción, una consulta que depende de ella se vuelve incorrecta y se debe cambiar a una frase que no dependa de ella y que no haya tenido que cambiar . Esto no tiene nada que ver con NATURAL JOIN.


Usted ha descrito la diferencia de efecto: solo se devuelve una copy de cada columna común.

¿Existe alguna regla general para build una consulta SQL a partir de una descripción legible para el ser humano? :

Resulta que las expresiones de lenguaje natural y las expresiones lógicas y las expresiones de álgebra relacional y las expresiones de 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 subset) de la parte y , y las unidades z de la parte x son necesarias para ensamblar una unidad de la parte y .

De esta respuesta :

Cada tabla base tiene una plantilla de statement, también conocida como pnetworkingicado , parametrizada por nombres de columna, mediante la cual colocamos una fila o la dejamos afuera.

Al conectar una fila en un pnetworkingicado, se obtiene una proposition, también conocida como proposition. Las filas que hacen que una proposition verdadera entre en una tabla y las filas que hacen una proposition falsa permanecen fuera. (Entonces, una tabla indica la proposition de cada fila presente y NO establece la proposition de cada fila ausente).

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

  • T NATURAL JOIN U tiene filas donde T (…) Y U (…)
  • T WHERE condition contiene filas donde T (…) AND condición
  • T UNION CORRESPONDING U mantiene filas donde T (…) O U (…)
  • T EXCEPT CORRESPONDING U mantiene filas donde T (…) AND NOT U (…)
  • SELECT DISTINCT columns to keep SELECT DISTINCT columns to keep FROM T contiene filas donde
    EXISTE columnas para tirar TAN QUE T (…)
  • etc

Mientras que el razonamiento sobre SQL de lo contrario es … no "natural":

Una sentencia SQL SELECT se puede considerar algebraicamente como 1. RENOMBRANDO implícitamente cada columna C de una tabla con el nombre de correlación (posiblemente implícito) T a TC , luego 2. CRUZANDO JUNTOS, luego 3. RESTRINGIENDO por ENTRADA INTERIOR, luego 4. RESTRINGIENDO por DONDE, luego 5. PROYECCIONAR por SELECCIONAR, luego 6. RENOMBRAR por SELECCIONAR, soltar T. s, luego 7. RENOMBRAR implícitamente para soltar las T. restantes s entre los operadores de álgebra T. -RENAME también se pueden considerar operadores lógicos y tabla nombres como sus pnetworkingicados: T JOIN ... vs Employee T.EMPLOYEE has name T.NAME ... AND ... Pero conceptualmente, dentro de una statement SELECT hay una tabla CROSS JOIN de inducción doble RENAME con TC s para los nombres de las columnas, mientras que las tablas externas tienen C s para los nombres de las columnas.

Alternativamente, una instrucción SQL SELECT se puede considerar lógicamente como 1. introduciendo FORSOME T IN E alnetworkingedor de la instrucción completa por nombre de correlación T y nombre base o subconsulta E , luego 2. refiriéndose al valor de T cuantificada utilizando TC para referirse a su C parte, luego 3. construyendo filas de resultados de TC s por FROM etc., luego 4. nombrando las columnas de la fila de resultados por la cláusula SELECT, luego 4. dejando el scope de las FORSOME s. De nuevo, los operadores de álgebra están siendo considerados como operadores lógicos y nombres de tabla como sus pnetworkingicados. De nuevo, sin embargo, conceptualmente tiene TC dentro de SELECT pero C fuera con nombres de correlación que van y vienen.

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

USING es una especie de huérfano de tierra media 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 duplicates allí. En este último, es más o less simplemente abreviar las condiciones de JOIN y las cláusulas SELECT.

Puedo ver la desventaja en las últimas forms es que se espera que haya nombrado sus keys primarias y extranjeras de la misma manera, lo que no siempre es práctico.

PKs (keys principales), FK (keys externas) y otras restricciones no son necesarias para consultar. (Saber que una columna es una function de otros permite subconsultas escalares, pero siempre se puede sintetizar). Además, se pueden unir dos tablas significativas. Si necesita dos columnas para tener el mismo nombre con NATURAL JOIN, cambie el nombre a través de SELECT AS.

Escribir JOIN realiza una UNIÓN INTERNA de forma pnetworkingeterminada. 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 tendrá solo 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á un duplicado de columnas de departamento en el resultado.

INNER JOIN es más fácil de leer, especialmente si su consulta tiene otros types de unión (IZQUIERDA o DERECHA o …) incluidos en ella.

A NATURAL JOIN supone las columnas con el mismo nombre en ambas tablas coincidentes. Por lo tanto, no puede realizar una NATURAL JOIN si, por ejemplo, en la tabla de empleados, su columna de unión se denomina "departamento" y en la tabla de departamentos su columna de unión se llama "departamento".

De la documentation de Oracle :

A NATURAL JOIN es una operación JOIN que crea una cláusula de unión implícita basada en las columnas comunes en las dos tablas que se unen. Las columnas comunes son columnas que tienen el mismo nombre en ambas tablas.

Una UNIÓN NATURAL puede ser una combinación INTERIOR, una combinación EXTREMO IZQUIERDO o una combinación DERECHO EXTERIOR. El valor pnetworkingeterminado es INNER join.

La cláusula

 TableA JOIN tableB USING(column) 

es, como ha notado, simplemente azúcar sintáctico para

 TableA JOIN tableB ON tableA.column = tableB.column 

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

Hay muchos arguments para que NATURAL JOIN sea una mala idea. Personalmente, creo que no nombrar explícitamente cosas como join implica invitar a desastres.

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

Cuando construya un sistema, nunca debe permitir que estos types de riesgos se filtren. Es lo mismo que crear vistas en varias tablas sin un alias de tabla en cada columna y usar inserción sin una list de columnas.

Por esas razones, si solo está aprendiendo SQL ahora, desactive el uso de estos.