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);
SELECT a1 FROM a
WHERE EXISTS (SELECT * FROM b WHERE a.a2 = b.b2 AND a.a3 = b.b3);
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.
b has no duplicates and if the column names were the same, you could do:
SELECT a1 FROM a JOIN b USING (a2, a3);
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.