Conditional Statements in SQL: Boost Your Database Skills

What are Conditional Statements in SQL?

SQL conditional statements execute actions or statements based on conditional tests. If specific criteria are met, a conditional statement performs a specified action or combination of actions. It's important to note that while SQL conditional statements manage database connections and server activities, they do not filter data. They filter records based on one criterion and do not affect database performance.

Common SQL conditional statements include the CASE statement, the IF statement, and the NULLIF statement. Each has its unique use case and syntax, which we will explore in detail in this article.

The CASE Statement

SQL's CASE statement handles conditional expressions like IF-THEN-ELSE in other computer languages. If conditions are proper, a case statement returns THEN and terminates. If no condition is met or FALSE, it examines the ELSE sentence and stops.

Syntax

CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 WHEN condition_n THEN result_n… ELSE result END

Example

Indicate whether a student passed or failed a subject based on his marks, assuming he needed 250 out of 500.

SELECT student_id, first_name, last_name, subject, marks,
CASE
WHEN marks > 250 THEN 'PASSED'
ELSE 'FAILED'
END AS result
FROM students;

Output

Advance Your Career with SQL

SQL Certification CourseExplore Program

The DECODE Function

SQL DECODE replaces conditional values in query results. Oracle Database and other databases use it most. Data transformation, CASE statement simplification, and conditional value replacement are common uses.

Syntax

DECODE(expression, search1, result1, search2, result2, ..., default_result)

Example 

If the "students" table has this data:

If the "students" table has this data:
| student_id | score |
|------------|-------|
| 1          | 96    |
| 2          | 78    |
| 3          | 81    |
| 4          | 62    |
| 5          | 43    |

SELECT student_id, score,
       DECODE(
         WHEN score >= 90 THEN 'A'
         WHEN score >= 80 THEN 'B'
         WHEN score >= 70 THEN 'C'
         WHEN score >= 60 THEN 'D'
         ELSE 'F'
       ) AS grade
FROM students;

Output

This result gives letter grades based on student scores using DECODE.

COALESCE

COALESCE() is a valid SQL function that returns the first non-NULL parameter value. It is usually used for database NULL handling. Instead of replacing NULL values at the application level, it handles them upon data retrieval.

Syntax

COALESCE(value_1, value_2, ...., value_n)

Example

Check below the input table ‘customer_contact’:

ID
Phone_number
1
2
+9 312-985-7824
3
+9 187-765-4329

SELECT ID, COALESCE(Phone_number, 'NULL') AS Phone_Number
FROM customer_contact;

Output

This example uses COALESCE() to replace NULL entries in the Phone_Numbers column with ‘NULL’ for consistency.

Ready to level up your career in data management? Our SQL Certification Course is your gateway to mastering essential database skills. Enroll today and join thousands of professionals who have elevated their careers with our comprehensive program!

GREATEST

You can use the SQL GREATEST function to find the largest value in a set of values. GREATEST() accepts n parameters. 

Syntax

GREATEST ( expr1, [expr_n] )

Example

SELECT
GREATEST(6, 15, 25, 43, 69) AS GREATEST_CHECK,

Output

IFNULL

The SQL IFNULL function is essential for developers and analysts seeking to master database management. Its core function—seamlessly replacing NULL values with a defined alternative—enables data integrity and analysis.

Syntax  

IFNULL is built on simplicity and efficiency. This approach cleanly replaces NULL values with a given replacement.

IFNULL(expression, replacement_value)

Example 

When the parameter is an empty set, SUM, AVG, MAX, and MIN aggregate functions return null. In this example, the IFNULL function returns a value instead of a null when the function evaluates to an empty set:

IFNULL(SUM(employee.salary)/25, -1)

Output

Discover SQL and Database Management

SQL Certification CourseExplore Program

IN

The SQL IN condition (or IN operator) lets you quickly verify if an expression matches any entry in a list. It reduces the number of OR conditions in SELECT, INSERT, UPDATE, and DELETE statements.

Syntax 

The SQL IN condition syntax is:

expression IN (value1, value2, .... value_n);

or

expression IN (subquery);

Example 

The IN condition works with all SQL data types. Try it with character (string) values. A table called suppliers contains the following data:

SELECT *
FROM suppliers
WHERE supplier_name IN ('EY', 'Oracle', 'Microsoft');

Output

Select three records. These are the expected results:

LEAST

Unlike GREATEST, SQL LEAST returns the “least” or smallest value in a set of values.

Syntax

LEAST ( expr1, [expr_n] )

Example

