• The SQL CASE function is a conditional expression that allows you to evaluate different conditions and return a corresponding value based on the result.
• Similar to if-then-else statement, CASE statement goes through conditions and returns a value when the first condition is met.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result3
END

The COALESCE function in SQL is used to return the first non-NULL value from a list of expressions. It is used to manage NULL values and provide a default value if necessary.

Syntax:

COALESCE(expression1, expression2, ..., expression_n)

Example:

SELECT employee_id,
       COALESCE(salary, 0) AS salary
FROM employees;

• ****To replace NULL values in salary with 0.

Convert one data type into another. It’s essential for data manipulation and ensuring data integrity.

Syntax:

CAST(expression AS data_type)

Example:

SELECT CAST('12345' AS INT) AS converted_number;

• Purpose: Converts the string '12345' to the integer 12345.

The NULLIF function in SQL is used to return NULL if two expressions are equal, or the first expression if they are not. It’s often used to avoid division by zero errors or other unexpected results when dealing with NULL values.

Syntax:

NULLIF(expression1, expression2)

Example:

SELECT NULLIF(discount, ?)

• If discount equals ?, NULLIF returns NULL.

The (NOT) EXISTS clause in SQL is used to check for the existence (or non-existence) of rows in a subquery.

Syntax:

SELECT column_name1, column_name2, ...
FROM table_name
WHERE (NOT) EXISTS (subquery);

Example:

SELECT customer_id, first_name, last_name
FROM customers
WHERE EXISTS (
  SELECT 1
  FROM orders
  WHERE orders.customer_id = customers.customer_id
);

• Consider two tables: customers and orders. Find customers who have placed at least one order.

The UNION operator is used to combine the result-set of two or more SELECT statements.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2; 

• Note: The order of rows in the final result set is not guaranteed.

A CROSS JOIN is a type of join operation in SQL that produces a Cartesian product of two tables. It combines each row from the first table with every row from the second table, resulting in a table that contains all possible combinations of rows from the two tables.

Syntax:

SELECT column_name1, column_name2, ...
FROM table1
CROSS JOIN table2;

Example

• Consider two tables: colors and sizes.

  • colors has red, blue and green
  • sizes has small, medium and large

• To get all possible combinations of colors and sizes:

  SELECT colors.color, sizes.size
  FROM colors
  CROSS JOIN sizes;
  

• Output

  color | size  
  _____________
  Red   | Small 
  Red   | Medium
  Red   | Large 
  Blue  | Small 
  Blue  | Medium
  Blue  | Large 
  Green | Small 
  Green | Medium
  Green | Large 
  

• Distinct Rows: UNION returns only unique rows, eliminating duplicates. To allow duplicate values, use UNION ALL
• Column Compatibility: The SELECT statements must have the same number of columns, and the corresponding columns must have compatible data types.