SQL Fundamentals: CRUD, Joins & Execution Order Explained for Beginners

After leading teams for years, the author observes that beginners stumble on SQL because they lack clarity on a few core concepts: execution order and join logic. This article breaks down those essentials in plain language, drawing on two earlier posts for reference.

1. CRUD: The Four Basic Operations

SQL is simply a conversation with the database. The four keywords to remember:

INSERT – add new data (e.g., adding a new employee to the employee table).

DELETE – remove data (e.g., removing a departed employee). Caution: a mistake can drop the whole table.

UPDATE – modify existing data (e.g., changing an employee's phone number).

SELECT – query data (e.g., retrieving this month's sales figures).

SELECT * vs. Explicit Columns

Many tutorials start with SELECT * FROM table, but * is a wildcard meaning "all columns." Using it in a real task — such as sharing salary and grade data with another department — would expose sensitive fields like ID numbers, addresses, and bank accounts, creating both performance overhead and security risks. The correct practice: list only the needed columns, e.g., SELECT 姓名, 工资, 职级 FROM 员工表.

Aliases with AS

The AS keyword serves two purposes:

Column aliases – give computed columns readable names. Example: SELECT COUNT(员工ID) AS 部门人数 FROM 员工表 renames the result column to "部门人数" instead of the default "COUNT(员工ID)".

Table aliases – shorten table names in joins. Example:

FROM 员工表 AS e INNER JOIN 部门表 AS d ON e.部门ID = d.部门ID

lets you write e.姓名 and d.部门名称.

In many databases AS is optional; writing 表名 别名 works too. Beginners should learn the explicit form first.

2. Join Queries: Combining Data from Multiple Tables

Data often lives in separate tables. For instance, an employee table holds employee ID, name, and department ID, while a department table holds department ID and department details. To answer "which department each employee belongs to," you must join on the common key (department ID).

Join Types Explained with Analogies

INNER JOIN – "Exact match only." Returns rows where the join key exists in both tables. Employees without a department and departments without employees are excluded.

LEFT JOIN – "All rows from the left table." Every employee appears; if no matching department, department columns show NULL.

RIGHT JOIN – "All rows from the right table." Every department appears; if no matching employee, employee columns show NULL.

FULL JOIN – combines both sides (all employees and all departments). MySQL does not support it directly; emulate with LEFT JOIN UNION RIGHT JOIN.

General syntax template:

SELECT * FROM 表1 LEFT JOIN 表2 ON 表1.关联字段 = 表2.关联字段

The author likens a join to matching an employee's "department ID" introduction letter to the department table.

3. SQL Execution Order: The "Cooking Process" Analogy

The database does not execute clauses in the written order. The actual sequence:

FROM & JOIN (备菜) – choose tables and how to combine them.

WHERE (择菜) – filter out unwanted rows (e.g., only employees hired after 2026-01-01).

GROUP BY (分装) – group rows (e.g., by department name).

HAVING (挑盘子) – filter groups (e.g., keep only departments with more than 5 employees). Note: WHERE filters rows before grouping; HAVING filters after grouping.

SELECT (出锅) – finally pick the output columns.

ORDER BY (摆盘) – sort results (e.g., by employee count descending).

LIMIT (上菜) – restrict number of rows (e.g., top 3).

Example query and step-by-step walkthrough:

SELECT 部门名称, COUNT(员工ID) AS 员工人数
FROM 员工表
INNER JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID
WHERE 员工表.入职时间 > '2026-01-01'
GROUP BY 部门名称
HAVING COUNT(员工ID) > 5
ORDER BY 员工人数 DESC
LIMIT 3

Mapping to the cooking steps: 1. Join employee and department tables on department ID. 2. Keep only employees hired after 2026-01-01. 3. Group those employees by department name. 4. Discard groups with 5 or fewer employees. 5. Output department name and computed headcount. 6. Sort by headcount descending. 7. Return only the first three departments.

4. Why Learn SQL? Benefits and Beginner Tips

Efficiency – retrieve data yourself instead of waiting for technical colleagues.

Precise analysis – extract actionable insights from large datasets quickly.

Career advantage – SQL is now expected in roles like operations, marketing, and product.

Beginners should master basic CRUD and join syntax, understand execution order, and practice debugging. A critical safety habit: always run a SELECT first to confirm the target rows before executing DELETE or UPDATE.

Referenced earlier articles: "老唐唠 SQL：30个必知函数，新手从零到一直接上手!" "老唐唠SQL进阶：连表查询！多表数据一锅炖，新手也能秒上手"