SQL Basics

◆SQL◆Databases◆Beginner◆Beginner🎯Free Tools

Introduction

SQL (Structured Query Language) is the standard language for working with relational databases. Every backend developer needs to know SQL, even if they use an ORM — understanding what happens under the hood helps you write better queries, debug issues, and make informed decisions.

This guide covers the fundamental SQL operations you will use daily: SELECT for reading data, WHERE for filtering, ORDER BY for sorting, and LIMIT for pagination. Each concept includes multiple examples with real-world scenarios.

SELECT — Reading Data

The SELECT statement is the foundation of SQL. It retrieves data from one or more tables. You can select specific columns, all columns, or computed expressions.

select-basics.sql

SQL

1	-- Select all columns from a table
2	SELECT * FROM users;
3
4	-- Select specific columns
5	SELECT id, name, email FROM users;
6
7	-- Select with column aliases (rename columns in output)
8	SELECT
9	id AS user_id,
10	name AS full_name,
11	email AS contact_email
12	FROM users;
13
14	-- Select with computed columns
15	SELECT
16	name,
17	price,
18	price * 1.1 AS price_with_tax,
19	price - 5 AS discounted_price
20	FROM products;
21
22	-- Select with string concatenation
23	SELECT
24	first_name \|\| ' ' \|\| last_name AS full_name,
25	email
26	FROM employees;
27
28	-- Select with CASE expression (conditional logic)
29	SELECT
30	name,
31	price,
32	CASE
33	WHEN price < 10 THEN 'Budget'
34	WHEN price < 100 THEN 'Mid-range'
35	ELSE 'Premium'
36	END AS price_category
37	FROM products;

ℹ

info

Avoid using SELECT * in production code. Always specify the columns you need — this reduces network traffic, improves query performance, and makes your code more maintainable.

WHERE — Filtering Rows

The WHERE clause filters rows based on conditions. You can combine multiple conditions using AND, OR, and NOT. Understanding operator precedence is critical for writing correct queries.

where-filtering.sql

SQL

1	-- Basic WHERE with comparison operators
2	SELECT * FROM users WHERE age > 18;
3	SELECT * FROM users WHERE age >= 21;
4	SELECT * FROM users WHERE age < 65;
5	SELECT * FROM users WHERE age <= 30;
6	SELECT * FROM users WHERE status = 'active';
7	SELECT * FROM users WHERE status != 'banned';
8
9	-- Combining conditions with AND
10	SELECT * FROM products
11	WHERE category = 'electronics'
12	AND price < 500
13	AND in_stock = true;
14
15	-- Combining conditions with OR
16	SELECT * FROM products
17	WHERE category = 'electronics'
18	OR category = 'computers';
19
20	-- AND + OR (use parentheses for clarity)
21	SELECT * FROM products
22	WHERE (category = 'electronics' OR category = 'computers')
23	AND price < 500;
24
25	-- NOT operator
26	SELECT * FROM users WHERE NOT status = 'banned';
27	-- Same as: SELECT * FROM users WHERE status != 'banned';
28
29	-- Range with BETWEEN (inclusive)
30	SELECT * FROM products WHERE price BETWEEN 10 AND 50;
31	-- Same as: WHERE price >= 10 AND price <= 50
32
33	-- List of values with IN
34	SELECT * FROM users WHERE country IN ('USA', 'Canada', 'Mexico');
35
36	-- Pattern matching with LIKE
37	SELECT * FROM users WHERE email LIKE '%@gmail.com';
38	SELECT * FROM users WHERE name LIKE 'John%';
39	SELECT * FROM users WHERE name LIKE '%smith%';
40
41	-- Case-insensitive pattern matching (PostgreSQL)
42	SELECT * FROM users WHERE name ILIKE '%john%';

⚠

warning

Operator precedence matters: AND is evaluated before OR. Always use parentheses when mixing AND and OR to avoid unexpected results.

ORDER BY and LIMIT — Sorting and Pagination

ORDER BY sorts the result set, and LIMIT restricts the number of rows returned. Together, they are essential for pagination and displaying top-N results.

order-by-limit.sql

SQL

1	-- Sort by one column (ascending by default)
2	SELECT * FROM users ORDER BY name;
3	SELECT * FROM users ORDER BY name ASC;
4
5	-- Sort descending
6	SELECT * FROM products ORDER BY price DESC;
7
8	-- Sort by multiple columns
9	SELECT * FROM users ORDER BY country ASC, name ASC;
10
11	-- Sort by column position (not recommended)
12	SELECT * FROM users ORDER BY 2; -- Second column
13
14	-- Sort with NULL handling (PostgreSQL)
15	SELECT * FROM users ORDER BY last_login DESC NULLS LAST;
16	SELECT * FROM users ORDER BY last_login ASC NULLS FIRST;
17
18	-- LIMIT: Restrict number of rows
19	SELECT * FROM products ORDER BY price DESC LIMIT 10;
20
21	-- LIMIT with OFFSET: Skip rows (pagination)
22	SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20; -- Page 3
23
24	-- TOP-N query: Most expensive products
25	SELECT name, price
26	FROM products
27	ORDER BY price DESC
28	LIMIT 5;
29
30	-- Pagination with page numbers
31	-- Page 1: LIMIT 10 OFFSET 0
32	-- Page 2: LIMIT 10 OFFSET 10
33	-- Page 3: LIMIT 10 OFFSET 20
34	-- Page N: LIMIT 10 OFFSET (N-1) * 10

