Simple SQL query to get phrase pairs from a translations table

Question

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.

Answer 1

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;