The SQL Conditional Expressions
You can use SQL conditional expressions, conditional statements, or conditional logic to control SQL queries and figure out what to do based on the situation.
SQL Conditional Expressions and Statements:
- Control SQL queries using conditional expressions, statements, or logic.
- Move SQL code to a different line based on the statement’s truth or falseness.
- Useful in various situations.
- Included are WHERE, AND, OR, NOT, SELECT, and CASE statements.
What are the key points of SQL conditional expressions?
Conditional Logic:
SQL conditional expressions evaluate conditions and return results or execute specific actions based on whether the condition is true or false.
Usage in Queries:
They are used within SELECT, WHERE, HAVING, and CASE statements to determine which records to retrieve or manipulate.
Types of conditional expressions:
Common types include CASE expressions, functions like COALESCE and NULLIF, IF statements in some database systems, and the DECODE function in Oracle.
Handling Null values:
Conditional expressions are often used to handle null values by substituting them with default values or performing actions based on their presence.
Syntax Variations:
Different database systems might have variations in syntax or specific functions for conditional expressions, but the core purpose remains the same to same to introduce logic into SQL queries.
What is the use of conditional expressions?
Categorizing Data:
Using CASE expressions to categorize age groups, status, or any other criteria based on certain conditions.
Handling Null values:
Using functions like COALESCE to replace null values with defaults or NULLIF to handle comparisons involving nulls.
Conditional Calculations:
Executing calculations or assigning values based on specific conditions within a query.
Branching Logic:
Using IF statements or the DECODE function (in Oracle) to perform if-then-else logic within SQL.
WHERE Clause with Conditional Operators Examples:
EQUALS (=):
SELECT * FROM employees WHERE department_id = 11;
NOT EQUALS (!= or <>):
SELECT * FROM products WHERE category <> ‘Mens’;
Greater Than (>), Less Than (<):
SELECT * FROM orders WHERE sales_order > 5500;
Greater Than or Equal To (>=), Less Than or Equal To (<=):
SELECT * FROM customers WHERE Birth_year >= 2001;
AND, OR, NOT:
SELECT * FROM sales WHERE (price > 5000 AND stock_quantity > 100) OR category = ‘100’;
SQL CASE Statement:
The CASE statement allows you to perform conditional logic within a SQL query with use of SELECT, WHERE, ORDER BY clauses, and others.
SELECT product_name, CASE category
WHEN ‘Electronics’ THEN ‘High-Tech’
WHEN ‘Clothing’ THEN ‘Fashion’
ELSE ‘Other’
END AS category_type
FROM products;
SQL CASE statement:
SELECT sale_id, CASE
WHEN sale_amount > 10000 THEN ‘Large’
WHEN sale_amount > 500 THEN ‘Medium’
ELSE ‘Small’
END AS sale_size
FROM sales;
SQL IFNULL and COALESCE Functions:
These functions are used to provide a default value when a column contains NULL.
SQL IFNULL Function:
SELECT product_name, IFNULL(discounted_price, regular_price) AS final_price FROM products;
SQL COALESCE Function:
SELECT product_name, COALESCE(discounted_price, regular_price, 0) AS final_price FROM products;
The use of these SQL conditional expressions is crucial for filtering and modifying data according to predetermined conditions.
Some other types of SQL conditional expressions include:
The CASE Expression:
The DECODE Function:
COALESCE :
GREATEST:
IFNULL:
IN:
LEAST:
NULLIF:
CASE Expression:
The CASE expression lets you use conditional logic in SQL. It checks a list of factors and gives you an answer based on the first condition.
Syntax:
CASE
WHEN condition1 THEN case1
WHEN condition2 THEN case2
WHEN condition3 THEN case3
WHEN conditionN THEN caseN
ELSE default_result
END;
Example:
SELECT
CASE
WHEN age < 18 THEN ‘Adult’
WHEN age BETWEEN 18 AND 25 THEN ‘Younger’
ELSE ‘Senior’
END AS age_group
FROM users;
COALESCE Function:
In a list, COALESCE returns the first expression that is not null. It can be used to replace null numbers with other values.
Syntax:
COALESCE(expr1, expr2, …)
Example:
SELECT COALESCE(name, ‘Anonymous’) AS username FROM users;
NULLIF Function:
If two expressions are equal, NULLIF gives null. If they are not, it returns the first expression.
Syntax:
NULLIF(expr1, expr2)
Example:
SELECT NULLIF(salary, 0) AS valid_salary FROM employees;
IF Statement (MySQL):
In MySQL, the IF statement allows conditional execution of statements based on a condition.
Syntax:
IF(condition, expr_true, expr_false)
Example:
SELECT IF(age >= 18, ‘Adult’, ‘Minor’) AS age_group FROM people;
DECODE Function (Oracle):
In Oracle, the DECODE function is used to perform if-then-else logic.
Syntax:
DECODE(expr, search1, result1, search2, result2, …, default_result)
Example:
SELECT DECODE(status, ‘A’, ‘Active’, ‘I’, ‘Inactive’, ‘Unknown’) AS user_status FROM users;
Importance of SQL Conditional Expressions:
- Allows dynamic data retrieval or modification.
- Handles null values and logical splitting.
- Different database systems have unique conditional statement writing methods.