SQL Decision Structures

[chili5]

SQL Decision Structures

If you have programmed before, then these will be familiar. The two control structures available are: CASE and IF.

If... Else

These functions return a value depending on whether a condition is true or not.

Think of it like this:

if ((x == x) == true) {
	return "X"
} else {
	// false
	return "Y";
}

The generic syntax for this function is:

IF (expr,val1,val2)

The expression that we are testing is the first parameter. If expr is true, then the return value is val, otherwise the return value is val2.

Example:

SELECT IF(5>2,'a',4)

If 5 is greater than 2, the return value is 'a'. If 5 is less than 2 the return value is 4. Since 5 is greater than 2, the return value is 'a'.

Another example:

SELECT IF(3>4,'greater','less')

Result:

less

You can nest if structures by including an if structure as one of the return values. If you have used Excel before then this notation will be familar to you. You can make more complicated statements using AND and or operators.

This might be useful when you want to return data from one table, if a certain condition is true or from a different table if the condition is false.

Switch Case

The switch case structure is a concise method of testing one variable against multiple values.

The generic syntax is:

SELECT CASE value WHEN compareValue THEN result WHEN compare2 THEN result ELSE result END;

Example:

SELECT CASE 1 WHEN 5 THEN '5' WHEN 4 THEN '4' WHEN 3 THEN '3' ELSE 1 END;

Output:

1

This is a useless example but you might want to test a value that the user has entered and return a certain value depending on what the value is, or insert it into a different table depending on the value.

[WingedPanther]

CASE can be really handy with metadata with certain types of databases. Some of them represent things like VARCHAR as odd numeric codes internally. +rep

[Jordan]

I've rarely used if/else and I don't think I even knew case structures existed in SQL! Nice one, +rep.