📝

note

Large OFFSET values can be slow on big tables. For deep pagination, consider using cursor-based pagination (WHERE id > last_id) instead of OFFSET.

DISTINCT and NULL Handling

DISTINCT removes duplicate rows from the result set. NULL represents missing or unknown data — it requires special handling because NULL is not equal to anything, including itself.

distinct-null.sql

SQL

1	-- DISTINCT: Remove duplicate rows
2	SELECT DISTINCT country FROM users;
3
4	-- DISTINCT with multiple columns
5	SELECT DISTINCT country, city FROM users;
6
7	-- COUNT DISTINCT: Count unique values
8	SELECT COUNT(DISTINCT country) AS unique_countries FROM users;
9
10	-- NULL handling: IS NULL and IS NOT NULL
11	SELECT * FROM users WHERE phone IS NULL;
12	SELECT * FROM users WHERE phone IS NOT NULL;
13
14	-- NULL is not equal to anything (including NULL)
15	SELECT * FROM users WHERE phone = NULL; -- WRONG! Returns no rows
16	SELECT * FROM users WHERE phone IS NULL; -- CORRECT
17
18	-- COALESCE: Replace NULL with a default value
19	SELECT
20	name,
21	COALESCE(phone, 'No phone') AS phone_number,
22	COALESCE(last_login, 'Never') AS last_seen
23	FROM users;
24
25	-- NULLIF: Return NULL if values are equal
26	SELECT NULLIF(0, 0); -- Returns NULL
27	SELECT NULLIF(1, 0); -- Returns 1
28
29	-- NULL-safe comparison (PostgreSQL)
30	SELECT * FROM users WHERE phone IS DISTINCT FROM '555-1234';
31	SELECT * FROM users WHERE phone IS NOT DISTINCT FROM '555-1234';
32
33	-- Count NULL vs non-NULL values
34	SELECT
35	COUNT(*) AS total,
36	COUNT(phone) AS with_phone,
37	COUNT(*) - COUNT(phone) AS without_phone
38	FROM users;

ℹ

info

COALESCE is your friend when working with NULLs. It returns the first non-NULL value from its arguments, making it perfect for providing default values.

Common Patterns

common-patterns.sql

SQL

1	-- Top 10 most recent orders
2	SELECT * FROM orders
3	ORDER BY created_at DESC
4	LIMIT 10;
5
6	-- Users who haven't logged in recently
7	SELECT * FROM users
8	WHERE last_login < NOW() - INTERVAL '30 days'
9	OR last_login IS NULL
10	ORDER BY last_login ASC NULLS FIRST;
11
12	-- Products in a price range with stock
13	SELECT * FROM products
14	WHERE price BETWEEN 20 AND 100
15	AND stock > 0
16	AND status = 'active'
17	ORDER BY price ASC;
18
19	-- Random sample (PostgreSQL)
20	SELECT * FROM users ORDER BY RANDOM() LIMIT 100;
21
22	-- Random sample (MySQL)
23	SELECT * FROM users ORDER BY RAND() LIMIT 100;
24
25	-- Case-insensitive search
26	SELECT * FROM products
27	WHERE LOWER(name) LIKE LOWER('%wireless%')
28	OR LOWER(description) LIKE LOWER('%bluetooth%');
29
30	-- Full-text search alternative (simple)
31	SELECT * FROM products
32	WHERE name ILIKE '%wireless%'
33	OR description ILIKE '%bluetooth%'
34	OR category ILIKE '%audio%';
35
36	-- Filter by date range
37	SELECT * FROM orders
38	WHERE created_at >= '2025-01-01'
39	AND created_at < '2025-02-01';
40
41	-- Filter by time of day
42	SELECT * FROM logs
43	WHERE created_at >= CURRENT_DATE
44	AND created_at < CURRENT_DATE + INTERVAL '1 day'
45	AND EXTRACT(HOUR FROM created_at) BETWEEN 9 AND 17;

Summary

Operation	Purpose	Example
SELECT	Choose columns to retrieve	SELECT name, email FROM users
WHERE	Filter rows by condition	WHERE age > 18 AND status = 'active'
ORDER BY	Sort result set	ORDER BY created_at DESC
LIMIT	Restrict number of rows	LIMIT 10 OFFSET 20
DISTINCT	Remove duplicates	SELECT DISTINCT country
IS NULL	Check for NULL values	WHERE phone IS NULL
COALESCE	Replace NULL with default	COALESCE(phone, 'N/A')

X GitHub YouTube Instagram Facebook Discord Slack Telegram Buy me a coffee Ko-fi Sponsor