Thursday, January 8, 2015

[MySQL][SQL] Example of if and case sql statement in select statment

If statement:
SELECT id, IF( post_title = "Title 1", "TRUE", "FALSE" )
FROM  `wp_posts`
A closer look of the if part
IF( post_title = "Title 1", "TRUE", "FALSE" )
Text in blue is the condition part of the if statement, check if the value with column name “post_title” is equal to “Title 1”.
Text in green is the output if the “IF” statement returns true, string “TRUE” would be output in this example.
Text in red the is output if the “IF” statement returns false, string “FALSE” would be output in this example.


To make the results look better, set the column which used if statement as new_post_title:
SELECT id, post_title, IF( post_title = "Title 1", "TRUE", "FALSE" ) AS new_post_title
FROM `wp_posts`


And now let try an SQL with CONCAT to add some string after the returned result, you can  click here to make the sample table to try this example SQL:

SELECT id, post_title, IF( post_title =  "Title 1", CONCAT(  'TRUE: ', post_title ), post_title ) AS new_post_title
FROM  `wp_posts`


* To know more about CONCAT, please click here.

-------------------------

If you want to IF…THEN in an SQL SELECT, it’s better to use the CASE statement. CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server.

SELECT id,
    CASE post_title
        WHEN 'Title 1' THEN id
        WHEN 'Title 2' THEN -id
        WHEN 'This is Title 3' THEN -id
        ELSE 'Others title'
    END AS result
FROM wp_posts




Reference:
Nice article to read:

No comments :

Post a Comment