The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages:
CASEWHEN condition THEN result [WHEN...][ELSE result]END
CASE clauses can be used wherever an expression is valid. Each condition is an expression that returns a boolean result. If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed. If the ELSE clause is omitted and no condition is true, the result is null. The data types of all the result expressions must be convertible to a single output type. There is a simple form of CASE expression that is a variant of the general form above:
CASE expression WHENvalueTHEN result [WHEN...][ELSE result]END
The first expression is computed, then compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If not match is found, the result of the ELSE clause is returned. A CASE expression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT...WHERECASEWHEN x <>0THEN y/x >1.5ELSEfalseEND;
COALESCE
COALESCE(value[,...])
The COALESCE function returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute default values when data is retrieved for display. The arguments must all be convertible to a common data type, which will be the type of the result. COALESCE only evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated.
NULLIF
NULLIF(value1,value2) NULLIF(value,' (none) ')-- IF value is ' (none) ', NULL is returned
The NULLIF function returns a null value if value1 equals value2; otherwise it returns value. This can be used to perform the inverse operation of COALESCE. The two arguments must be comparable types.
GREATEST and LEAST
GREATEST(value[,...]) LEAST(value[,...])
The GREATEST and LEAST functions select the largest r smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result.
NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
Are you sure you want to delete this article section? You can not undo this change.
Add a Comment
Annotate Article
Share Article
Successfully copied article URL to clipboard!
Something went wrong copying the article URL to the clipboard.
Successfully copied editor state to clipboard!
A previous, saved version of this article is saved. To compare the current version of the article with the previous version - or to replace the current version with the saved version - click the icon above the text editor.
Successfully copied URL to clipboard!
Copied code to clipboard.
Copied TeX code to clipboard.
Uploaded file must be an image of type .jpeg, .jpg, .png, .webp, .gif, .bmp, or .svg.
Image Size must be less than 5MB.
Uploaded file must be an image of type jpeg, jpg, png, webp, avif, tiff, or svg.
Something went wrong uploading the image to the database. Try reloading the page.
The maximum number of images you can upload is 30.
There was an error taking an image using the device's camera. Try uploading an image instead.
Uploaded file must be an audio file.
Audio file must be an audio file of type .m4a, .flac, .mp3, .mp4, .wav, .wma, .aac, .webm, or .mpeg.
Audio file must be less than 300 MB in size.
Something went wrong uploading the audio file. Try reloading the page.
The maximum number of audio files you can upload is 10.
There was an error capturing an audio recording using the device's microphone. Try uploading an audio recording or video instead.
Uploaded file must be an video file.
Video file must be an audio file of type .mp4, .mov, .avi, .wmv, .avchd, .webm, or .flv.
Video file must be less than 300 MB in size.
Something went wrong uploading the video file. Try reloading the page.
The maximum number of video files you can upload is 10.
There was an error taking a video using the device's camera / microphone. Try uploading a video instead.
Comments
You have to be logged in to add a comment
User Comments
There are currently no comments for this article.