SQL Basics: SELECT, WHERE, ORDER

Create a free account to save your progress

Earn XP, track streaks, and sync your dashboard across devices.

Lesson

Imagine you're a librarian managing millions of books. Without a system, finding a specific book would take forever. But with a proper catalog, you can instantly answer questions like "Show me all science fiction books published after 2020" or "How many books do we have by this author?"

SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. is that catalog system for data. It's the universal language that lets you ask questions of your database and get precise answers. Whether you're querying PostgreSQL, MySQL, SQLite, or SQL Server, the core SQL syntax is nearly identical-learn it once, use it everywhere.

Reading data with SELECT

SELECT is the most common SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. command-it's how you ask your database questions. Think of it as saying "Show me..." followed by what you want to see.

Selecting specific columns

-- Get all columns (use sparingly in production)
SELECT * FROM users;

-- Get only the columns you need (better practice)
SELECT name, email FROM users;

-- Rename columns for clarity
SELECT name AS username, email AS user_email FROM users;

-- Remove duplicate values
SELECT DISTINCT country FROM users;

-- Count rows
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;

The asterisk (*) is convenient for quick exploration, but in production code, always list specific columns. This makes your queries faster (less data transferred) and prevents breaking your application if someone adds a column to the table.

Performance tip

SELECT * forces the database to read every column from disk, even ones you don't need. When querying large tables with many columns, specifying only the columns you need can dramatically improve query speed.

Filtering with WHERE

WHERE is your filter-it narrows down results to only the rows that match your criteria. Without WHERE, you get everything. With WHERE, you get exactly what you need.

Basic comparison operators

-- Exact match
SELECT * FROM users WHERE country = 'France';

-- Numeric comparisons
SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE age >= 18 AND age <= 65;

-- Range check (inclusive)
SELECT * FROM products WHERE price BETWEEN 10 AND 50;

-- Multiple possible values
SELECT * FROM users WHERE country IN ('France', 'Belgium', 'Switzerland');

-- Pattern matching with LIKE
SELECT * FROM users WHERE name LIKE 'John%';          -- Starts with John
SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- Ends with @gmail.com
SELECT * FROM users WHERE name LIKE '%Smith%';        -- Contains Smith

-- Checking for NULL (unknown values)
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

Combining conditions with AND and OR

-- All conditions must be true (AND)
SELECT * FROM users 
WHERE country = 'France' 
  AND age >= 18;

-- Any condition can be true (OR)
SELECT * FROM users 
WHERE city = 'Paris' 
  OR city = 'Lyon';

-- Mixing AND and OR (use parentheses!)
SELECT * FROM users
WHERE country = 'France'
  AND age >= 18
  AND (city = 'Paris' OR city = 'Lyon');

The parentheses are crucial when mixing AND and OR. Without them, the database might interpret your logic differently than you intended.

Operator	Meaning	Example
`=`	Equal to	`country = 'France'`
`<>` or `!=`	Not equal to	`status <> 'deleted'`
`<`	Less than	`age < 18`
`>`	Greater than	`price > 100`
`<=`	Less than or equal	`age <= 65`
`>=`	Greater than or equal	`price >= 10`
`BETWEEN`	Inclusive range	`age BETWEEN 18 AND 65`
`IN`	Match any in list	`country IN ('FR', 'BE')`
`LIKE`	Pattern match	`name LIKE '%John%'`
`IS NULL`	Is null	`phone IS NULL`

LIKE pattern matching

% matches any sequence of characters (including none), _ matches exactly one character. LIKE 'J%' finds "John" and "Jane", LIKE 'J_n' finds "Jan" and "Jen" but not "John".

Sorting results with ORDER BY

By default, databases return rows in whatever order is most efficient-usually the order they were inserted. ORDER BY lets you specify exactly how you want your results sorted.

-- Alphabetical order (A-Z)
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;  -- ASC is default

-- Reverse order (Z-A or highest to lowest)
SELECT * FROM products ORDER BY price DESC;

-- Multiple sort criteria
SELECT * FROM users ORDER BY country ASC, age DESC;

-- Handle NULL values explicitly
SELECT * FROM users ORDER BY phone NULLS LAST;

