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

join, select, sql

Solution

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.

Problem

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?

Original source