{"id":1161,"date":"2018-01-14T08:39:52","date_gmt":"2018-01-14T15:39:52","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1161"},"modified":"2025-02-08T05:41:34","modified_gmt":"2025-02-08T12:41:34","slug":"sql-decode","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-comparison-functions\/sql-decode\/","title":{"rendered":"SQL DECODE"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL\u00a0DECODE()<\/code> function to add if-then-else logic to queries.<\/p>\r\n\r\n\r\n\r\n

Introduction to SQL DECODE() function #<\/a><\/h2>\r\n\r\n\r\n\r\n

The SQL DECODE()<\/code> function allows you to add procedure if-then-else logic to queries<\/a>. For example:<\/p>\r\n\r\n\r\n

SELECT<\/span> DECODE<\/span>(1<\/span>,1<\/span>,'Equal'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this example, the DECODE()<\/code> function compares the first argument (one) with the second argument (also one).<\/p>\r\n\r\n\r\n\r\n
Since the first argument equals the second one, the function returns the third argument which is the string Equal<\/code>.<\/p>\r\n\r\n\r\n\r\n
The DECODE()<\/code> function above works the same as the following IF<\/code> statement:<\/p>\r\n\r\n\r\n
IF 1 = 1 THEN\r\n    RETURN 'Equal';\r\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
The following example returns NULL<\/code> because the first argument is not equal to the second one.<\/p>\r\n\r\n\r\n
SELECT<\/span> DECODE<\/span>(1<\/span>,2<\/span>, 'Equal'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It works like the following statement:<\/p>\r\n\r\n\r\n
IF 1 = 2 THEN\r\n    RETURN 'Equal';\r\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
If you want to specify the value when the first argument is not equal to the second one, you use the following form of the DECODE()<\/code> function:<\/p>\r\n\r\n\r\n
SELECT<\/span> DECODE<\/span>(1<\/span>,2<\/span>, 'Equal'<\/span>, 'Not Equal'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
It works like the following IF-THEN-ELSE statement:<\/p>\r\n\r\n\r\n
IF 1 = 2 THEN\r\n    RETURN 'Equal';\r\nELSE\r\n    RETURN 'Not Equal';\r\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In general, you can compare the first argument of the DECODE()<\/code> function with a list of arguments, as shown in the following example:<\/p>\r\n\r\n\r\n
SELECT<\/span> DECODE<\/span> (2<\/span>, 1<\/span>, 'Equal 1'<\/span>, 2<\/span>, 'Equal 2'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
This example works as the following IF-THEN-ELSE IF statement:<\/p>\r\n\r\n\r\n
IF 2 = 1 THEN\r\n     RETURN 'Equal 1';\r\nELSE IF 2 = 2 \r\n    RETURN 'Equal 2';\r\nEND<\/span> IF<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
See the following example:<\/p>\r\n\r\n\r\n
SELECT<\/span> DECODE<\/span>(3<\/span>,1<\/span>, 'Equal 1,'<\/span>, 2<\/span>, 'Equal 2'<\/span>, 'Not Equal 1 or 2'<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
This example works like the following IF-THEN-ELSEIF-ELSE statement:<\/p>\r\n\r\n\r\n
IF 3 = 1 THEN \r\n    RETURN 'Equal 1';\r\nELSE IF 3 =2 THEN\r\n    RETURN 'Equal 2';\r\nELSE\r\n    RETURN 'Not Equal 1 or 2';\r\nEND<\/span> IF<\/span>;\r\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
SQL DECODE() function syntax #<\/a><\/h2>\r\n\r\n\r\n\r\n
The following illustrates the syntax of the SQL DECODE()<\/code> function:<\/p>\r\n\r\n\r\n
DECODE (e , s1, r1[, s2, r2], ...,[,sn,rn] [, d]);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
In this syntax:<\/p>\r\n\r\n\r\n\r\n