What are the advantages of using CASE statement?
-
Combines multiple scans
-
Reduce number of calls to the database
-
Performs many small scans as compared to one large scan
-
Makes more smaller calls to the database as compared to one large call
CASE expressions in SQL let you implement conditional logic within a single statement. This allows you to combine what would otherwise be multiple queries or scans into one operation, reducing roundtrips to the database and overall execution time.
A CASE (or DECODE) statement lets you evaluate multiple conditions against the same row set in a single pass, effectively combining what would otherwise be several separate scans of the same table into one scan. Because the database only has to be queried once to compute all the conditional results, it also reduces the number of round-trips/calls made to the database. The other two options describe the opposite effect (more, smaller scans/calls), which is what CASE is specifically designed to avoid.