sql - ejemplos - inner join

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

Question

Cada vez que hay una descripción de la consulta frente a nosotros, tratamos de aplicar heurística y lluvia de ideas para construir la consulta.

¿Existe alguna forma sistemática paso a paso o matemática para construir una consulta SQL a partir de una descripción dada legible por humanos?

Por ejemplo, cómo determinar eso, si una consulta SQL necesitaría una unión en lugar de una subconsulta, si requeriría un grupo por, si requeriría una cláusula IN, etc.

Por ejemplo, cualquiera que estudie Electrónica Digital estaría al tanto de los métodos como Karnaugh Map o Quin McClausky. Estos son algunos enfoques sistemáticos para simplificar la lógica digital.

¿Si hay algún método como este para analizar consultas SQL manualmente para evitar una lluvia de ideas cada vez?

Answer 1

¿Existe alguna forma sistemática paso a paso o matemática para construir una consulta SQL a partir de una descripción dada legible por humanos?

Sí hay.

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. (Lo que sigue es sin filas duplicadas ni nulos).

Cada tabla (base o resultado de la consulta) tiene un predicado asociado: una plantilla de declaración de espacios en blanco de relleno de lenguaje natural (con nombre) parametrizada por nombres de columna.

[liker] likes [liked]

Una tabla contiene cada fila que, utilizando los valores de columna de la fila para completar los espacios en blanco (con nombre), hace una declaración verdadera, también conocida como proposición .

liker  | liked
--------------
Bob    | Dex    /* Bob likes Dex */
Bob    | Alice  /* Bob likes Alice */
Alice  | Carol  /* Alice likes Carol */

Cada proposición de llenar un predicado con los valores de una fila en una tabla es verdadera. Y cada proposición de llenar un predicado con los valores de una fila que no está en una tabla es falsa.

/*
    Alice likes Carol
AND NOT Alice likes Alice
AND NOT Alice likes Bob
AND NOT Alice likes Dex
AND NOT Alice likes Ed
...
AND Bob likes Alice
AND Bob likes Dex
AND NOT Bob likes Bob
AND NOT Bob likes Carol
AND NOT Bob likes Ed
...
AND NOT Carol likes Alice
...
AND NOT Dex likes Alice
...
AND NOT Ed likes Alice
...
*/   

El DBA proporciona el predicado para cada tabla base. La sintaxis SQL para una declaración de tabla es muy similar a la taquigrafía lógica tradicional para la versión en lenguaje natural de un predicado dado.

/* (person, liked) rows where [liker] likes [liked] */
/* (person, liked) rows where Likes(liker, liked) */
SELECT * FROM Likes

Una (sub) expresión de consulta SQL transforma los valores de la tabla de argumentos en un nuevo valor de tabla que contiene las filas que hacen una declaración verdadera de un nuevo predicado. El nuevo predicado de tabla se puede expresar en términos del predicado (s) de la tabla de argumentos de acuerdo con los operadores relacionales / de tabla de la (sub) expresión. Una consulta es una expresión SQL cuyo predicado es el predicado para la tabla de filas que queremos.

Dentro de una instrucción SELECT :
• Una tabla base llamada T con alias A tiene predicado / es filas donde T(AC,...) .
• R CROSS JOIN S & R INNER JOIN S tienen predicado / son filas donde the predicate of R AND the predicate of S (Las filas que son una combinación de una fila de cada argumento alias A después de renombrar sus columnas C,... a AC,... )
• La R ON condition y la R ON condition R WHERE condition tienen predicado / son filas donde the predicate of R AND condition .
• SELECT DISTINCT AC AS D,... FROM R (quizás con A implícito y / o AS D implícito) tiene predicado / es filas donde FOR SOME [value for] luego descartó columnas y luego the predicate of R con AC,... reemplazado por D,... (Las columnas descartadas no son parámetros del nuevo predicado).
• Equivalentemente, seleccione SELECT DISTINCT AC AS D,... FROM R tiene predicado / es filas donde FOR SOME A.*,..., AC=D AND ... AND the predicate of R (Esto puede ser menos compacto pero se parece más al SQL).
• (X,...) IN (R) significa predicate of R con columnas C,... reemplazado por X,...
• Entonces (...) IN (SELECT * FROM T) significa T(...) .