SELECT
LEAST('10', '15', '65', 8') AS LEAST_CHECK;

Output

NULLIF

NULLIF takes two arguments, expr1 and expr2. If expr1 and expr2 are equal, NULLIF returns NULL; otherwise, it returns expr1. The first parameter can't be NULL, unlike the other null handling procedure.

Syntax

SELECT NULLIF (expression1, expression2);

Example

This example yields NULL since the first and second arguments match:

SELECT 
    NULLIF(10, 10) result;

Output

Since the two arguments are not equal, the following example yields the first:

SELECT 
    NULLIF(20, 10) result;

Output

Unlock the power of data with our SQL Certification Course! Learn essential SQL skills and gain a valuable certification to advance your career. Enroll now and take the first step towards becoming a data expert!

Handling NULL Values with Conditional Statements

Rows without column values have null values. Null values are unknown, not zero or blanks. Null values can be used in WHERE and HAVING clauses. Null values cannot be less, equal, or greater than the condition value.

As said, SQL Server has NULL value handling functions.

• The ISNULL() function uses two parameters to replace NULL values with a given value.
• COALESCE(): In an infinite list, it returns the first non-null expression.

Practical Applications of Conditional Statements in SQL

Here are a few essential applications of conditional statements.

1. Inventory Control Management Database

• Any product-based business must track its inventory. Data analysts utilize SQL to build an inventory management system to help business owners make essential inventory planning decisions.
• For instance, take a Walmart shop sales dataset (Year, Month, Product Category, Sales) for a given period. Include GDP, CPI, cotton production, unemployment, etc. You can get weather and holiday data. Use the dataset to develop an SQL online retail application database for data analysis in this SQL project.

2. European Soccer Game Analysis

• This SQL project is perfect for you if you love soccer and enjoy looking at trends in different teams. It will teach you how to aggregate the dataset using the query language.
• Use the dataset to create a SQL database. Use SQL's Conditional SELECT command to view table values. Combine tables using specified JOIN commands based on values. Analyze team and player data using ORDER BY, GROUP BY, and LIMIT. Also, rank teams using aggregate functions.

3. Blood Bank Management System

• Blood banks store and distribute blood. The Blood Bank Management System helps all blood banks track blood quality and availability when patients request it. If you want healthcare SQL practice projects, try this one.
• A blood donation database should include data about donors (name, age, blood group), recipients, hospitals that need blood from the blood bank, blood quality (infected or not), etc.

4. Bookstore Analysis/ Library Management System 

• Think back to school. You must have seen many books. Create a virtual bookstore with your favorite books.
• Consider running a bookstore chain. To create a sample dataset of your favorite books, include Book_ID, Name, Publishing Name, ISBN, Edition, No. of pages, Sales, City, and Price in a table.

Start Your Journey to Data Mastery

SQL Certification CourseExplore Program

Conclusion

Tech companies require SQL Developers who understand advanced SQL concepts. Mastering these advanced approaches can help you write more efficient and well-structured SQL searches for large datasets and complex conditions. Enroll in the SQL Certification Course from Simplilearn to learn all the information you need to start working with SQL databases and use them in your applications successfully. Learn how to structure your database correctly, author efficient SQL statements and clauses, and manage your SQL database for scalable growth.

FAQs

1. How does the IF statement differ from the CASE statement in SQL?

The IF statement in SQL is often used to conditionally execute chunks of procedural code, such as stored procedures, functions, and triggers. CASE queries return values based on SELECT, WHERE, and ORDER BY clauses.

2. What are IFNULL, NULLIF, and COALESCE used for in SQL?

• If not NULL, IFNULL returns the first argument. Returns the second argument if NULL.
• NULLIF returns null if both arguments are identical. SQL uses this function to compare two values.
• COALESCE returns the first non-null parameter. Returns null if all parameters are null.

3. How can you use conditional statements with functions to create complex queries?

In addition to AND, OR, and NOT, you can create complicated SQL conditions by mixing SQL conditional statements. Subqueries, EXISTS, ANY, ALL operators, or nesting CASE expressions can do this.

4. Are conditional statements in SQL case-sensitive?

SQL is usually case-insensitive. However you write your SQL keywords, the database engine will read them appropriately, yielding the same result regardless of the keyword case.

5. Can I use conditional statements with aggregate functions in SQL?

We can add a set of conditions to the current Aggregate functions. Some frequent aggregate functions are: 

• Count the number of rows that fulfill the conditions using COUNT(). 
• The SUM() function returns the total sum of a numeric column.
• Max() and MIN() yield the largest and smallest values that fulfill query conditions.
• AVG() calculates the average of defined values.
• GROUP BY summarizes rows with identical values. Used with aggregate functions like COUNT, SUM, etc.