ASAmol Shukla

4. SELECT, WHERE, NULL and sorting

Step 1 of 4 · Lesson

Lesson 4 of 21

SELECT, WHERE, NULL and ORDER BY in MySQL

Choose columns with SELECT, keep matching rows with WHERE, combine conditions with AND / OR / NOT, handle NULL correctly and sort results with ORDER BY.

90 min 4 min read 19 problems · 0 solved Learning Shop (small)

In short

How do you filter and sort rows in MySQL?

Use WHERE to keep only rows that match a condition and ORDER BY to sort them: SELECT product_name, price FROM products WHERE price >= 300 ORDER BY price DESC;. Combine conditions with AND / OR (add brackets when you mix them) and test missing values with IS NULL, never = NULL.

By the end of this lesson you can

  • Pick exactly the columns you need and name calculated columns
  • Filter rows with =, <>, <, >, <=, >= on numbers, text and dates
  • Combine conditions with AND, OR and NOT — and know which runs first
  • Find missing values with IS NULL / IS NOT NULL
  • Sort by one or more columns, ascending or descending

The shape of a query

Almost every query you will write follows this order:

SELECT   columns you want to see
FROM     the table they come from
WHERE    which rows to keep
ORDER BY how to sort the result;

Only SELECT and FROM are required. Name the columns instead of using * — the result is narrower, faster and does not change when someone adds a column to the table.

SQL · shop
SELECT product_name, price
FROM products
ORDER BY product_id;
MySQL result6 rows
product_nameprice
Notebook100.00
Pen20.00
Desk lamp800.00
Mug250.00
Keyboard1200.00
Planner300.00

Calculated columns

You can calculate in the SELECT list. The table is not changed; only the output gets a new column. Give every calculated column an alias:

SQL · shop
SELECT product_name, price, price * 1.18 AS price_with_gst
FROM products
ORDER BY product_id;
MySQL result6 rows
product_namepriceprice_with_gst
Notebook100.00118.0000
Pen20.0023.6000
Desk lamp800.00944.0000
Mug250.00295.0000
Keyboard1200.001416.0000
Planner300.00354.0000

MySQL shows four decimals (118.0000) because multiplying a DECIMAL(10,2) by 1.18 keeps the extra precision. Lesson 6 shows ROUND() to tidy this.

WHERE keeps the rows that match

WHERE tests every row and keeps those where the condition is true.

Operator Meaning Example
= equal city = 'Pune'
<> or != not equal status <> 'completed'
> >= < <= greater / less price >= 300

Text and dates go in single quotes. In MySQL, text comparison ignores upper/lower case by default, so 'pune' also matches Pune.

SQL · shop
SELECT customer_id, customer_name, city
FROM customers
WHERE city = 'Pune'
ORDER BY customer_id;
MySQL result2 rows
customer_idcustomer_namecity
1AshaPune
3CharuPune

Dates compare in calendar order when written as 'YYYY-MM-DD': WHERE order_date >= '2026-02-01' keeps February and March orders.

AND, OR, NOT — and which runs first

  • AND — both conditions must be true.
  • OR — at least one must be true.
  • NOT — reverses a condition.

AND runs before OR, exactly as * runs before +. That surprises people. Suppose you want Stationery or Home products that cost under 200:

SQL · shop
-- Wrong: reads as Stationery OR (Home AND price < 200)
SELECT product_name, category, price
FROM products
WHERE category = 'Stationery' OR category = 'Home' AND price < 200
ORDER BY product_id;
MySQL result3 rows
product_namecategoryprice
NotebookStationery100.00
PenStationery20.00
PlannerStationery300.00

Planner (300.00) slipped in, because every Stationery row passes the first half on its own. Brackets fix it:

Brackets make the meaning explicit

Whenever you mix AND with OR, add brackets — even when you think the default order is right. Reviewers will thank you.

SQL · shop
SELECT product_name, category, price
FROM products
WHERE (category = 'Stationery' OR category = 'Home') AND price < 200
ORDER BY product_id;
MySQL result2 rows
product_namecategoryprice
NotebookStationery100.00
PenStationery20.00

NULL needs IS NULL

Remember from lesson 1: NULL means unknown. Comparing anything with unknown gives unknown — not true — so WHERE email = NULL returns no rows, without any error. Use IS NULL and IS NOT NULL:

