SELECT - Querying Data

The Shape of a Query

SELECT is the statement you will write more than all the others combined. Its job is to read: it never changes stored data, so it is safe to experiment with. Three clauses carry most of the work — SELECT says which columns you want, FROM says which table they come from, and WHERE says which rows qualify.

SELECT * means "every column". It is convenient while exploring a table you have not seen before, and a bad habit everywhere else. It drags back columns you do not need, which costs network time and memory; it silently changes what your program receives the moment somebody adds a column; and it hides your intent from the next person reading the query. Naming your columns is not pedantry — it is what lets a database use a covering index and what stops a report breaking after a schema change.

AS gives a column a temporary name in the result, called an alias. Aliases matter more than they look: when a column is the output of a calculation, without an alias you get a heading like marks * 0.75, and your application code has to refer to it by that exact string. The AS keyword is optional in most databases, but writing it makes the query far easier to read.

-- Everything, for a quick look at an unfamiliar table
SELECT * FROM students;

-- What you should actually write
SELECT name, branch, marks FROM students;

-- Filter the rows
SELECT name, marks
FROM students
WHERE branch = 'CSE';

-- Aliases, including one on a calculated column
SELECT
    name        AS student_name,
    marks       AS total,
    marks * 0.6 AS weighted_score
FROM students;

-- A calculation needs no table at all
SELECT 15 * 4 AS result;

The Order SQL Really Runs In

A query is written SELECT ... FROM ... WHERE ..., but it is not evaluated in that order. The engine works through the clauses in a fixed logical sequence, and knowing that sequence explains a whole family of otherwise baffling errors.

FROM comes first: the engine works out which table or joined set of tables it is reading. Then WHERE throws away rows. Then GROUP BY collapses what remains into groups, and HAVING throws away whole groups. Only then does SELECT run, computing your expressions and applying your aliases. ORDER BY sorts the finished result, and LIMIT takes a slice of it.

Now the payoff. An alias created in SELECT does not exist yet when WHERE runs, so WHERE weighted_score > 50 fails with "unknown column" in every major database. You have to repeat the expression: WHERE marks * 0.6 > 50. But ORDER BY runs after SELECT, so ORDER BY weighted_score works perfectly. Same alias, same query, different answer — purely because of when each clause runs.

The other payoff is performance intuition. Because WHERE runs before GROUP BY, filtering early means the grouping has fewer rows to chew through. Because LIMIT runs last, LIMIT 10 does not save the database from sorting the entire result first.

• 1. FROM / JOIN — decide which rows are on the table at all
• 2. WHERE — discard individual rows that fail the condition
• 3. GROUP BY — collapse the survivors into groups
• 4. HAVING — discard whole groups that fail the condition
• 5. SELECT — compute the output columns; aliases come into existence here
• 6. DISTINCT — remove duplicate output rows
• 7. ORDER BY — sort the finished result, where aliases are usable
• 8. LIMIT / OFFSET — take a slice of the sorted result

-- This FAILS: weighted_score does not exist when WHERE runs
-- SELECT name, marks * 0.6 AS weighted_score
-- FROM students
-- WHERE weighted_score > 50;

-- Correct: repeat the expression in WHERE
SELECT name, marks * 0.6 AS weighted_score
FROM students
WHERE marks * 0.6 > 50;

-- But ORDER BY runs after SELECT, so the alias works there
SELECT name, marks * 0.6 AS weighted_score
FROM students
WHERE branch = 'CSE'
ORDER BY weighted_score DESC;

Comparisons, AND/OR, and a Precedence Trap

Conditions in WHERE are built from comparison operators and joined with AND, OR and NOT. The operators themselves hold no surprises. The way they combine does.

AND binds more tightly than OR, exactly as multiplication binds more tightly than addition in arithmetic. So WHERE branch = 'CSE' OR branch = 'ECE' AND marks > 80 does not mean what an English reading suggests. SQL reads it as branch = 'CSE' OR (branch = 'ECE' AND marks > 80), which returns every CSE student regardless of marks. The fix is parentheses, and the habit worth forming is to write them whenever AND and OR appear in the same condition — even where they are not strictly needed, because the next person to read the query should not have to remember a precedence rule.

One small portability point: MySQL and PostgreSQL have a real BOOLEAN type and accept WHERE is_active = TRUE. SQL Server has no boolean column type at all — it uses BIT, and you write WHERE is_active = 1. Comparing against 1 and 0 works in MySQL too, so it is the safer thing to write if your query might travel.

• = — equal to
• <> or != — not equal to (<> is the standard form; != is widely accepted)
• > and < — greater than, less than
• >= and <= — greater than or equal to, less than or equal to
• AND — both sides must be true; evaluated before OR
• OR — at least one side must be true
• NOT — inverts a condition

-- Simple comparisons
SELECT name, marks FROM students WHERE marks >= 80;
SELECT name FROM students WHERE branch <> 'CSE';

-- The precedence trap: AND is evaluated before OR
-- Reads as: CSE (any marks) OR (ECE AND marks > 80)
SELECT name, branch, marks
FROM students
WHERE branch = 'CSE' OR branch = 'ECE' AND marks > 80;

-- What was almost certainly intended
SELECT name, branch, marks
FROM students
WHERE (branch = 'CSE' OR branch = 'ECE') AND marks > 80;

