One row in, one value out. Single-row functions transform each row independently  rounding money, taking absolute values, picking the best test score, or replacing missing data  without collapsing rows like aggregates.
studentsCREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(30) NULL,
phone VARCHAR(20) NULL,
fee_paid DECIMAL(10,2) NULL, -- for ROUND()
score_change DECIMAL(10,2) NULL, -- for ABS()
test1 INT NULL, -- for GREATEST/LEAST
test2 INT NULL, test3 INT NULL
);
INSERT INTO students (id, name, city, phone, fee_paid, score_change, test1, test2, test3) VALUES
(1, 'Aisha', 'Delhi', NULL, 2804.45, -12.25, 55, 60, 58),
(2, 'Rohan', 'Mumbai', '', 3303.06, -80.00, 40, NULL, 52),
(3, 'Meenal', NULL, '8888000004', 7999.00, NULL, NULL, NULL, 35),
(4, 'Arjun', 'Pune', '9777000005', 3717.94, 10.00, 72, 70, 75),
(5, 'Neha', 'Jaipur', NULL, 3499.00, -30.75, 10, 15, 12),
(6, 'Vikas', 'Hyderabad', '9666000007', 5099.00, 120.75, 88, 91, 95),
(7, 'Sana', 'Ahmedabad', NULL, 7085.13, -15.00, 65, 64, NULL),
(8, 'Imran', 'Kolkata', '', 3303.06, -1.00, 50, 49, 51),
(9, 'Pallavi', '', '9555000010', 3303.06, 5.25, 92, 90, 91),
(10, 'Deepak', 'Chennai', NULL, NULL, -22.00, 33, 40, 38),
(11, 'Ananya', 'Bengaluru', '9444000012', 3504.16, 18.50, 78, NULL, NULL),
(12, 'Tanya', 'Kolkata', '', 3303.06, -10.00, 45, 42, 48);CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(30),
phone VARCHAR(20),
fee_paid NUMERIC(10,2),
score_change NUMERIC(10,2), test1 INT, test2 INT, test3 INT
);
-- same INSERT as SQL| id | name | city | phone | fee_paid | score_change | test1 | test2 | test3 |
|---|---|---|---|---|---|---|---|---|
| 1 | Aisha | Delhi | NULL | 2804.45 | -12.25 | 55 | 60 | 58 |
| 2 | Rohan | Mumbai | '' | 3303.06 | -80.00 | 40 | NULL | 52 |
| 3 | Meenal | NULL | 8888000004 | 7999.00 | NULL | NULL | NULL | 35 |
| 4 | Arjun | Pune | 9777000005 | 3717.94 | 10.00 | 72 | 70 | 75 |
| 5 | Neha | Jaipur | NULL | 3499.00 | -30.75 | 10 | 15 | 12 |
| 6 | Vikas | Hyderabad | 9666000007 | 5099.00 | 120.75 | 88 | 91 | 95 |
| 7 | Sana | Ahmedabad | NULL | 7085.13 | -15.00 | 65 | 64 | NULL |
| 8 | Imran | Kolkata | '' | 3303.06 | -1.00 | 50 | 49 | 51 |
| 9 | Pallavi | '' | 9555000010 | 3303.06 | 5.25 | 92 | 90 | 91 |
| 10 | Deepak | Chennai | NULL | NULL | -22.00 | 33 | 40 | 38 |
| 11 | Ananya | Bengaluru | 9444000012 | 3504.16 | 18.50 | 78 | NULL | NULL |
| 12 | Tanya | Kolkata | '' | 3303.06 | -10.00 | 45 | 42 | 48 |
ROUND(number, decimals) rounds to the given decimal places. No decimals → nearest integer.
SELECT
id, name, fee_paid,
ROUND(fee_paid, 0) AS fee_rupees,
ROUND(fee_paid, 2) AS fee_2dp
FROM students
ORDER BY id;SELECT
id, name, fee_paid,
ROUND(fee_paid, 0) AS fee_rupees,
ROUND(fee_paid, 2) AS fee_2dp
FROM students
ORDER BY id;| id | name | fee_paid | fee_rupees | fee_2dp |
|---|---|---|---|---|
| 1 | Aisha | 2804.45 | 2804 | 2804.45 |
| 2 | Rohan | 3303.06 | 3303 | 3303.06 |
| 3 | Meenal | 7999.00 | 7999 | 7999.00 |
| 4 | Arjun | 3717.94 | 3718 | 3717.94 |
| 5 | Neha | 3499.00 | 3499 | 3499.00 |
| 6 | Vikas | 5099.00 | 5099 | 5099.00 |
| 7 | Sana | 7085.13 | 7085 | 7085.13 |
| 8 | Imran | 3303.06 | 3303 | 3303.06 |
| 9 | Pallavi | 3303.06 | 3303 | 3303.06 |
| 10 | Deepak | NULL | NULL | NULL |
| 11 | Ananya | 3504.16 | 3504 | 3504.16 |
| 12 | Tanya | 3303.06 | 3303 | 3303.06 |
Standard rules: ≥ 0.5 rounds up, < 0.5 rounds down. NULL stays NULL.
ABS(number) returns distance from zero  negative → positive, positive stays, NULL stays NULL.
SELECT id, name, score_change, ABS(score_change) AS abs_score_change
FROM students
ORDER BY id;SELECT id, name, score_change, ABS(score_change) AS abs_score_change
FROM students
ORDER BY id;| id | name | score_change | abs_score_change |
|---|---|---|---|
| 1 | Aisha | -12.25 | 12.25 |
| 2 | Rohan | -80.00 | 80.00 |
| 3 | Meenal | NULL | NULL |
| 4 | Arjun | 10.00 | 10.00 |
| 5 | Neha | -30.75 | 30.75 |
| 6 | Vikas | 120.75 | 120.75 |
| 7 | Sana | -15.00 | 15.00 |
| 8 | Imran | -1.00 | 1.00 |
| 9 | Pallavi | 5.25 | 5.25 |
| 10 | Deepak | -22.00 | 22.00 |
| 11 | Ananya | 18.50 | 18.50 |
| 12 | Tanya | -10.00 | 10.00 |
GREATEST(a,b,…) returns the largest value in the list; LEAST(a,b,…) the smallest  computed per row across columns (not across rows like MAX). If any argument is NULL, result is NULL.
SELECT id, name, test1, test2, test3,
GREATEST(test1, test2, test3) AS GREATEST_score,
LEAST(test1, test2, test3) AS LEAST_score
FROM students
ORDER BY id;SELECT id, name, test1, test2, test3,
GREATEST(test1, test2, test3) AS GREATEST_score,
LEAST(test1, test2, test3) AS LEAST_score
FROM students
ORDER BY id;| id | name | test1 | test2 | test3 | GREATEST_score | LEAST_score |
|---|---|---|---|---|---|---|
| 1 | Aisha | 55 | 60 | 58 | 60 | 55 |
| 2 | Rohan | 40 | NULL | 52 | NULL | NULL |
| 3 | Meenal | NULL | NULL | 35 | NULL | NULL |
| 4 | Arjun | 72 | 70 | 75 | 75 | 70 |
| 5 | Neha | 10 | 15 | 12 | 15 | 10 |
| 6 | Vikas | 88 | 91 | 95 | 95 | 88 |
| 7 | Sana | 65 | 64 | NULL | NULL | NULL |
| 8 | Imran | 50 | 49 | 51 | 51 | 49 |
| 9 | Pallavi | 92 | 90 | 91 | 92 | 90 |
| 10 | Deepak | 33 | 40 | 38 | 40 | 33 |
| 11 | Ananya | 78 | NULL | NULL | NULL | NULL |
| 12 | Tanya | 45 | 42 | 48 | 48 | 42 |
Note: If any test column is NULL, result becomes NULL  use COALESCE(test2,0) if you need to treat missing as 0.
Why is GREATEST for Rohan NULL?
IFNULL(expr, replacement) (MySQL)  if expr is NULL return replacement, else expr. Takes exactly 2 args. COALESCE(expr, …)  returns first non-NULL in the list (standard SQL, works in both MySQL and PostgreSQL).
SELECT id, name,
IFNULL(phone, 'Not Provided') AS phone_status
FROM students;SELECT id, name,
COALESCE(phone, 'Not Provided') AS phone_status
FROM students;| id | name | phone_status |
|---|---|---|
| 1 | Aisha | Not Provided |
| 2 | Rohan | '' |
| 3 | Meenal | 8888000004 |
| 4 | Arjun | 9777000005 |
| 5 | Neha | Not Provided |
| 6 | Vikas | 9666000007 |
| 7 | Sana | Not Provided |
| 8 | Imran | '' |
| 9 | Pallavi | 9555000010 |
| 10 | Deepak | Not Provided |
| 11 | Ananya | 9444000012 |
| 12 | Tanya | '' |
Note: IFNULL only replaces NULL, not empty string ''. Rohan/Imran/Tanya have '' → stays ''.
NULL = missing/unknown; '' = present but empty  different.
Convert empty to NULL first with NULLIF, then replace:
SELECT id, name, phone,
IFNULL(NULLIF(phone, ''), 'Not Provided') AS final_phone
FROM students;SELECT id, name, phone,
COALESCE(NULLIF(phone, ''), 'Not Provided') AS final_phone
FROM students;| id | name | phone | final_phone |
|---|---|---|---|
| 1 | Aisha | NULL | Not Provided |
| 2 | Rohan | '' | Not Provided |
| 3 | Meenal | 8888000004 | 8888000004 |
| 4 | Arjun | 9777000005 | 9777000005 |
| 5 | Neha | NULL | Not Provided |
| 6 | Vikas | 9666000007 | 9666000007 |
| 7 | Sana | NULL | Not Provided |
| 8 | Imran | '' | Not Provided |
| 9 | Pallavi | 9555000010 | 9555000010 |
| 10 | Deepak | NULL | Not Provided |
| 11 | Ananya | 9444000012 | 9444000012 |
| 12 | Tanya | '' | Not Provided |
NULLIF(phone, '') → '' becomes NULLIFNULL(…, 'Not Provided') / COALESCE → NULL becomes replacementCOALESCE(v1, v2, …) returns the first non-NULL value in the list. Unlike IFNULL (2 args, MySQL-only), COALESCE takes many args and is standard SQL.
CREATE TABLE coalesce_demo (
id INT PRIMARY KEY,
primary_email VARCHAR(80),
work_email VARCHAR(80),
personal_email VARCHAR(80),
hours_logged DECIMAL(10,2),
default_hours DECIMAL(10,2)
);
INSERT INTO coalesce_demo VALUES
(1, 'aisha@company.com', NULL, 'aisha@gmail.com', 5.0, 0.0),
(2, NULL, 'rohan@company.com',NULL, NULL, 0.0),
(3, NULL, NULL, 'meera@yahoo.com', 2.5, 0.0),
(4, NULL, NULL, NULL, NULL, 0.0),
(5, '', NULL, 'raj@gmail.com', 5.0, 0.0);CREATE TABLE coalesce_demo (
id INT PRIMARY KEY,
primary_email VARCHAR(80),
work_email VARCHAR(80),
personal_email VARCHAR(80),
hours_logged DECIMAL(10,2),
default_hours DECIMAL(10,2)
);
-- same INSERT as SQL| id | primary_email | work_email | personal_email | hours_logged | default_hours |
|---|---|---|---|---|---|
| 1 | aisha@company.com | NULL | aisha@gmail.com | 5.0 | 0.0 |
| 2 | NULL | rohan@company.com | NULL | NULL | 0.0 |
| 3 | NULL | NULL | meera@yahoo.com | 2.5 | 0.0 |
| 4 | NULL | NULL | NULL | NULL | 0.0 |
| 5 | '' | NULL | raj@gmail.com | 5.0 | 0.0 |
SELECT id,
COALESCE(primary_email, work_email, personal_email) AS contact_email
FROM coalesce_demo;SELECT id,
COALESCE(primary_email, work_email, personal_email) AS contact_email
FROM coalesce_demo;| id | CONTACT_EMAIL |
|---|---|
| 1 | aisha@company.com |
| 2 | rohan@company.com |
| 3 | meera@yahoo.com |
| 4 | NULL |
| 5 | '' |
COALESCE returns first non-NULL  id 4 (all NULL → NULL), id 5 ('' is not NULL → returns '')SELECT id,
COALESCE(primary_email, work_email, personal_email, 'hello@tuf') AS contact_email
FROM coalesce_demo;SELECT id,
COALESCE(primary_email, work_email, personal_email, 'hello@tuf') AS contact_email
FROM coalesce_demo;| id | CONTACT_EMAIL |
|---|---|
| 1 | aisha@company.com |
| 2 | rohan@company.com |
| 3 | meera@yahoo.com |
| 4 | hello@tuf |
| 5 | '' |
Fallback 'hello@tuf' only for id 4 (all NULL). Empty string still returned as-is.
SELECT id,
COALESCE(NULLIF(TRIM(primary_email),''), work_email, personal_email) AS contact_email
FROM coalesce_demo;SELECT id,
COALESCE(NULLIF(TRIM(primary_email),''), work_email, personal_email) AS contact_email
FROM coalesce_demo;| id | CONTACT_EMAIL |
|---|---|
| 1 | aisha@company.com |
| 2 | rohan@company.com |
| 3 | meera@yahoo.com |
| 4 | NULL |
| 5 | raj@gmail.com |
TRIM(primary_email) removes spacesNULLIF(…, '') converts empty → NULL so COALESCE skips it → id 5 now correctly picks personal_email| Concept | Definition | Used in | MySQL Syntax | PostgreSQL Syntax |
|---|---|---|---|---|
| IS NULL | Checks if value is actually NULL | WHERE (filter) | col IS NULL | col IS NULL |
| IS NOT NULL | Checks if value is present | WHERE (filter) | col IS NOT NULL | col IS NOT NULL |
| IFNULL() | Replaces NULL with value (2 args) | SELECT (clean output) | IFNULL(col, value) | ✖ Not available |
| COALESCE() | Returns first non-NULL (many args) | SELECT (clean output) | COALESCE(col, value) | COALESCE(col, value) |
| Common mistake | = NULL never works  NULL is not a value |  | ✖ col = NULL | ✖ col = NULL |
IFNULL(phone,'Not Provided') vs COALESCE with NULLIF  which handles empty string?
ROUND(fee_paid,0) → nearest rupee; ROUND(fee_paid,2) → 2 decimals; NULL stays NULL
ABS(score_change) → distance from 0; -80 → 80, NULL → NULL
GREATEST/LEAST per row across columns: GREATEST(55,60,58)=60; any NULL → NULL (≠MAX aggregate)
IFNULL(phone,'Not Provided')  MySQL, 2 args, only NULL; '' stays ''
COALESCE(phone, 'Not Provided')  standard, many args, first non-NULL wins
Handle '' too: IFNULL(NULLIF(phone,''),'Not Provided') / COALESCE(NULLIF(TRIM(primary_email),''), work_email…)
COALESCE(primary, work, personal) → first non-NULL; all NULL → NULL (id 4)
COALESCE(…, 'hello@tuf') → default fallback; '' still not NULL (id 5 → '')
COALESCE(NULLIF(TRIM(primary_email),''), …) → skips '' → id 5 → raj@gmail.com
IS NULL / IS NOT NULL filters rows; IFNULL/COALESCE cleans output; never use = NULL
Primary sources: MySQL Math Functions · Postgres Math · Postgres Conditional. Ask me anything that's unclear.
Questions? Ask your agent  you can follow up on any concept, quiz answer, or how this applies to a specific query. Review the SQL glossary or syntax quick reference for a compressed overview.