mysql

Two advanced MySQL tips today:

1. Never use a subquery inside a WHERE field IN(...) list

2. How-to: Update values in a table from a calculation that uses the table's own values, in a single query

I love databases because I always feel there is a way for retrieving my data in any shape or form I desire. Trying to find the correct syntax can sometimes be frustrating, however. Here are two tricks that have helped me during those special times:

1. Custom "ORDER BY"
   It's easy to order by ASC or DESC, but what if we want a custom order returned? This is when the FIELD() function comes in handy.
   SQL

   SELECT * FROM table_name ORDER BY FIELD(field_name, 'Small','Medium','Large');

   The disadvantage is that this will slow down your query. For maximum performance, store the field as an ENUM with the values defined in the correct order.

2. Retrieving values from multiple rows as a single comma-delimited field