Tuesday, September 18, 2012

Case Statements in SQL

One of the complains that businesses have is that reporting is slow. There are multiple ways that I have seen developers handling this using caching putting in pagination in place etc. My thought process for reporting queries why  not write good SQL queries and fetch data efficiently rather than putting in hacks at the application level. Now, there are few things that you will need to take care of at the database level like putting indexes on the right columns using unions over joins or vice a versa. I am not going to get into all of that here. I will be talking about one of the biggest boons that the SQL programming language offers the "CASE" statement.

CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END
Or
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END

Lets begin with a small example where it could be used as a "CASE"  programming language construct. Lets say you are building a report for a teacher to display count of students in various Grades. Now instead of writing the logic at the application level to iterate over the student records and create a hash of grades and the count of students for each key. A more efficient way would be doing it through a SQL query and a "CASE" statement.

SELECT DISTINCT(s.grades),
CASE s.grades
WHEN 'A' THEN COUNT(s.grades)
WHEN 'B' THEN COUNT(s.grades)
WHEN 'C' THEN Count(s.grades)
ELSE Count(0)
END AS number_of_students
FROM students s
GROUP BY s.grades

If calculations are involved or you want to use the sql case statement as if-else if-else we can also do that. Lets take an example where you want to calculate grades for students based on their marks:
SELECT DISTINCT(s.grades),
CASE
WHEN s.grades BETWEEN 90 AND 100 THEN SUM(1)
WHEN s.grades BETWEEN 80 AND 90 THEN SUM(1)
WHEN s.grades BETWEEN 60 AND 80 THEN SUM(1)
ELSE Count(0) END AS number_of_students
FROM students s
GROUP BY s.grades;

A CASE statement in SQL is just an expression that needs to return a value and can be used in group by and order by clauses of a query as well for example: 
ORDER BY events.date, (CASE(events.display_on_homepage) WHEN true THEN 0 when false then 1 ELSE 2 END) ASC

Again using CASE statements will not make the the performance better but I would take the business logic closer to the database. It could contested that case statements are the best way of doing things. Our Ruby on Rails development team at Idyllic Software believe it is. Feel free to pass on you comments/suggestions etc. I have used case statements to a good extent in Mysql and Postgres. I hope it works in other databases.