CASE Statement
The CASE statement is SQL’s way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements. It must end with the END statement. The ELSE statement is optional. The CASE statement is SQL’s way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements. It must end with the END statement. The ELSE statement is optional.
The syntax for the CASE statement is as follows:
SELECT column_name,
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE result
END
FROM table_name;
The CASE statement is SQL’s way of handling if/then logic. The CASE statement is followed by at least one pair of WHEN and THEN statements. It must end with the END statement. The ELSE statement is optional.
Example:
| product_id | product_name | price |
|---|---|---|
| 1 | Product 1 | 100 |
| 2 | Product 2 | 200 |
| 3 | Product 3 | 300 |
SELECT product_name,
CASE
WHEN product_id = 1 THEN 'Product 1'
WHEN product_id = 2 THEN 'Product 2'
ELSE 'Product 3'
END
FROM products;
The above query will return the product name based on the product_id. If the product_id is 1, it will return 'Product 1', if the product_id is 2, it will return 'Product 2', and if the product_id is not 1 or 2, it will return 'Product 3'.
The CASE statement can be used in the SELECT, WHERE, ORDER BY, and HAVING clauses.
NULL Values Handling
NULL is a special value that represents missing or unknown data. It is not the same as an empty string or a zero value. NULL values are used to represent missing or unknown data in a database. NULL values are used to represent missing or unknown data in a database.
To check for NULL values, you can use the IS NULL operator. The IS NULL operator returns true if the value is NULL and false otherwise.
SELECT column_name
FROM table_name
WHERE column_name IS NULL;
To check for non-NULL values, you can use the IS NOT NULL operator. The IS NOT NULL operator returns true if the value is not NULL and false otherwise.
SELECT column_name
FROM table_name
WHERE column_name IS NOT NULL;
You can also use the COALESCE function to handle NULL values. The COALESCE function returns the first non-NULL value in a list of values.
SELECT COALESCE(column_name, 'default_value')
FROM table_name;
The above query will return the column_name if it is not NULL, otherwise it will return 'default_value'.
You can also use the IFNULL function to handle NULL values. The IFNULL function returns the second value if the first value is NULL.
SELECT IFNULL(column_name, 'default_value')
FROM table_name;
Example:
| product_id | product_name | price |
|---|---|---|
| 1 | Product 1 | 100 |
| 2 | Product 2 | NULL |
| 3 | Product 3 | 300 |
SELECT product_name,
IFNULL(price, 0)
FROM products;
| product_name | price |
|---|---|
| Product 1 | 100 |
| Product 2 | 0 |
| Product 3 | 300 |
The above query will return the product name and the price. If the price is NULL, it will return 0.
GROUP BY Clause
The GROUP BY clause is used to group rows that have the same values in one or more columns. The GROUP BY clause is used in conjunction with aggregate functions to group the result set by one or more columns. The GROUP BY clause is used in conjunction with aggregate functions to group the result set by one or more columns.
The syntax for the GROUP BY clause is as follows:
SELECT column_name1, aggregate_function(column_name2)
FROM table_name
GROUP BY column_name1;
The GROUP BY clause is used to group rows that have the same values in one or more columns. The GROUP BY clause is used in conjunction with aggregate functions to group the result set by one or more columns.
Example:
| product_id | product_name | price |
|---|---|---|
| 1 | Product 1 | 100 |
| 2 | Product 2 | 200 |
| 3 | Product 3 | 300 |
| 1 | Product 1 | 150 |
| 2 | Product 2 | 250 |
| 3 | Product 3 | 350 |
SELECT product_name, SUM(price)
FROM products
GROUP BY product_name;
| product_name | SUM(price) |
|---|---|
| Product 1 | 250 |
| Product 2 | 450 |
| Product 3 | 650 |
The above query will return the product name and the sum of the price for each product.