I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current user.
Originally I tried this:
SELECT content,select_start,select_end,select_colour FROM documents LEFT OUTER JOIN annotations USING (document_id) WHERE document_id=<some_document_id_here> AND (user_id=<some_user_id_here> or user_id IS NULL)
But it failed to return a document if another user had added annotations. So I'm now using this:
SELECT content,select_start,select_end,select_colour FROM documents A LEFT OUTER JOIN ( SELECT document_id,select_start,select_end,select_colour FROM annotations WHERE user_id=<some_user_id_here> ) B ON A.document_id = B.document_id WHERE A.document_id=<some_document_id_here>
which works, but the inner select statement could create quite a sizeable temporary table. I'd like to know if there's a better way to achieve this.