The SQL conditional expressions
SQL conditional expressions or conditional statements or conditional logic are used to control SQL queries and determine what should be done in light of the given circumstances.
SQL conditional expressions enable code branching according to the value of a condition, which can be either true or false. SQL conditional expressions are useful in various SQL statements, like
SELECT
WHERE
AND
OR
NOT
CASE statements
SQL conditional expressions with examples:
WHERE Clause with Conditional Operators:
Use conditional operators in the WHERE clause to filter.
EQUALS (=): Select rows where a column equals a specific value.
SELECT * FROM employees WHERE department_id = 5;
NOT EQUALS (!= or <>): Select rows where a column is not equal to a specific value.
SELECT * FROM sales WHERE category <> ‘Electronics’;
Greater Than (>), Less Than (<): Select rows where a column is greater than or less than a specific value. SELECT * FROM orders WHERE order_amount > 5000;
Greater Than or Equal To (>=), Less Than or Equal To (<=): Select rows where a column is greater than or equal to, or less than or equal to, a specific value. SELECT * FROM customers WHERE registration_year >= 2024;
AND, OR, NOT: Combine conditions using logical operators.
SELECT * FROM sales WHERE (price > 500 AND stock_quantity > 0) OR category = ‘Clear’;
SQL CASE Statement:
The CASE statement is a SQL operator that enables the execution of conditional logic. It is applicable, among other places, in the SELECT, WHERE, and ORDER BY clauses.
SELECT product_name, CASE category
WHEN ‘Electronics’ THEN ‘High-Values’
WHEN ‘Clothing’ THEN ‘Fashion’
ELSE ‘Other’
END AS category_type
FROM products;
SQL Searched CASE Statement:
SELECT order_id, CASE
WHEN order_amount > 5000 THEN ‘Large’
WHEN order_amount > 500 THEN ‘Medium’
ELSE ‘Small’
END AS order_size
FROM orders;
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.
Read more about SQL…