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

select 
     body 
from node_revisions A 
where exists (select 'x' 
              from Node B 
              Where A.Vid = B.Vid and B.NID=4)

Answer 2

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

Answer 3

The latest MySQL 6.x code will automatically convert that IN expression into an INNER JOIN using a semi-join subquery optimization, making the 2 statements largely equivalent:

http://forge.mysql.com/worklog/task.php?id=3740

but, actually writing it out is pretty simple to do, because INNER JOIN is the default join type, and doing this wouldn't rely on the server optimizing it away (which it might decide not to for some reason and which wouldn't be portable necessarily). all things being equal, why not go with:

select body from node_revisions r, node n where r.vid = n.vid and n.node = 4

Answer 4

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 5

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 6

I don't see anything wrong with what you wrote, and a good optimizer may even change it to a join if it sees fit.

Answer 7

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

This query is logically equivalent to a join if and only if nid is a PRIMARY KEY or is covered by a UNIQUE constraint.

Otherwise, the queries are not equivalent: a join will always succeed, while the subquery will fail if there are more that 1 row in node with nid = 4.

If nid is a PRIMARY KEY, then the JOIN and the subquery will have same performance.

In case of a join, node will be made leading

In case of a subquery, the subquery will be executed once and transformed into a const on parsing stage.

Answer 8

A join is interesting:

select body 
from node_revisions nr
join node n on nr.vid = n.vid
where n.vid = 4

But you can also express a join without a join [!]:

select body 
from node_revisions nr, node n
where n.nid = 4 and nr.vid = n.vid

Interestingly enough, SQL Server gives a slight different query plan on both queries, while the join has a clustered index scan, the "join without a join" has a clustered index seek in its place, which indicates it's better, at least in this case!