Lenguaje natural y taquigrafía para filas (persona, me gusta) donde [persona] es Bob y a Bob le gusta alguien a quien le gusta [me gusta] pero a quien no le gusta Ed.

/* (person, liked) rows where
for some value for x,
    [person] likes [x]
and [x] likes [liked]
and [person] = ''Bob''
and not [x] likes ''Ed''

/* (person, liked) rows where
FOR SOME [value for] x,
        Likes(person, x)
    AND Likes(x, liked)
    AND person = ''Bob''
    AND NOT Likes(x, ''Ed'')
*/

Reescribe usando los predicados de nuestras tablas base y luego SQL.

/* (person, liked) rows where
FOR SOME [values for] l1.*, l2.*,
        person = l1.liker AND liked = l2.liked
    AND Likes(l1.liker, l1.liked)
    AND Likes(l2.liker, l2.liked)
    AND l1.liked = l2.liker
    AND person = ''Bob''
    AND NOT Likes(l1.liked, ''Ed'')
*/
SELECT l1.liker AS person, l2.liked AS liked
FROM
    /* (l1.liker, l1.liked, l2.liker, l2.liked) rows where
        Likes(l1.liker, l1.liked)
    AND Likes(l2.liker, l2.liked)
    AND l1.liked = l2.liker
    AND l1.liker = ''Bob''
    AND NOT Likes(l1.liked, ''Ed'')
    */
Likes l1 INNER JOIN Likes l2
ON l1.liked = l2.liker
WHERE l1.liker = ''Bob''
AND NOT (l1.liked, ''Ed'') IN (SELECT * FROM Likes)

• R UNION CORRESPONDING S tiene predicado / es filas donde the predicate of R OR the predicate of S
• R EXCEPT S tiene un predicado / es filas donde the predicate of R AND NOT the predicate of S
• VALUES (X,...),... con columnas C,... tiene predicado / es filas donde (C = X AND ...) OR ...

/* (person) rows where
    (FOR SOME liked, Likes(person, liked))
OR  person = ''Bob''
*/
    SELECT liker AS person
    FROM Likes
UNION
    VALUES (''Bob'')

Entonces, si expresamos nuestras filas deseadas en términos de plantillas de declaración de lenguaje natural de la tabla base dada para que las filas sean verdaderas o falsas (para ser devueltas o no), entonces podemos traducir a consultas SQL que son anidaciones de shorthands lógicos y operadores y / o nombres de tablas y operadores. Y luego el DBMS puede convertirse totalmente en tablas para calcular las filas haciendo que nuestro predicado sea verdadero.

Consulte Cómo obtener datos coincidentes de otra tabla SQL para dos columnas diferentes: ¿Unión interna y / o Unión? re aplicando esto a SQL. (Otra autounión).
Consulte Álgebra relacional para el escenario bancario para obtener más información sobre las frases en lenguaje natural. (En un contexto de álgebra relacional).

Answer 2

Esto es lo que hago en consultas no agrupadas:

Puse en la cláusula FROM cuya tabla espero recibir cero o una fila de salida por fila en la tabla. A menudo, desea algo como "todos los clientes con ciertas propiedades". Luego, la tabla del cliente entra en la cláusula FROM .

Use combinaciones para agregar columnas y filtrar filas. Las uniones no deben duplicar filas. Una unión debe encontrar cero o una fila, nunca más. Eso lo mantiene muy intuitivo porque puede decir que "una combinación agrega columnas y filtra algunas filas".

Se deben evitar las subconsultas si una unión puede reemplazarlas. Las combinaciones se ven mejor, son más generales y, a menudo, son más eficientes (debido a las debilidades comunes del optimizador de consultas).

Cómo usar WHERE y las proyecciones es fácil.