SQL Basics: SELECT, WHERE, ORDER BY & LIMIT Explained

Imagine your college keeps one giant register of all 5,000 students. The placement cell walks in: "Give me the names of final-year CS students with CGPA above 8." What do you do? You go through the register, keep only the rows that match, and write down just the columns you need. That thought process — filter rows, pick columns — is SQL. This whole lesson is just teaching your brain to write that thought down formally.

SELECT and FROM — asking the register a question

Every read query has the same skeleton: FROM names the table, SELECT names the columns you want back. For the examples in this lesson, we'll use this employees table — nine people, three departments, one missing salary (that blank will become important very soon):

The simplest possible query grabs everything:

SELECT * FROM employees;

* means "all columns". Handy for exploring, but in real code you name the columns you need — it's faster, and your query doesn't break when someone adds a column later:

SELECT name, salary
FROM employees;

Alice Sharma   | 95000
Bob Iyer       | 72000
... (9 rows — every employee, just two columns)

You can even compute new columns on the fly and name them with AS:

SELECT name, salary * 12 AS annual_salary
FROM employees;

WHERE — keeping only the rows you care about

WHERE is the bouncer at the door: every row walks up, the condition checks it, and only rows where the condition is TRUE get in. Conditions use operators you already know: =, <> (not equal), >, <, >=, <= — combined with AND, OR, NOT, plus two nice shortcuts: BETWEEN and IN.

SELECT name, department, salary
FROM employees
WHERE department = 'Sales' AND salary > 55000;

Chitra Rao | Sales | 65000
David Paul | Sales | 58000

The shortcuts read even more like English — WHERE salary BETWEEN 60000 AND 90000 (inclusive on both ends, a detail interviewers like), and WHERE department IN ('Sales', 'Marketing') instead of chaining ORs.

DISTINCT — each value only once

"Which departments exist in this company?" A plain SELECT department would print Sales four times. DISTINCT collapses the duplicates:

SELECT DISTINCT department
FROM employees;

Engineering
Sales
Marketing

One subtlety worth locking in now: with two columns, DISTINCT de-duplicates the pair as a whole — SELECT DISTINCT city, state keeps (Mumbai, Maharashtra) and (Mumbai, Goa) both, because the combinations differ. It never de-duplicates each column separately.

NULL — a blank is not a zero

Look back at Harish Verma's salary in our table. It's not 0. It's not empty text. It's NULL — meaning "unknown". Maybe HR hasn't entered it yet; maybe it doesn't apply. SQL is very philosophical about this: you cannot ask "is something equal to unknown?" and get a yes.

So what do you think this returns?

SELECT name FROM employees WHERE salary = NULL;

