SQL: Is a query like this OK or is there a more efficient way of doing it, like using a join?

Question

I often find myself wanting to write an SQL query like the following:

SELECT body 
  FROM node_revisions 
 where vid = (SELECT vid 
                FROM node 
               WHERE nid = 4);

I know that there are joins and stuff you could do, but they seem to make things more complicated. Are joins a better way to do it? Is it more efficient? Easier to understand?

Answer 1

Joins tend to be more efficient since databases are written with set operations in mind (and joins are set operations).

However, performance will vary from database to database, how the tables are structured, the amount of data in them and how much will be returned by the query.

If the amount of data is small, I would use a subquery like yours rather than a join.

Here is what a join would look like:

SELECT body 
FROM node_revisions nr
INNER JOIN node n
  ON nr.vid = n.vid
WHERE n.nid = 4

I would not use the query you posted, as there is chance of more than one node record with a nid = 4, which would cause it to fail.

I would use:

SELECT body 
FROM node_revisions 
WHERE vid IN (SELECT vid 
             FROM node 
             WHERE nid = 4);

Is this more readable or understandable? In this case, it's a matter of personal preference.

Answer 2

I think joins are easier to understand and can be more efficient. Your case is pretty simple, so it is probably a toss-up. Here is how I would write it:

SELECT body 
  FROM node_revisions 
    inner join node 
      on (node_revisions.vid = node.vid)
  WHERE node.nid = 4

Answer 3

The answer to any performance related questions in databases is it depends, and we're short on details in the OP. Knowing no specifics about your situation... (thus, these are general rules of thumb)

Joins are better and easier to understand

• If for some reason you need multiple column keys (fishy), you can continue to use a join and simply tack on another expression to the join condition.
• If in the future you really do need to join auxiliary data, the join framework is already there.
• It makes it more clear exactly what you're joining on and where indexes should be implemented.
• Use of joins makes you better at joins and better at thinking about joins.
• Joins are clear about what tables are in play

Written queries have nothing to do with effiency*

The queries you write and what actually gets run have little to do with one another. There are many ways to write a query but only so few ways to fetch the data, and it's up to the query engine to decide. This relates mostly to indexes. It's very possible to write four queries that look totally different but internally do the same thing.

(* It's possible to write a horrible query that is inefficient but it takes a special kind of crazy to do that.)

select
  body

from node_revisions nr

join node n
on n.vid = nr.vid

where n.nid = 4