2
\$\begingroup\$

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

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks for answering! All the answers are great, but I accepted this one because it offers a great mix of examples and reasoning. \$\endgroup\$ – Supuhstar Dec 31 '14 at 7:04
2
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thanks! I didn't know union was so unpredictable! \$\endgroup\$ – Supuhstar Dec 31 '14 at 16:05
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Thank you for your answer! It's very informative. I'd accept it if there were some code examples. \$\endgroup\$ – Supuhstar Dec 31 '14 at 7:05
1
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ Thanks for your answer! It helped a lot. I'd accept it if there were some more reasoning to your code. \$\endgroup\$ – Supuhstar Dec 31 '14 at 7:05
  • \$\begingroup\$ A small query: What if any of the IDs say 14 or 1 does not exist? Do you still want 5 or ou wish to ignore the same? \$\endgroup\$ – thepace Dec 31 '14 at 7:08
  • \$\begingroup\$ Why would the IDs change? \$\endgroup\$ – Supuhstar Dec 31 '14 at 7:09
  • \$\begingroup\$ I meant if the IDs didn't occur. Currently you are hardcoding it to 14, 1, 5. Suppose the input is 15 which is not in the table, would you proceed with the selections for 1, 5 or do you want 14,1,5 only if all the three entries exists in the database? \$\endgroup\$ – thepace Dec 31 '14 at 7:12
  • \$\begingroup\$ I'd skip any that aren't in the database. \$\endgroup\$ – Supuhstar Dec 31 '14 at 7:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.