When you sort by multiple columns, the database sorts by the first column, then for rows with equal values in the first column, it sorts by the second column, and so on.

PaginationWhat is pagination?Splitting a large set of results into smaller pages so the server and client only handle a manageable chunk at a time. with LIMIT and OFFSET

When dealing with large datasets, you rarely want to display thousands of rows at once. Pagination breaks results into manageable chunks.

-- Get first 10 results
SELECT * FROM users LIMIT 10;

-- Get page 2 (results 11-20)
SELECT * FROM users LIMIT 10 OFFSET 10;

-- Get page 3
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Alternative syntax (PostgreSQL)
SELECT * FROM users OFFSET 20 LIMIT 10;

-- Alternative syntax (MySQL)
SELECT * FROM users LIMIT 20, 10;  -- OFFSET 20, LIMIT 10

The math is simple: to get page N with page size S, use LIMIT S OFFSET (N-1) * S. Page 1 with 10 items per page: LIMIT 10 OFFSET 0. Page 2: LIMIT 10 OFFSET 10. Page 3: LIMIT 10 OFFSET 20.

Warning

OFFSET pagination gets slow on large tables because the database still has to scan and discard all the offset rows. For tables with millions of rows, consider "cursor pagination" using indexed columns instead.

Modifying data: INSERT, UPDATE, DELETE

While SELECT reads data, these three commands modify it. They don't return rows (unless you use RETURNING in PostgreSQL)-they change the database itself.

INSERT: Adding new rows

-- Insert a single row
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- Insert multiple rows at once
INSERT INTO users (name, email, age) VALUES
  ('Jane Smith', 'jane@example.com', 25),
  ('Bob Wilson', 'bob@example.com', 35),
  ('Alice Brown', 'alice@example.com', 28);

-- Insert and get back the inserted data (PostgreSQL only)
INSERT INTO users (name, email)
VALUES ('New User', 'new@example.com')
RETURNING *;

-- Insert data from another table
INSERT INTO archive_users (name, email)
SELECT name, email FROM users WHERE inactive = true;

UPDATE: Modifying existing rows

-- Update a single column
UPDATE users SET age = 31 WHERE id = 1;

-- Update multiple columns
UPDATE users SET
  name = 'John Updated',
  email = 'new@example.com',
  updated_at = NOW()
WHERE id = 1;

-- ⚠️ DANGER: Without WHERE, you update EVERY row!
UPDATE users SET country = 'France';  -- Don't do this!

-- Update and return modified rows (PostgreSQL)
UPDATE users SET age = 32 WHERE id = 1 RETURNING *;

CRITICAL WARNING

Always write your WHERE clause before writing the SET clause. A missing WHERE in an UPDATE or DELETE affects every row in the table. Many database professionals have horror stories about accidentally updating or deleting production data-don't let it happen to you!

DELETE: Removing rows

-- Delete a specific row
DELETE FROM users WHERE id = 1;

-- Delete with complex conditions
DELETE FROM users WHERE inactive = true AND created_at < '2023-01-01';

-- ⚠️ DANGER: Without WHERE, you delete EVERYTHING!
DELETE FROM users;  -- Deletes ALL rows!

-- Delete old records (keep database clean)
DELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';

Command	Purpose	Risk Level
`SELECT`	Read data	None
`INSERT`	Add data	Low
`UPDATE`	Modify data	High (missing WHERE = all rows)
`DELETE`	Remove data	Critical (missing WHERE = empty table)

Getting information about your database

Before querying, you often need to know what tables exist and what columns they have. Each database has its own commands for this:

-- View table structure
\d users           -- PostgreSQL
DESCRIBE users;    -- MySQL
.schema users      -- SQLite

-- List all tables
\dt                -- PostgreSQL
SHOW TABLES;       -- MySQL
.tables            -- SQLite

-- Add comments to your SQL
-- This is a single-line comment
/* This is a
   multi-line comment */

You've now mastered the foundational SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. operations. You can retrieve data with precision, filter it exactly how you want, sort it, paginate it, and modify it safely. But real power comes when you need to combine data from multiple tables-which is exactly what JOINs enable, and what you'll learn next.

Done

Complete & Next