? MySQL Advanced Functions

? Quick Overview

MySQL Advanced Functions help perform conditional logic, handle NULL values, convert data types, and generate complex computed results directly within SQL queries. They are widely used in reporting, analytics, and data transformation.

? Key Concepts

Conditional evaluation using IF and CASE
Handling NULL values safely
Data type conversions
Advanced string and aggregation handling

? Syntax & Theory

IF() – Executes conditional logic
CASE – Multi-condition decision making
COALESCE() – Returns first non-NULL value
NULLIF() – Converts matching values to NULL
CAST() / CONVERT() – Data type conversion
GROUP_CONCAT() – Aggregates strings

? Code Examples

? View Code Example

-- Using IF() for conditional output
SELECT name, IF(marks >= 40, 'Pass', 'Fail') AS result
FROM students;

? View Code Example

-- CASE statement with multiple conditions
SELECT name,
CASE
WHEN marks >= 75 THEN 'Distinction'
WHEN marks >= 40 THEN 'Pass'
ELSE 'Fail'
END AS grade
FROM students;

? View Code Example

-- Handling NULL values using COALESCE
SELECT name, COALESCE(phone, 'Not Provided') AS contact
FROM users;

? View Code Example

-- NULLIF converts matching values to NULL
SELECT NULLIF(100, 100) AS result;

? View Code Example

-- CAST used for data type conversion
SELECT CAST('2025-01-01' AS DATE) AS converted_date;

? View Code Example

-- GROUP_CONCAT merges multiple rows into a single string
SELECT department, GROUP_CONCAT(name)
FROM employees
GROUP BY department;

? Live Output / Explanation

IF and CASE simplify decision-making inside queries
COALESCE prevents NULL-related display issues
CAST ensures correct data type handling
GROUP_CONCAT is useful for summary reports

✅ Tips & Best Practices

Prefer CASE over nested IF for readability
Always handle NULL values explicitly
Use CAST when comparing different data types
Limit GROUP_CONCAT size using group_concat_max_len

? Try It Yourself

Create a grading system using CASE
Replace NULL values in a table using COALESCE
Convert string dates to DATE format
Generate department-wise employee lists