Selecting three stylesheets in a specific order

Question

I'm somewhat new to SQL (only had to use it in Database classes). I have a simple table of stylesheets, from which I want to select specific ones in a specific order (e.g. reset, then layout, then colors). Below is an example selector that my current algorithm might generate. Is there a better (more efficient or more intended use of keywords) way to do this?

SELECT *
  FROM `Stylesheets`
  WHERE
    ID = 14

UNION

SELECT *
  FROM `Stylesheets`
  WHERE
    ID = 1

UNION

SELECT *
  FROM `Stylesheets`
  WHERE
    ID = 5
;

See full example on SQLFiddle

Answer 1

I have a sneaking suspicion that this might should do an IN clause and then handle the sorting in code, but if you want to keep it in SQL, you can do:

SELECT *
  FROM Stylesheets
  WHERE
    ID IN (14, 1, 5)
  ORDER BY ID = 14, ID = 1, ID = 5;

Don't use backticks when you don't have to by the way. They're extra typing, and they pretty much kill portability (for no reason -- you'll eventually have to kill portability for some reason or other).

Also, I'm not a fan of ALL_CAPS identifiers. They blend in too closely with keywords which tend to be all caps ("ID IN" looks pretty odd for example).

Perhaps your actual use of this is different, but if 14, 1, and 5 are actually hard coded, that's a bit fragile. It would be better to assign them to some kind of constant and then use that in the query. Or, if you wanted to go a step farther, you could even assign them some kind of code in addition to an int primary key ('reset', 'layout', 'colors'), then not only are they less likely to change (what if they get inserted in a different order next time? suddenly all your IDs are wrong), they're also easier to remember, and the values of your constants will have more meaning.

Answer 2

More discussion on UNION vs. WHERE IN. In particular, note that WHERE IN is the equivalent of UNION ALL, not just UNION. That may not change the data returned in your situation, as it looks like a row can only match one ID. UNION may still be slower than UNION ALL though, as it may check for duplicates even if there are none.

Note that using IN will still cause three queries to be run behind the scenes. The speed pickup comes from how it integrates the results, not because it generates them better.

Be careful in using *. Unless you know that you need all the columns, you should prefer solutions that name the columns. This is especially important with TEXT type columns, but also may be relevant if all your selected and queried columns are in the same index. It's quite possible for a query to only hit the index in memory and ignore the table on disk.

Answer 3

Relying on magic numbers 14, 1, and 5 is cryptic, fragile, and poor programming practice. Surely there must be some other attributes of the rows of interest that you could query by, and if not, you should add some.

Since you know that you are expecting three distinct rows, with no possibility of overlap, you should use UNION ALL instead of UNION.

Your query won't necessarily produce results in the order you expect, because UNION provides no such guarantee:

Note that UNION does not guarantee the order of rows. Rows from the second operand may appear before, after, or mixed with rows from the first operand. In situations where a specific order is desired, ORDER BY must be used.

Answer 4

The other options along with the execution time:

SELECT *
FROM `Stylesheets`
WHERE ID = 14 OR ID = 1 OR ID = 5
ORDER BY ID = 14, ID = 1, ID = 5;
-- Execution Time: 2-4ms

SELECT *
FROM `Stylesheets`
WHERE ID IN (14,1,5)
ORDER BY ID = 14, ID = 1, ID = 5;
-- Execution Time: 2-4ms

-- For your code, execution time: 2-4ms

You could check the above results for a table with large number of entries to check which would be better.

Also we can query the where clause with ID in sorted order (as would be in the database) and order afterwards as we require.

Check this: https://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause