SQL Syntax Complete Guide: SELECT, JOIN, WHERE Query Commands Tutorial [2025]

Introduction: The Importance of Mastering SQL Syntax

Have you ever wondered why almost every data analyst's resume mentions "proficient in SQL"?

The answer is simple: SQL is the universal language for communicating with databases. Whether you're using MySQL, PostgreSQL, or SQL Server, the core syntax is interchangeable.

This article will systematically introduce every aspect of SQL query syntax. From the most basic SELECT, to WHERE condition filtering, JOIN multi-table connections, and GROUP BY grouping statistics. After reading this, you'll be able to write SQL queries covering 90% of daily requirements.

If you're a complete beginner, we recommend first reading SQL Complete Guide: From Beginner to Expert to understand basic concepts.

SQL Query Syntax Overview

Basic Structure of SQL Statements

A complete SQL query statement typically contains the following clauses:

SELECT columns          -- 1. Select columns to display
FROM table              -- 2. Specify data source
WHERE condition         -- 3. Filter data (optional)
GROUP BY grouping_column -- 4. Group statistics (optional)
HAVING group_condition  -- 5. Filter group results (optional)
ORDER BY sort_column    -- 6. Sort results (optional)
LIMIT count             -- 7. Limit returned rows (optional)

SQL Execution Order

This is where many people get confused: SQL's writing order and execution order are different.

Execution Order (Important!):

1. FROM → Determine data source
2. WHERE → Filter individual data rows
3. GROUP BY → Perform grouping
4. HAVING → Filter group results
5. SELECT → Select columns to display
6. ORDER BY → Sort
7. LIMIT → Limit rows

Understanding execution order helps you avoid many errors. For example, you cannot use aliases defined in SELECT within WHERE, because WHERE executes before SELECT.

SQL Syntax Categories

SQL syntax is divided into four major categories by function:

Category	Full Name	Common Commands	Purpose
DQL	Data Query Language	SELECT	Query data
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	Manipulate data
DDL	Data Definition Language	CREATE, ALTER, DROP	Define structure
DCL	Data Control Language	GRANT, REVOKE	Permission control

This article mainly focuses on DQL (Query Language), which is also the most frequently used part in daily work.

For DML operations, refer to SQL CRUD Operations Complete Guide.

---

SELECT Query Syntax Complete Analysis

Basic SELECT Structure

SELECT is SQL's most core command, used to retrieve data from tables.

The simplest query:

-- Query all columns
SELECT * FROM employees;

-- Query specific columns
SELECT name, department, salary FROM employees;

Important reminder: Avoid using SELECT * in production environments for three reasons:

1. Poorer performance (reads unnecessary columns)
2. Column order may change
3. Lower code readability

Column Aliases with AS

Use AS to set aliases for columns or tables, making results more readable.

-- Column aliases
SELECT
    name AS employee_name,
    salary AS monthly_salary,
    salary * 12 AS annual_salary
FROM employees;

-- AS can be omitted (but recommended for readability)
SELECT name employee_name, salary monthly_salary FROM employees;

DISTINCT for Removing Duplicates

DISTINCT is used to remove duplicate data rows.

-- Query all unique departments
SELECT DISTINCT department FROM employees;

-- Multi-column deduplication (unique combinations)
SELECT DISTINCT department, job_title FROM employees;

Note: DISTINCT deduplicates based on all selected columns, not just a single column.

Calculated Columns and Operations

SELECT can perform mathematical operations and string processing.

-- Mathematical operations
SELECT
    name,
    salary,
    salary * 1.05 AS adjusted_salary,
    salary * 14 AS annual_with_bonus
FROM employees;

-- String concatenation (SQL Server)
SELECT
    name + ' - ' + department AS employee_info
FROM employees;

-- String concatenation (MySQL)
SELECT
    CONCAT(name, ' - ', department) AS employee_info
FROM employees;

---

WHERE Condition Filtering Techniques

Comparison Operators

WHERE clause is used to filter data rows that meet conditions.

Operator	Description	Example
=	Equal to	WHERE status = 'active'
<> or !=	Not equal to	WHERE status <> 'inactive'
>	Greater than	WHERE salary > 50000
<	Less than	WHERE age < 30
>=	Greater than or equal	WHERE score >= 60
<=	Less than or equal	WHERE price <= 1000

-- Query employees with salary over 60000
SELECT name, salary
FROM employees
WHERE salary > 60000;

-- Query employees not in marketing department
SELECT name, department
FROM employees
WHERE department <> 'Marketing';

LIKE Pattern Matching

LIKE is used for string pattern matching with wildcards.

Wildcard	Description	Example
%	Any number of characters	'John%' matches "John Smith", "Johnson"
_	Single character	'J_n' matches "Jon" but not "John"

-- Query employees whose name starts with 'J'
SELECT * FROM employees WHERE name LIKE 'J%';

-- Query employees whose name has 'o' as second character
SELECT * FROM employees WHERE name LIKE '_o%';

-- Query employees with gmail email
SELECT * FROM employees WHERE email LIKE '%@gmail.com';

-- Query employees with 'Engineer' in job title
SELECT * FROM employees WHERE job_title LIKE '%Engineer%';

IN and NOT IN List Queries

IN is used to match a set of values, more concise than multiple ORs.

-- Query customers in New York, Los Angeles, or Chicago
SELECT * FROM customers
WHERE city IN ('New York', 'Los Angeles', 'Chicago');

-- Equivalent to
SELECT * FROM customers
WHERE city = 'New York' OR city = 'Los Angeles' OR city = 'Chicago';

-- Exclude specific departments
SELECT * FROM employees
WHERE department NOT IN ('HR', 'Finance');

BETWEEN Range Queries

BETWEEN is used for range queries, including start and end values.

-- Query employees with salary between 40000 and 60000
SELECT name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 60000;

-- Equivalent to
SELECT name, salary
FROM employees
WHERE salary >= 40000 AND salary <= 60000;

-- Date range query
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

IS NULL Handling Null Values

NULL represents "no value" and cannot be compared with =.

-- Query employees without email filled in
SELECT * FROM employees WHERE email IS NULL;

-- Query employees with email filled in
SELECT * FROM employees WHERE email IS NOT NULL;

-- Incorrect syntax (will never return results)
SELECT * FROM employees WHERE email = NULL;  -- ❌

Important concept: NULL is not equal to empty string '', nor is it equal to 0. NULL represents "unknown" or "does not exist."

Logical Operators AND, OR, NOT

Use logical operators when combining multiple conditions.

-- AND: Both conditions must be true
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 70000;

-- OR: At least one condition must be true
SELECT * FROM employees
WHERE department = 'Engineering' OR department = 'Product';

-- NOT: Reverse the condition
SELECT * FROM employees
WHERE NOT (salary < 50000);

-- Complex conditions (note parentheses precedence)
SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'Product')
  AND salary > 60000;

Need Systematic SQL Learning?

According to surveys, people who learn SQL systematically save an average of 60% learning time compared to those who learn by trial and error, and are less likely to miss important concepts.

Free SQL Syntax Quick Reference

We've compiled a complete SQL syntax quick reference PDF including:

• Complete SELECT syntax structure
• All comparison operators with examples
• JOIN type diagrams
• Common functions list
• Performance optimization tips

This quick reference can be printed and placed on your desk for easy reference.

CloudInsight SQL Training Services

If your team needs systematic SQL training, CloudInsight offers:

• Customized courses: Course content designed based on team level
• Practice-oriented: Practice using real data
• Post-course support: 30-day online Q&A service

👉 Schedule Free Consultation for Enterprise Training Solutions

---

Aggregate Functions and GROUP BY

Five Major Aggregate Functions

Aggregate functions perform calculations on a group of data and return a single result.

Function	Purpose	Example	Notes
COUNT()	Count rows	COUNT(*)	COUNT(*) includes NULL, COUNT(column) doesn't
SUM()	Sum total	SUM(salary)	Only for numeric columns
AVG()	Average value	AVG(score)	Automatically ignores NULL
MAX()	Maximum value	MAX(price)	Works with strings (alphabetical order)
MIN()	Minimum value	MIN(age)	Works with dates

-- Count total employees
SELECT COUNT(*) AS total_employees FROM employees;

-- Calculate average salary
SELECT AVG(salary) AS average_salary FROM employees;

-- Multiple aggregate functions together
SELECT
    COUNT(*) AS employee_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS average_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

GROUP BY Grouping Statistics

GROUP BY groups data by specified columns, then performs aggregate calculations on each group.

-- Calculate employee count and average salary per department
SELECT
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS average_salary
FROM employees
GROUP BY department;

-- Multi-column grouping
SELECT
    department,
    job_title,
    COUNT(*) AS count
FROM employees
GROUP BY department, job_title;

Important rule: Non-aggregate columns in SELECT must appear in GROUP BY.

-- ❌ Error: name not in GROUP BY
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

-- ✅ Correct
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

HAVING Group Conditions

HAVING is used to filter grouped results and cannot be replaced with WHERE.

-- Find departments with more than 5 employees
SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Find departments with average salary over 60000
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

WHERE vs HAVING Differences

This is a common interview question, make sure you understand it clearly.

Comparison	WHERE	HAVING
Execution timing	Before GROUP BY	After GROUP BY
Filter target	Individual data rows	Group results
Can use aggregate functions	No	Yes

-- First use WHERE to filter data, then group statistics
SELECT department, AVG(salary) AS average_salary
FROM employees
WHERE hire_date >= '2020-01-01'  -- Filter employees hired after 2020
GROUP BY department
HAVING AVG(salary) > 50000;      -- Filter departments with avg salary over 50000

---

Sorting and Pagination

ORDER BY Sorting

ORDER BY is used to sort results.

-- Sort by salary from high to low
SELECT name, salary
FROM employees
ORDER BY salary DESC;

-- Sort by salary from low to high (ASC is default, can be omitted)
SELECT name, salary
FROM employees
ORDER BY salary ASC;

-- Multi-column sorting
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
-- First sort by department ascending, then by salary descending within each department

Sort keywords:

• ASC: Ascending, from small to large, default value
• DESC: Descending, from large to small

LIMIT and OFFSET Pagination

LIMIT restricts the number of returned data rows, commonly used for pagination.

-- Get first 10 rows (MySQL, PostgreSQL, SQLite)
SELECT * FROM employees LIMIT 10;

-- Get rows 11-20 (skip first 10)
SELECT * FROM employees LIMIT 10 OFFSET 10;

-- Shorthand syntax (MySQL)
SELECT * FROM employees LIMIT 10, 10;  -- LIMIT offset, count

SQL Server pagination syntax is different:

-- SQL Server 2012+
SELECT * FROM employees
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Older SQL Server versions
SELECT TOP 10 * FROM employees;

Pagination Query Example

Pagination in practice is typically implemented like this:

-- Assuming 20 rows per page, query page 3
-- Page 3 = skip first 40 rows ((3-1) * 20)

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

---

JOIN Multi-Table Connections Overview

When data is distributed across multiple tables, you need JOIN to combine them.

JOIN Types Overview

JOIN Type	Description	Use Case
INNER JOIN	Returns only data with matches on both sides	Most common, only related data
LEFT JOIN	Returns all data from left table	Keep all left table data even if right table has no match
RIGHT JOIN	Returns all data from right table	Less commonly used, can rewrite with LEFT JOIN
FULL OUTER JOIN	Returns all data from both sides	Need complete union of both tables
CROSS JOIN	Cartesian product	Need all combinations (rarely used)

INNER JOIN Basic Example

-- Query orders and corresponding customer names
SELECT
    orders.order_id,
    orders.order_date,
    customers.name AS customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- Using table aliases for simplification
SELECT
    o.order_id,
    o.order_date,
    c.name AS customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN Example

-- Query all customers and their orders (including customers without orders)
SELECT
    c.name,
    o.order_id,
    o.total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Customers without orders will have NULL for order_id and total_amount.

JOIN is a core skill for advanced SQL queries. For complete illustrated tutorial, refer to SQL JOIN Complete Tutorial Guide.

Practical Examples and Exercises

Sample Database Structure

Assume we have the following three tables:

employees (Employee Table)

Column	Type	Description
id	INT	Employee ID
name	VARCHAR	Name
department_id	INT	Department ID
salary	DECIMAL	Salary
hire_date	DATE	Hire date

departments (Department Table)

Column	Type	Description
id	INT	Department ID
name	VARCHAR	Department name
manager_id	INT	Manager ID

orders (Order Table)

Column	Type	Description
id	INT	Order ID
employee_id	INT	Responsible employee
amount	DECIMAL	Order amount
order_date	DATE	Order date

Practice Problems and Solutions

Problem 1: Query the names and salaries of the top 5 highest-paid employees

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Problem 2: Query employee count and average salary per department

SELECT
    d.name AS department_name,
    COUNT(e.id) AS employee_count,
    ROUND(AVG(e.salary), 0) AS average_salary
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.id, d.name;

Problem 3: Find employees with 2024 order amounts exceeding 1 million

SELECT
    e.name,
    SUM(o.amount) AS total_order_amount
FROM employees e
INNER JOIN orders o ON e.id = o.employee_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY e.id, e.name
HAVING SUM(o.amount) > 1000000
ORDER BY total_order_amount DESC;

Problem 4: Query employees without any orders

SELECT e.name
FROM employees e
LEFT JOIN orders o ON e.id = o.employee_id
WHERE o.id IS NULL;

---

SQL Syntax Quick Reference

SELECT Syntax Structure

SELECT [DISTINCT] column1, column2, ...
FROM table
[JOIN other_table ON condition]
[WHERE filter_condition]
[GROUP BY grouping_column]
[HAVING group_condition]
[ORDER BY sort_column [ASC|DESC]]
[LIMIT count [OFFSET offset]]

Common Operators Quick Reference

Type	Operators
Comparison	=, <>, >, <, >=, <=
Range	BETWEEN...AND
List	IN, NOT IN
Pattern	LIKE (%, _)
Null	IS NULL, IS NOT NULL
Logical	AND, OR, NOT

Aggregate Functions Quick Reference

Function	Purpose
COUNT(*)	Count all rows
COUNT(column)	Count non-NULL rows
SUM(column)	Numeric sum
AVG(column)	Numeric average
MAX(column)	Maximum value
MIN(column)	Minimum value

For more SQL functions, refer to SQL Functions Complete Guide.

---

FAQ

Q1: Can SQL SELECT select all columns?

Yes, using SELECT * will select all columns in the table. However, this is not recommended in production environments because: (1) Poorer performance, reads unnecessary data; (2) If table structure changes, it may cause program errors; (3) Lower code readability. Recommend explicitly listing the columns you need.

Q2: What's the difference between WHERE and HAVING?

WHERE executes before GROUP BY, used to filter individual data rows, and cannot use aggregate functions. HAVING executes after GROUP BY, used to filter grouped results, and can use aggregate functions. For example: WHERE salary > 50000 (filter individual salary) vs HAVING AVG(salary) > 50000 (filter department average salary).

Q3: Must SQL GROUP BY be used with aggregate functions?

Not necessarily, but GROUP BY is usually paired with aggregate functions to be meaningful. If you just want to remove duplicate data, you can use DISTINCT. The main purpose of GROUP BY is to group data for statistical calculations. Without aggregate functions, each group only returns one row, and results may not be as expected.

---

Further Reading

After mastering basic syntax, recommend continuing with these topics:

Basic to Advanced:

• SQL JOIN Complete Tutorial: INNER, LEFT, RIGHT JOIN Illustrated
• SQL CRUD Operations Guide: INSERT, UPDATE, DELETE Syntax
• SQL Functions Complete Guide: String, Date, Aggregate Functions Summary

Advanced Techniques:

• SQL Subquery Complete Tutorial: Subquery Syntax and Applications
• SQL Window Functions Tutorial: ROW_NUMBER, RANK Practical Applications
• SQL Performance Tuning Guide: Query Optimization and Index Strategy

Practical Applications:

• SQL Server Complete Guide: Installation, Configuration, Management
• SQL Advanced Techniques: Stored Procedures, Triggers, Transaction Control

---

Improve Team SQL Skills

A proficient SQL data analyst is 5-10 times more productive than a beginner. Investing in team SQL training is one of the most effective ways to improve overall productivity.

CloudInsight SQL Enterprise Training

• Beginner course: Complete tutorial on SELECT, WHERE, JOIN, GROUP BY
• Advanced course: Subqueries, window functions, performance optimization
• Practical workshop: Practice using real business data
• Post-course support: 30-day online Q&A support

Training Results Guarantee

• Able to independently write 80% of daily queries immediately after completion
• Course recordings provided for repeated review
• Completion test pass rate over 95%

👉 Schedule Free Consultation for Team SQL Training Solutions

---

References

1. Microsoft, "Transact-SQL Reference" (2024)
2. MySQL Documentation, "SELECT Statement" (2024)
3. PostgreSQL Documentation, "SQL Commands" (2024)
4. W3Schools, "SQL Tutorial" (2024)
5. SQLZoo, "SQL Teaching" (2024)