# Simple SQL query to get phrase pairs from a translations table

This is probably the noobiest SQL question ever, and I'm almost positive there's a JOIN or something in PostgreSQL for this, but I can't find it so I'm using a naive method. Here's a bit about my schema:

phrases:

CREATE TABLE phrases
(
phrase_id bigserial NOT NULL,
language character varying(4) NOT NULL,
text text NOT NULL,
CONSTRAINT phrases_pk PRIMARY KEY (phrase_id)
)


translations:

CREATE TABLE translations
(
source_id bigint NOT NULL,
destination_id bigint NOT NULL,
CONSTRAINT translations_pk PRIMARY KEY (source_id, destination_id)
)


I use the following query to get 100 phrases (for two language pairs). This requires limiting the results to the language pairs, then finding their translations in the translations table:

SELECT P1.phrase_id, P1.language, P1.text, P2.phrase_id, P2.language, P2.text
FROM phrases P1, phrases P2, translations T
WHERE (P1.language = 'eng' AND P2.language = 'fra'
AND P1.phrase_id = T.source_id
AND P2.phrase_id = T.destination_id)
LIMIT 100


For what it's worth, the query runs in 34 ms with ~7.5m rows in the translations table, and 1m rows in the phrases table. I'll end up doing full text searches on the phrases table though, so I want to make the translation lookup as efficient as possible (as the translations table will grow ~5x faster than phrases). Lookup in translations is also the most common operation in this database.

Are there any fancy methods of accomplishing this more elegantly/efficiently in PostgreSQL?

P.S. - There is no foreign key constraint in the translations table because I am still in the process of sanitizing the database and removing old records. Importing into Postgres was impossible with the FK due to entries missing from phrases.

I recommend using more vertical space when you write a query to make it easier to read. That said, this should work:

SELECT
P1.phrase_id,
P1.language,
P1.text,
P2.phrase_id,
P2.language,
P2.text
FROM    phrases P1
INNER JOIN translations T
ON P1.phrase_id = T.source_id
INNER JOIN phrases P2
ON P2.phrase_id = T.destination_id
WHERE   P1.language = 'eng'
AND     P2.language = 'fra'
LIMIT 100;

• There's a syntax error after the second INNER JOIN (extra )). Also, oddly enough the EXPLAIN for my query and yours give the exact same output... It still ends up running a nested loop, but maybe that can't be avoided... +1 for the formatting suggestion though :) Do you have any other ideas? I'm not sure if this can even get more efficient now =/ Nov 19, 2014 at 14:46
• Whoops, good catch. I'm not surprised the query plan is the same (as it is doing the same thing) but yours are ANSI-92 joins and those can be problematic. This article may be interesting to you on that topic. Nov 19, 2014 at 16:38
• Ah, good read! I'll change that right away :P Nov 19, 2014 at 16:49