SQL · shop
SELECT customer_id, customer_name, email
FROM customers
WHERE email IS NULL;
MySQL result1 row
customer_idcustomer_nameemail
3CharuNULL

A second trap: WHERE email <> 'asha@example.test' also skips Charu, because comparing her NULL email gives unknown. If missing values should be included, say so: WHERE email <> 'asha@example.test' OR email IS NULL.

ORDER BY: sorting results

SQL does not guarantee any row order unless you ask for one. ORDER BY sorts ascending (ASC, the default) or descending (DESC). List several columns to break ties — the second column only matters when the first is equal:

SQL · shop
SELECT product_name, category, price
FROM products
ORDER BY category, price DESC;
MySQL result6 rows
product_namecategoryprice
KeyboardElectronics1200.00
Desk lampElectronics800.00
MugHome250.00
PlannerStationery300.00
NotebookStationery100.00
PenStationery20.00

In MySQL, NULLs sort first in ascending order and last in descending order. Sorting by a column that is not unique? Add the primary key at the end (ORDER BY price DESC, product_id) so the order is repeatable.

Write queries other people can read

SQL ignores spaces and line breaks, so use them for the reader. A few habits make a query easy to review — and easy for you to debug next month:

  • One clause per line, keywords in capitals: SELECT, FROM, WHERE, ORDER BY each start a line.
  • Name the columns you need instead of *, in the order the reader should see them.
  • Alias calculated columns with clear names: price_with_gst, not col3.
  • Comment the why, not the what: -- exclude test customers tells a reviewer something the SQL cannot.
  • Put each condition on its own line with AND / OR at the start, so a condition can be commented out while testing.
SQL · shop
-- Products a customer can buy for under 500, cheapest first
SELECT product_name,
       category,
       price
FROM products
WHERE price < 500
  AND category <> 'Electronics'   -- electronics ship separately
ORDER BY price, product_id;
MySQL result4 rows
product_namecategoryprice
PenStationery20.00
NotebookStationery100.00
MugHome250.00
PlannerStationery300.00

Common mistakes

Wrong

WHERE email = NULL

Fix

WHERE email IS NULL

Why: Comparing with NULL is never true, so no rows come back — and there is no error to warn you.

Wrong

WHERE category = 'Stationery' OR category = 'Home' AND price < 200

Fix

WHERE (category = 'Stationery' OR category = 'Home') AND price < 200

Why: AND binds before OR, so the price limit only applies to Home.

Wrong

WHERE city = Pune

Fix

WHERE city = 'Pune'

Why: Without quotes MySQL looks for a column called Pune (error 1054).

Wrong

Relying on the order rows happen to come back in

Fix

Always add ORDER BY with a unique tiebreaker for reports.

Why: Without ORDER BY the order can change between runs, servers and versions.

Check yourself

  1. Why does WHERE email = NULL return nothing instead of Charu?
  2. Rewrite a condition that mixes AND and OR so its meaning is obvious.
  3. Why is ORDER BY price DESC, product_id better than ORDER BY price DESC for a report?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Is SQL case-sensitive in MySQL?

Keywords are not. Text comparisons are case-insensitive with MySQL's default utf8mb4 collation, so 'pune' = 'Pune'. Table names can be case-sensitive on Linux servers, so match their exact case.

What is the difference between <> and != in MySQL?

None — both mean not equal. <> is the SQL standard; != is accepted by MySQL and most other databases.

Why does my WHERE with NULL return no rows?

Because = NULL compares with an unknown value and is never true. Use IS NULL or IS NOT NULL.

Quick quiz — 4 questions

Quick Check: SELECT, WHERE, NULL and sorting

1 / 4
How many rows does SELECT * FROM customers WHERE email = NULL; return?

Answers — SELECT, WHERE, NULL and sorting

  1. Easy. List product names and prices in product-id order. Target: 6 rows; Notebook is 100.00 and Planner is 300.00.

    SELECT product_name, price FROM products ORDER BY product_id;

  2. Medium. Find products costing at least 300, most expensive first. Target: Keyboard, Desk lamp, Planner, in that order.

    SELECT product_name, price FROM products WHERE price >= 300 ORDER BY price DESC, product_id;

  3. Hard. Find customers whose email is missing. Target: Only Charu, customer_id 3. Explain why = NULL is wrong.

    SELECT customer_id, customer_name FROM customers WHERE email IS NULL;

  4. Professional. Prepare a stable two-row extract for a customer service team. Target: First page ids 1,2; second page 3,4. Document that offset pages can shift when source data changes.

    SELECT customer_id, customer_name, city FROM customers ORDER BY customer_id LIMIT 2 OFFSET 0;
