DISTINCT and CASE expressions
40 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Two tools shape what a result says. DISTINCT removes repeated rows, so each value appears once. CASE turns raw values into words people understand, such as marks into grades or stock into "In stock".
DISTINCT on one column
DISTINCT removes duplicate rows from a result, so each row appears only once. It changes the result only, never the table, and it treats all NULLs as equal.
A filter menu should list each city once, not once per customer. SELECT DISTINCT city removes the repeats; the second query lists the departments that employees actually belong to.
Example 1 · Each city and each department once
SELECT DISTINCT cityFROM customersORDER BY city;
SELECT DISTINCT dept_idFROM employeesORDER BY dept_id;+-----------+ | city | +-----------+ | Bengaluru | | Chennai | | Delhi | | Hyderabad | | Kochi | | Mumbai | | Pune | +-----------+ +---------+ | dept_id | +---------+ | NULL | | 1 | | 2 | | 3 | | 4 | +---------+
What this code does
- Ten customers live in seven cities, so the first result has seven rows.
ORDER BY citysorts the distinct list. - Thirteen employees give five distinct values: departments 1 to 4 and one NULL for Tanvi Kulkarni. DISTINCT treats NULLs as equal, and Research (5) has no employees, so it does not appear.
Apps use this for the cities in a filter, the branches of a college, the statuses that really occur. A column with several NULLs shows just one NULL row.
DISTINCT on several columns
DISTINCT on several columns removes only the rows where the whole combination repeats. CSE, 2 and CSE, 3 stay as two rows, because year differs.
SELECT DISTINCT branch, year FROM students keeps one row for each different pair. Writing DISTINCT(branch), year looks like it applies to branch only; the second query shows it does not.
Example 2 · DISTINCT works on the whole row
SELECT DISTINCT branch, yearFROM studentsORDER BY branch, year;
-- The brackets change nothing: DISTINCT still covers both columns.SELECT DISTINCT(branch), yearFROM studentsORDER BY branch, year;+--------+------+ | branch | year | +--------+------+ | CSE | 2 | | CSE | 3 | | ECE | 2 | | ECE | 3 | | IT | 1 | | IT | 4 | | MECH | 1 | +--------+------+ +--------+------+ | branch | year | +--------+------+ | CSE | 2 | | CSE | 3 | | ECE | 2 | | ECE | 3 | | IT | 1 | | IT | 4 | | MECH | 1 | +--------+------+
What this code does
- Eight students give seven distinct (branch, year) pairs. Only
CSE, 2repeats, because Aditya and Bhavana share it. - The second query returns exactly the same seven rows.
DISTINCT(branch)only looks like a function call; DISTINCT always applies to every column in the SELECT list.
DISTINCT belongs to the whole SELECT list; brackets around one column are just brackets.
DISTINCTgoes right afterSELECT, once, before all columns.- With DISTINCT, ORDER BY may only use columns that are in the SELECT list. Sorting by another column gives
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list ... this is incompatible with DISTINCT. - If DISTINCT seems needed to hide repeated rows, check the query first; often a condition is missing.
CASE WHEN for labels and buckets
A CASE expression picks a value by testing conditions in order. MySQL uses the first WHEN test that is true, or the ELSE value, or NULL when there is no ELSE.
Marks become grades and status codes become words people read. CASE WHEN ... THEN ... END is the SQL if-else chain: the label is built in the result and the table keeps the raw value.
Example 3 · Marks to grades with CASE
SELECT student_id, course_id, marks, CASE WHEN marks IS NULL THEN 'Absent' WHEN marks >= 90 THEN 'O' WHEN marks >= 75 THEN 'A' WHEN marks >= 60 THEN 'B' WHEN marks >= 40 THEN 'C' ELSE 'F' END AS gradeFROM enrollmentsWHERE course_id IN (4, 5)ORDER BY course_id, student_id;+------------+-----------+-------+--------+ | student_id | course_id | marks | grade | +------------+-----------+-------+--------+ | 1 | 4 | 75 | A | | 3 | 4 | 58 | C | | 5 | 4 | 45 | C | | 6 | 4 | 81 | A | | 4 | 5 | 80 | A | | 5 | 5 | NULL | Absent | +------------+-----------+-------+--------+
What this code does
- For each row MySQL tries the WHEN tests from the top and stops at the first true one. 81 fails
>= 90and passes>= 75, so it gets A, never reaching the later tests. - The NULL test comes first, so student 5 in course 5 gets
Absent. Without it, every test would be UNKNOWN and the row would fall toELSE 'F'.
Example 4 · Simple CASE, and the WHEN-order trap
-- Simple CASE: one value compared with fixed valuesSELECT order_id, CASE status WHEN 'PLACED' THEN 'Order placed' WHEN 'SHIPPED' THEN 'On the way' WHEN 'DELIVERED' THEN 'Delivered' ELSE 'Cancelled' END AS shown_to_customerFROM ordersWHERE customer_id = 2ORDER BY order_id;
-- Trap: the loosest test first catches everythingSELECT name, price, CASE WHEN price >= 1000 THEN 'Mid range' WHEN price >= 20000 THEN 'Premium' ELSE 'Budget' END AS bucketFROM productsWHERE category_id = 2ORDER BY product_id;+----------+-------------------+ | order_id | shown_to_customer | +----------+-------------------+ | 2 | Delivered | | 7 | On the way | | 15 | Order placed | +----------+-------------------+ +--------------------+----------+-----------+ | name | price | bucket | +--------------------+----------+-----------+ | Redmi Note 13 | 17999.00 | Mid range | | Samsung Galaxy M35 | 19999.00 | Mid range | | iPhone 15 | 69900.00 | Mid range | +--------------------+----------+-----------+
What this code does
- The simple form compares
statuswith each WHEN value, so the app gets friendly text and the table keeps its short codes. - In the second query the iPhone at 69900 is labelled
Mid range:price >= 1000is true, so MySQL never checks>= 20000. Putting the Premium test first fixes it.
There is also a short simple CASE that compares one value with fixed values: CASE status WHEN 'PLACED' THEN 'Order placed' END.
- Order the WHEN tests from the strictest to the loosest:
>= 90before>= 75before>= 40. - Put
WHEN marks IS NULLfirst when NULL needs its own label;WHEN marks = NULLnever matches. - Always finish with
END, and give the column an alias.
Find the bug
Find the wrong CASE line
Select the line that is wrong.
Marks of 95 should give Distinction, but they give Pass. Which line is wrong?
Round 1 of 3CASE in ORDER BY
CASE in ORDER BY turns each value into a rank number and sorts the rows by that rank. It gives a custom sort order when alphabetical order is wrong.
Sorted as text, priorities come out as High, Low, Medium, so a Low complaint jumps ahead of a Medium one. The example ranks the words with CASE and sorts by the rank; the table stays unchanged.
Example 5 · Custom sort order for hostel complaints
DROP TABLE IF EXISTS hostel_complaints;CREATE TABLE hostel_complaints ( complaint_id INT PRIMARY KEY, room_no INT NOT NULL, issue VARCHAR(40) NOT NULL, priority VARCHAR(10) NOT NULL);INSERT INTO hostel_complaints VALUES (1, 204, 'Fan not working', 'Medium'), (2, 118, 'No water in bathroom', 'High'), (3, 305, 'Wi-Fi slow', 'Low'), (4, 112, 'Broken window', 'High'), (5, 220, 'Tube light flickers', 'Medium');
-- Alphabetical: High, Low, Medium (wrong for a warden)SELECT complaint_id, issue, priorityFROM hostel_complaintsORDER BY priority, complaint_id;
-- By urgency: High, Medium, LowSELECT complaint_id, issue, priorityFROM hostel_complaintsORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END, complaint_id;+--------------+----------------------+----------+ | complaint_id | issue | priority | +--------------+----------------------+----------+ | 2 | No water in bathroom | High | | 4 | Broken window | High | | 3 | Wi-Fi slow | Low | | 1 | Fan not working | Medium | | 5 | Tube light flickers | Medium | +--------------+----------------------+----------+ +--------------+----------------------+----------+ | complaint_id | issue | priority | +--------------+----------------------+----------+ | 2 | No water in bathroom | High | | 4 | Broken window | High | | 1 | Fan not working | Medium | | 5 | Tube light flickers | Medium | | 3 | Wi-Fi slow | Low | +--------------+----------------------+----------+
What this code does
- Sorting the text puts
LowbeforeMedium, so the Wi-Fi complaint jumps ahead of the fan and the tube light. - The CASE turns each priority into a rank, 1, 2 or 3, and the rows are sorted by that rank.
complaint_idthen breaks ties, so older complaints come first within each priority.
IF() as MySQL shorthand
IF(condition, a, b) is a MySQL function that returns a when the condition is true and b otherwise. It is a CASE with exactly one test, and an UNKNOWN condition gives b.
For a yes/no label, IF() is shorter than CASE. The first query labels products In stock or Out of stock; the second shows what happens to an absent student whose mark is NULL.
Example 6 · IF() for yes/no labels, and its NULL trap
SELECT name, stock, IF(stock > 0, 'In stock', 'Out of stock') AS availabilityFROM productsWHERE category_id = 4ORDER BY product_id;
SELECT student_id, marks, IF(marks >= 40, 'Pass', 'Fail') AS naive_result, IF(marks IS NULL, 'Absent', IF(marks >= 40, 'Pass', 'Fail')) AS resultFROM enrollmentsWHERE course_id = 5ORDER BY student_id;+------------------------+-------+--------------+ | name | stock | availability | +------------------------+-------+--------------+ | boAt Airdopes 141 | 120 | In stock | | Mi Power Bank 20000mAh | 60 | In stock | | Logitech M331 Mouse | 0 | Out of stock | +------------------------+-------+--------------+ +------------+-------+--------------+--------+ | student_id | marks | naive_result | result | +------------+-------+--------------+--------+ | 4 | 80 | Pass | Pass | | 5 | NULL | Fail | Absent | +------------+-------+--------------+--------+
What this code does
IF(stock > 0, ...)is the same asCASE WHEN stock > 0 THEN 'In stock' ELSE 'Out of stock' END, written in one short call.- For the NULL mark,
marks >= 40is UNKNOWN and IF() takes the false branch:Fail. The second column checks for NULL first, so the absent student is labelled correctly.
IF() is MySQL-only, while CASE is standard SQL and works in every database. PostgreSQL has no IF() function, so shared queries use CASE. Test for NULL first when it matters.
Next step
Check your understanding
11 quick questions