Drux Drux - 3 months ago 7
SQL Question

Shorthand notation for comparing composite keys (in PostgreSQL)

I have two tables like so:

CREATE TABLE a (a1 TEXT, a2 INTEGER, a3 INTEGER,
PRIMARY KEY (a2, a3));
CREATE TABLE b (b1 TEXT, b2 INTEGER, b3 INTEGER,
FOREIGN KEY (b2, b3) REFERENCES a);


I would like to obtain
a1
for those rows in
a
that are referenced by at least one row in
b
. E.g. like so:

SELECT a1 FROM a
WHERE EXISTS (SELECT * FROM b WHERE a.a2 = b.b2 AND a.a3 = b.b3);


Is there a shorter (and efficient) way to express this, preferably one that would not require me to list all columns in composite keys explicitly, for that list may change?

Answer

One short-cut you can do in Postgres is:

SELECT a1
FROM a
WHERE (a.a2, a.a3) IN (SELECT b.b2, b.b3 FROM b) ;

Of course, you still have to list the keys.

If b has no duplicates and if the column names were the same, you could do:

SELECT a1
FROM a JOIN
     b
     USING (a2, a3);

With USING, you only have to list the keys once.

I hesitate to say this, but you could also further reduce this using NATURAL JOIN, assuming that the common keys have the same name and no other columns have the same name:

SELECT a1
FROM a NATURAL JOIN
     b;

However, I strongly discourage the use of NATURAL JOIN because it uses all columns in the two tables that have the same name. It doesn't respect declared foreign key relationships, so it can return unexpected results.

Comments