SELECT customer_id, customer_name, city FROM customers ORDER BY customer_id LIMIT 2 OFFSET 2;

  5. Easy. List all customers from Mumbai. Target: 2 rows: (2, Bilal); (5, Esha)

    SELECT customer_id, customer_name FROM customers WHERE city = 'Mumbai' ORDER BY customer_id;

  6. Easy. List products in the Stationery category. Target: 3 rows: (Notebook, 100.00); (Pen, 20.00); (Planner, 300.00)

    SELECT product_name, price FROM products WHERE category = 'Stationery' ORDER BY product_id;

  7. Easy. List all cancelled orders. Target: 1 row: order_id = 104, customer_id = 3

    SELECT order_id, customer_id FROM orders WHERE status = 'cancelled';

  8. Easy. List products sorted by name A to Z. Target: 6 rows: Desk lamp, Keyboard, Mug, Notebook, Pen, Planner

    SELECT product_name FROM products ORDER BY product_name;

  9. Medium. List products cheaper than 300. Target: 3 rows: (Pen, 20.00); (Notebook, 100.00); (Mug, 250.00)

    SELECT product_name, price FROM products WHERE price < 300 ORDER BY price;

  10. Medium. List orders that are not completed. Target: 2 rows: (104, cancelled); (107, pending)

    SELECT order_id, status FROM orders WHERE status <> 'completed' ORDER BY order_id;

  11. Medium. List Stationery products costing more than 50. Target: 2 rows: (Notebook, 100.00); (Planner, 300.00)

    SELECT product_name, price FROM products WHERE category = 'Stationery' AND price > 50 ORDER BY product_id;

  12. Medium. List customers from Pune or Delhi. Target: 3 rows: (Asha, Pune); (Charu, Pune); (Dev, Delhi)

    SELECT customer_name, city FROM customers WHERE city = 'Pune' OR city = 'Delhi' ORDER BY customer_id;

  13. Medium. List customers who do have an email. Target: 5 rows: everyone except Charu

    SELECT customer_name, email FROM customers WHERE email IS NOT NULL ORDER BY customer_id;

  14. Hard. List customers sorted by city, then by name within each city. Target: 6 rows: (Dev, Delhi); (Farah, Jaipur); (Bilal, Mumbai); (Esha, Mumbai); (Asha, Pune); (Charu, Pune)

    SELECT customer_name, city FROM customers ORDER BY city, customer_name;

  15. Hard. Show each product with a new column price_with_gst = price * 1.18. Target: 6 rows; Notebook 118.00, Pen 23.60, Desk lamp 944.00, Mug 295.00, Keyboard 1416.00, Planner 354.00

    SELECT product_name, price, price * 1.18 AS price_with_gst FROM products ORDER BY product_id;

  16. Hard. Find Electronics products OR any product under 50, and explain the brackets in: category = 'Home' AND (price < 300 OR price > 1000). Target: 3 rows: Pen, Desk lamp, Keyboard

    SELECT product_name FROM products WHERE category = 'Electronics' OR price < 50 ORDER BY product_id;

  17. Hard. Show all customers with the missing email replaced by the text 'no email' (preview of COALESCE). Target: 6 rows; Charu shows no email

    SELECT customer_name, COALESCE(email, 'no email') AS email FROM customers ORDER BY customer_id;

  18. Professional. Prepare the 2 most expensive products for a price-review meeting. Target: 2 rows: (Keyboard, 1200.00); (Desk lamp, 800.00)

    SELECT product_name, price FROM products ORDER BY price DESC, product_id LIMIT 2;

  19. Professional. Give the delivery team every completed order, newest first, with order_id and date only. Target: 6 rows: 108, 106, 105, 103, 102, 101

    SELECT order_id, order_date FROM orders WHERE status = 'completed' ORDER BY order_date DESC;

Up next · Lesson 5

IN, BETWEEN, LIKE, DISTINCT, LIMIT

75 min · 17 practice problems

Start next lesson
Import and explore the shop data IN, BETWEEN, LIKE, DISTINCT, LIMIT