Complete SQL Beginner's Guide

Master the five core operations: SELECT / INSERT / UPDATE / DELETE / JOIN

📅 April 6, 2026 · 8 min read · DatabaseSQLBackend Development

SQL (Structured Query Language) is the standard language for interacting with relational databases. Whether you're a frontend developer looking to understand the backend, or a backend engineer diving deeper into databases, SQL is an essential skill. This guide systematically covers the five core SQL operations — SELECT, INSERT, UPDATE, DELETE, and JOIN — helping you build a complete SQL knowledge base from scratch.

1. What Is SQL

SQL was born in the 1970s, developed by IBM researchers based on E.F. Codd's relational model theory. After more than 50 years of evolution, SQL has become the universal language of databases, supported by virtually every major relational database system (MySQL, PostgreSQL, SQL Server, Oracle, SQLite).

SQL's core value lies in its declarative approach — you tell the database "what you want," not "how to get it." You simply describe the desired result structure and conditions, and the database engine automatically selects the optimal execution path.

2. SELECT — Querying Data

SELECT is the most frequently used SQL statement, used to retrieve data from database tables. It forms the foundation of data analysis, report generation, and business logic.

2.1 Basic Queries

-- Query all columns
SELECT * FROM users;

-- Query specific columns
SELECT name, email, created_at FROM users;

-- Use aliases
SELECT name AS username, email AS mailbox FROM users;

2.2 WHERE Filtering

-- Exact match
SELECT * FROM users WHERE status = 'active';

-- Multiple conditions
SELECT * FROM orders
WHERE amount > 100 AND status = 'completed';

-- Pattern matching
SELECT * FROM products WHERE name LIKE '%phone%';

-- Range query
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';

2.3 Sorting and Pagination

-- Sort by price descending
SELECT * FROM products ORDER BY price DESC;

-- Paginated query (10 per page, page 2)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10;

2.4 Aggregate Functions and Grouping

-- Count orders by status
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status;

-- Total spending per user, only show users over 1000
SELECT user_id, SUM(amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING SUM(amount) > 1000;

3. INSERT — Adding Data

The INSERT statement adds new rows to a table. It's the first step in data persistence.

-- Insert a complete row
INSERT INTO users (name, email, age, status)
VALUES ('John Doe', 'john@example.com', 28, 'active');

-- Bulk insert
INSERT INTO products (name, price, stock) VALUES
('Product A', 99.9, 100),
('Product B', 199.9, 50),
('Product C', 299.9, 30);

-- Insert from query results
INSERT INTO archived_orders (user_id, amount, created_at)
SELECT user_id, amount, created_at
FROM orders
WHERE created_at < '2025-01-01';

4. UPDATE — Modifying Data

The UPDATE statement modifies existing data in a table. Use it carefully — forgetting the WHERE clause will update every row.

-- Update a single field
UPDATE users SET status = 'inactive' WHERE id = 42;

-- Update multiple fields
UPDATE products
SET price = 89.9, stock = stock - 1
WHERE id = 100;

-- Conditional batch update
UPDATE orders
SET status = 'expired'
WHERE status = 'pending'
  AND created_at < '2026-01-01';

5. DELETE — Removing Data

The DELETE statement removes rows from a table. As with UPDATE, the WHERE clause is crucial.

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

-- Conditional batch delete
DELETE FROM logs
WHERE created_at < '2025-01-01'
  AND level = 'debug';

-- Delete duplicates (keep the one with the smallest id)
DELETE FROM products
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id FROM products GROUP BY name
  ) tmp
);

DELETE vs TRUNCATE

6. JOIN — Combining Tables

JOIN is one of SQL's most powerful features, used to combine data from multiple related tables. Understanding JOINs is key to writing efficient queries.

6.1 INNER JOIN

Returns only matching rows from both tables. This is the most commonly used join type.

-- Query orders with user information
SELECT o.id, o.amount, u.name, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

6.2 LEFT JOIN

Returns all rows from the left table, filling NULL where the right table has no match. Useful for finding "missing associations."

-- Find users who have never placed an order
SELECT u.name, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

6.3 RIGHT JOIN

The reverse of LEFT JOIN — keeps all rows from the right table. Less commonly used in practice.

6.4 FULL OUTER JOIN

Returns all rows from both tables, filling NULL where there's no match. MySQL doesn't support this natively; use UNION to simulate it.

6.5 Multi-Table Joins

-- Orders + users + products (three-table join)
SELECT o.id AS order_id,
       u.name AS user_name,
       p.name AS product_name,
       o.amount
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 20;

7. SQL Execution Order

Understanding SQL execution order helps you write correct queries:

1. FROM — determine data sources (tables/joins)
2. WHERE — row-level filtering
3. GROUP BY — grouping
4. HAVING — group-level filtering
5. SELECT — column selection
6. DISTINCT — deduplication
7. ORDER BY — sorting
8. LIMIT/OFFSET — pagination

8. Performance Optimization Tips

• Use indexes wisely: create indexes on columns used in WHERE, JOIN, and ORDER BY clauses
• Avoid SELECT *: query only the columns you need to reduce data transfer and memory usage
• Use EXPLAIN: analyze query execution plans to find performance bottlenecks
• Limit result sets: always use LIMIT to avoid returning massive datasets
• Avoid functions on columns in WHERE: this can cause index misses