Query Fundamentals

Lesson 0003 — SELECT, WHERE, Operators, ORDER BY, LIMIT, DISTINCT & Aliases — ~15 min read

Reading data is the core of SQL. Every real question — “Who signed up today?” or “Top 10 customers by spend?” — is answered by picking the right table, the right columns, the right rows, and the right order.

Once you understand what a database and SQL are, the next step is learning how to read data. Commands like SELECT, FROM, WHERE, logical operators (AND/OR/NOT), comparison operators, ORDER BY, LIMIT, DISTINCT, and AS (aliases) work together to pick the right rows, choose the right columns, and present results clearly.

At a high level, every basic query answers:

• From which table do I want data? → FROM
• Which columns do I want? → SELECT
• Which rows should stay or go? → WHERE
• In what order should I see them? → ORDER BY
• All rows or just a few? → LIMIT / DISTINCT

The Example: countries Table

To make every command concrete, we will use one relatable table throughout this lesson.

CREATE TABLE countries (
  id INT PRIMARY KEY AUTO_INCREMENT,
  country VARCHAR(64) NOT NULL,
  population BIGINT NULL, area INT NOT NULL,
  region VARCHAR(64) NOT NULL
);

INSERT INTO countries (country, population, area, region) VALUES
('India',     1400000000, 3287000, 'Asia'),
('China',     1200000000, 4287000, 'Asia'),
('Brazil',     214000000, 8516000, 'South America'),
('Australia',   25700000, 7692000, 'Oceania'),
('USA',        331000000, 9834000, 'North America');

CREATE TABLE countries (
  id SERIAL PRIMARY KEY,
  country VARCHAR(64) NOT NULL,
  population BIGINT NULL, area INT NOT NULL,
  region VARCHAR(64) NOT NULL
);

INSERT INTO countries (country, population, area, region) VALUES
('India',     1400000000, 3287000, 'Asia'),
('China',     1200000000, 4287000, 'Asia'),
('Brazil',     214000000, 8516000, 'South America'),
('Australia',   25700000, 7692000, 'Oceania'),
('USA',        331000000, 9834000, 'North America');

1. SELECT & FROM

SELECT specifies which columns you want. FROM tells SQL which table to read.

All columns, all rows

SELECT *
FROM countries;

SELECT *
FROM countries;

Useful when exploring a new table, but prefer explicit columns in real code.

Specific columns only

SELECT country, population
FROM countries;

SELECT country, population
FROM countries;

Result is narrower and focused — build the habit: only select what you need.

2. WHERE — Filtering Rows

WHERE keeps only rows that satisfy a condition.

SELECT *
FROM countries
WHERE region = 'Oceania';

SELECT *
FROM countries
WHERE region = 'Oceania';

WHERE decides which rows stay.

3. Comparison Operators

These form the foundation of filtering. We will use them in every query ahead.

4. Logical Operators — AND, OR, NOT

Combine or modify conditions in WHERE.

a. AND — all conditions must be true

Population > 50M and area < 5M sq km:

SELECT *
FROM countries
WHERE population > 50000000 AND area < 5000000;

SELECT *
FROM countries
WHERE population > 50000000 AND area < 5000000;

b. OR — at least one condition must be true

Nations in Asia or Oceania:

SELECT *
FROM countries
WHERE region = 'Asia' OR region = 'Oceania';

SELECT *
FROM countries
WHERE region = 'Asia' OR region = 'Oceania';

c. NOT — negates a condition

Exclude North America:

SELECT *
FROM countries
WHERE NOT region = 'North America';
-- equivalent
SELECT * FROM countries WHERE region <> 'North America';

SELECT *
FROM countries
WHERE NOT region = 'North America';

5. Arithmetic Operators & NULLIF

NULLIF — Safe Division

NULLIF(value1, value2) returns NULL if the two values are equal, otherwise value1. Common use: prevent division-by-zero — convert 0 to NULL so division yields NULL instead of an error.

Syntax: NULLIF(value1, value2)

SELECT
  country,
  population / NULLIF(area, 0) AS density
FROM countries;

SELECT
  country,
  population / NULLIF(area, 0) AS density
FROM countries;

If a test row had area = 0, NULLIF(area, 0) → NULL, so population / NULL → NULL safely instead of an error.

6. ORDER BY & LIMIT

ORDER BY sorts rows. Default is ASC (small → large, A → Z). Use DESC for reverse (large → small, Z → A). LIMIT returns only the top N rows.

Top 2 countries by land area (largest first):

SELECT country, area
FROM countries
ORDER BY area DESC
LIMIT 2;

SELECT country, area
FROM countries
ORDER BY area DESC
LIMIT 2;
-- or FETCH FIRST 2 ROWS ONLY

First sort by area DESC, then keep only the top 2 via LIMIT.

7. DISTINCT & AS (Aliases)

DISTINCT removes duplicates — keep only unique values. AS gives a temporary, reader-friendly name to a column or table without changing the schema.

Unique regions, renamed to unique_region:

SELECT DISTINCT region AS unique_region
FROM countries;

SELECT DISTINCT region AS unique_region
FROM countries;

Notes

Primary sources: MySQL SELECT · Postgres SELECT · Postgres NULLIF. 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.