(zero rows — always, even though Harish's salary IS null)

NULL gets its own special checks:

-- the right way
SELECT name FROM employees WHERE salary IS NULL;
SELECT name FROM employees WHERE salary IS NOT NULL;

Selection-round radar: the = NULL trap is asked practically everywhere — service and product companies both. If you're ever shown a query containing = NULL or != NULL, the bug is the question. Say "three-valued logic" in your answer and watch the interviewer's eyebrows go up.

LIKE — searching when you only know a part

You type "Ra" in your phone's contact search and get Rahul, Ravi, Ramesh. SQL's version is LIKE with two wildcards: % matches any number of characters (including zero), and _ matches exactly one.

SELECT name FROM employees WHERE name LIKE 'A%';   -- starts with A
SELECT name FROM employees WHERE name LIKE '%Verma'; -- ends with Verma
SELECT name FROM employees WHERE name LIKE '_a%';   -- second letter is a

'A%'      → Alice Sharma, Amit Verma
'%Verma'  → Harish Verma, Amit Verma
'_a%'     → David Paul, Farhan Khan, Harish Verma

Need to search for a literal % inside text (say, a discount column storing "50% off")? Escape it: WHERE offer LIKE '%50\%%' — the \% is a real percent sign, the outer %s are wildcards.

ORDER BY — sorting the answer

Without ORDER BY, SQL promises you nothing about row order — whatever order you see is an accident of storage. So when order matters, say so:

SELECT name, department, salary
FROM employees
ORDER BY salary DESC, name ASC;

Two sort keys: highest salary first, and if two people tie, their names break the tie alphabetically. Multi-key sorting like this is how you make an ordering stable — remember that word, it returns in the pagination story below. One MySQL quirk to know: MySQL treats NULL as lower than every value — so ASC puts NULL salaries first and DESC puts them last. To force NULLs to the end regardless of direction: ORDER BY salary IS NULL, salary ASC.

LIMIT and OFFSET — because nobody loads 10 lakh rows

When Instagram opens your feed, it doesn't fetch every post ever made — it fetches one small page, then the next when you scroll. SQL pages results the same way:

-- top 3 earners (skipping NULL salaries)
SELECT name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3;

-- "page 2": skip 3 rows, take the next 3
SELECT name, salary
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 3 OFFSET 3;

Page 1 → Alice Sharma (95000), Esha Nair (88000), Bob Iyer (72000)
Page 2 → Chitra Rao (65000), then the two 60000s…

SQL Server spells this differently (SELECT TOP 3 ...), and MySQL also accepts the short form LIMIT 3, 3 — which means "skip 3, take 3", not rows 3 to 3. That comma form confuses everyone at least once; prefer the explicit LIMIT ... OFFSET ....

But here's the hidden problem. Farhan and Gita both earn exactly 60000. If that tie sits on a page boundary and your ORDER BY has no tie-breaker, the database may order them differently on different runs — the same person can appear on two pages, or on none. The fix is the stable ordering you just learned: ORDER BY salary DESC, emp_id ASC. You'll meet this exact scenario in the Practice Zone.

The order SQL actually runs your query

Here's the "wait, what?" moment of this lesson. You write SELECT first — but SQL runs it almost last.

Why should you care? Try this innocent-looking query:

SELECT salary * 12 AS annual
FROM employees
WHERE annual > 1000000;  -- ERROR: unknown column 'annual'

It fails. When WHERE runs (step 2), the alias annual hasn't been created yet — that happens in SELECT (step 5). But ORDER BY annual works fine, because sorting happens after SELECT. One diagram, and a whole family of "why doesn't this work" questions becomes obvious. This same diagram will explain WHERE vs HAVING in lesson 3.

Common mistakes (I've made all of these)

• Writing WHERE salary = NULL and wondering why the result is empty — use IS NULL.
• Using a SELECT alias inside WHERE — it doesn't exist yet; repeat the expression or use a subquery.
• Assuming rows come back in insertion order without ORDER BY — they don't, and it will bite you exactly once in production.
• Paginating on a non-unique sort key — add a tie-breaker column.
• SELECT * in application code — name your columns.

Quick recap

Practice Zone — PYQs from real selection rounds

Time to earn this lesson. Six click-to-answer questions and three write-the-query challenges — under each one you'll see the companies where that pattern has been asked. Attempt first, reveal second.

Q1DISTINCTeasy

I run SELECT DISTINCT city, state FROM addresses;. What exactly gets de-duplicated?

Asked in

ADuplicate cities are removed, whatever the state isBDuplicate states are removed, whatever the city isCRows where the whole (city, state) pair repeats are removedDBoth columns are de-duplicated independently

Q2NULLeasy

Some rows in my table genuinely have a NULL salary. What does SELECT * FROM employees WHERE salary = NULL; return?

Asked in

AExactly those rows where salary is NULLBNo rows at allCA syntax errorDEvery row in the table

Q3LIKEmedium

Which LIKE pattern matches every name whose second letter is 'a' (like 'Ravi' or 'Karan')?

Asked in

Aname LIKE 'a%'Bname LIKE '_a%'Cname LIKE '%a_'Dname LIKE 'a_%'

Q4Execution ordermedium

This query fails: SELECT salary * 12 AS annual FROM employees WHERE annual > 1000000;. Why can't WHERE see the alias annual?

Asked in

AAliases are only allowed in ORDER BY, nowhere elseBWHERE runs before SELECT, so the alias doesn't exist yetCAliases can't be used with numeric expressionsDYou forgot to wrap the alias in quotes

Q5LIMIT / OFFSETmedium

In MySQL, I want rows 11 to 20 (the second page of 10 results). Which query is right?

Asked in

A... ORDER BY salary DESC LIMIT 10 OFFSET 10;B... ORDER BY salary DESC LIMIT 11, 20;CSELECT TOP 10 ... ORDER BY salary DESC;D... ORDER BY salary DESC LIMIT 20 OFFSET 11;

Q6NULL sortinghard

In MySQL, I run SELECT name, salary FROM employees ORDER BY salary DESC; and one employee has a NULL salary. Where does that row appear?

Asked in

AAt the top, before the highest salaryBAt the bottom, after the lowest salaryCIt's removed from the resultDMySQL throws an error

Now write real queries. Read the table, think, write your answer on paper (seriously — paper), then reveal:

FAQ

Next lesson: data almost never lives in one table — Lesson 2: SQL Joins →