-- Combining several conditions readably
SELECT name, branch, marks
FROM students
WHERE branch IN ('CSE', 'IT')
  AND marks BETWEEN 70 AND 95
  AND is_active = 1;

NULL Is Not Equal to Anything — Including Itself

This is the single most misunderstood idea in SQL, and it produces bugs that no error message warns you about. NULL does not mean zero and does not mean an empty string. It means unknown. And once you read it as "unknown", the strange behaviour becomes logical.

Ask whether an unknown value equals 50. The honest answer is not "yes" and not "no" — it is "I cannot tell". SQL therefore evaluates marks = 50 to a third truth value, UNKNOWN, whenever marks is NULL. And WHERE only keeps rows where the condition is true. UNKNOWN is not true, so the row is dropped. The same reasoning explains why WHERE marks = NULL never matches anything, ever: you are asking whether an unknown equals an unknown, and even NULL = NULL is UNKNOWN. Because of this, SQL gives you dedicated operators: IS NULL and IS NOT NULL.

The consequence that actually bites is silent row loss. Suppose ten students have not been marked yet, so their marks is NULL. Now run WHERE marks < 40 to find the failures, and separately WHERE marks >= 40 to find the passes. The two results do not add up to the class, because those ten rows fall out of both. Nothing errors. The report is simply wrong, and it stays wrong until someone counts.

There is a second trap in NOT IN. If the list — or the subquery producing it — contains a single NULL, NOT IN returns no rows at all. Asking "is this value not equal to 3, not equal to 7, and not equal to unknown?" can never be answered true, so every row is discarded. NOT EXISTS does not suffer from this and is the safer construction, as the subqueries lesson explains.

-- These never match anything, no matter what is in the table
SELECT name FROM students WHERE marks = NULL;
SELECT name FROM students WHERE marks <> NULL;

-- Correct
SELECT name FROM students WHERE marks IS NULL;
SELECT name FROM students WHERE marks IS NOT NULL;

-- The silent gap: these two do NOT add up to every student
SELECT COUNT(*) FROM students WHERE marks < 40;
SELECT COUNT(*) FROM students WHERE marks >= 40;

-- Include the unmarked students explicitly
SELECT COUNT(*) FROM students WHERE marks >= 40 OR marks IS NULL;

-- Substitute a stand-in value for display with COALESCE,
-- which returns its first non-NULL argument
SELECT
    name,
    COALESCE(marks, 0)            AS marks_shown,
    COALESCE(phone, 'not given')  AS contact
FROM students;

-- NOT IN with a NULL in the list returns nothing at all
SELECT name FROM students WHERE branch NOT IN ('CSE', 'ECE', NULL);

LIKE, IN, BETWEEN and DISTINCT

Four operators cover most of the filtering you will do beyond simple comparisons. IN tests membership of a list and is far more readable than a chain of ORs. BETWEEN tests a range and is inclusive at both ends — BETWEEN 70 AND 90 includes 70 and 90. LIKE matches a text pattern, where % stands for any run of characters and _ for exactly one. DISTINCT removes duplicate rows from the result.

BETWEEN has a genuine trap with date-and-time columns. If ordered_at is a DATETIME, then BETWEEN '2026-01-01' AND '2026-06-30' quietly means up to midnight on 30 June, so everything ordered during that last day is excluded. Nobody notices until a month-end total is short. The robust pattern is a half-open range: greater than or equal to the start, and strictly less than the day after the end.

LIKE has a performance trap. A pattern anchored at the start, 'An%', can use an index — the database jumps to the right place alphabetically and scans forward. A pattern starting with a wildcard, '%sharma', cannot, because there is no starting point to jump to; the engine must examine every row. That is fine on a practice table and fatal on a table with millions of rows, which is why real search boxes are backed by a full-text index rather than by LIKE '%term%'.

DISTINCT applies to the whole selected row, not to one column. SELECT DISTINCT branch, marks gives distinct combinations of branch and marks, which is usually not what someone writing it wanted. And because deduplicating means sorting or hashing everything, DISTINCT sprinkled on a query to "fix" unexpected duplicates is usually a sign that a join is wrong — fix the join instead.

-- IN: much clearer than three ORs
SELECT name, branch FROM students WHERE branch IN ('CSE', 'IT', 'ECE');

-- BETWEEN is inclusive of both ends
SELECT name, marks FROM students WHERE marks BETWEEN 70 AND 90;

-- DATETIME trap: this misses everything ordered ON 30 June after midnight
-- SELECT * FROM orders WHERE ordered_at BETWEEN '2026-01-01' AND '2026-06-30';

-- Safe half-open range instead
SELECT * FROM orders
WHERE ordered_at >= '2026-01-01'
  AND ordered_at <  '2026-07-01';

-- LIKE patterns
SELECT name FROM students WHERE name LIKE 'An%';      -- starts with An
SELECT name FROM students WHERE name LIKE '%Nair';    -- ends with Nair (slow)
SELECT name FROM students WHERE name LIKE '_a%';      -- 'a' as 2nd character
SELECT email FROM students WHERE email LIKE '%@example.com';

-- Searching for a literal percent sign needs an escape character
SELECT name FROM offers WHERE title LIKE '%50!%%' ESCAPE '!';

-- DISTINCT applies to the whole row
SELECT DISTINCT branch FROM students;                 -- one row per branch
SELECT DISTINCT branch, marks FROM students;          -- distinct PAIRS