una - unir dos consultas sql misma tabla

Unir tablas en columnas de clave externa/primaria compuesta en una consulta

Question

CREATE TABLE subscription (
   magazine_id bigint,
   user_id     bigint,
   PRIMARY KEY (magazine_id, user_id)
)

CREATE TABLE delivery (
   magazine_id bigint,
   user_id     bigint,
   FOREIGN KEY (subscription) REFERENCES subscription (magazine_id, user_id)
)

¿Cuál es una buena manera de consultar las entregas dadas una suscripción particular? ¿Hay alguna forma de asignar un nombre de columna a PRIMARY KEY (magazine_id, user_id) y la clave externa correspondiente para que pueda consultar de esta manera?

SELECT *
FROM subscription
JOIN delivery ON (delivery.subscription_fk = delivery.subscription_pk)

Nota: puedo escribir algo como esto:

SELECT *
FROM subscription
JOIN delivery ON (delivery.magazine_id = subscription.magazine_id
              AND delivery.user_id = subscription.user_id)

Sin embargo, tengo la impresión de que hay una manera menos detallada de lograrlo.

Answer 1

¿La delivery no tiene dos columnas que representan la clave externa? Entonces debería funcionar como con una clave primaria no compuesta SELECT * FROM subscription JOIN delivery ON (delivery.magazine_id = subscription.magazine_id AND delivery.user_id = subscription.user_id) .

Answer 2

Hay una NATURAL JOIN :

SELECT *
FROM subscription NATURAL JOIN delivery

Citando el manual en SELECT :

NATURAL

NATURAL es una abreviatura de una lista de USING que menciona todas las columnas en las dos tablas que tienen los mismos nombres.

Funcionaría para la configuración de su prueba, pero no es estrictamente hacer lo que pide . La conexión se basa en todas las columnas que comparten el mismo nombre. Las claves foráneas no son consideradas.

Simplificar código / menos detallado

Para empezar, puede usar alias de tablas y no necesita paréntesis en las condiciones de unión con ON (a diferencia de USING ):

SELECT *
FROM   subscription s
JOIN   delivery     d ON d.magazine_id = s.magazine_id
                     AND d.user_id = s.user_id;

Dado que los nombres de columna en las condiciones de unión son idénticos, puede simplificar aún más USING :

SELECT *
FROM   subscription s
JOIN   delivery     d USING (magazine_id, user_id);

No hay una variante de sintaxis que haga combinaciones basadas en restricciones de clave externa automáticamente. Tendría que consultar los catálogos del sistema y construir el SQL dinámicamente para eso ...