Probably the first thing we learn in SQL (Structured Query Language) is a simple SELECT statement to draw information out of the database.
Let's assume we're working with a membership table (members) in our database that has first name (fname) and last name (lname) attributes. We can draw the names out of the database like this:
1 | SELECT fname,lname FROM members; |
So far so good. Now lets say we want to include informal names for those who use them, like 'Rob' for 'Robert' or 'Bill' for 'William', so we add another attribute, iname for the informal name.
However not everyone has an informal name, so we can't just change our query to reflect iname instead of fname. We need to use a conditional query to select iname if it exists, otherwise fname. Here's how to do it;
1 | SELECT IF(iname !='',iname,fname),lname FROM members; |
In plain English (or pseudocode) we are saying "If there's a value for iname, use it, otherwise use the value in fname."
We can dress this up just a bit more with the concat() function, which we use to contactenate, or paste, strings together.
1 | SELECT concat(IF(iname !='',iname,fname),' ',lname) AS "FULL Name" FROM members; |
This will return the name as one attribute called "Full Name" using the informal name if available (otherwise the first name) followed by a space and the last name.
There are some important advantages to doing this in the database query rather than a server-side scripting language like PHP or ASP. First, you're moving some of the processing work to the SQL server, which balances the load between servers. Second, you're simplifying our server side scripting code by delivering it a clean set of data which can be used as it is. The conditional clause in the SQL query is much simpler than the code that would be required to do the same thing in PHP.
It's generally a good idea to do as much of your conditional processing as possible in the SQL query so your PHP or other server-side script code will be easier to write, maintain, and understand.