Guide to standard SQL for relational databases (RDB)
πΎ Standard SQL Complete Guide
Covers the common standard syntax of SQL (Structured Query Language), the heart of relational databases, and the basics of performance tuning. Learn the key practical knowledge, from DDL and DML through joins, subqueries, grouping, and improving query performance with indexes.
Beginner
Updated Oct 10, 2026
~13 min read
15 sections
15 code examples
πΎ
Table design (DDL)Data manipulation (DML)JOIN optimizationGrouping and aggregation
Understanding Standard SQL as a real-world workflow
Covers the common standard syntax of SQL (Structured Query Language), the heart of relational databases, and the basics of performance tuning. Learn the key practical knowledge, from DDL and DML through joins, subqueries, grouping, and improving query performance with indexes. Rather than listing concepts, this guide is organized so you can follow the content in the order you have to make decisions on a real project.
Key perspective
Databases
Alongside query syntax, look at schema design, indexes, transactions, permissions, and backups from an operations perspective.
Table design (DDL)Data manipulation (DML)JOIN optimizationGrouping and aggregation
Architecture diagrams
To make what you read stick, it helps to capture the flow as a picture first. The two diagrams below are reference maps you can keep coming back to while learning Standard SQL.
Learning flow
Rendering diagramβ¦
Architecture view
Rendering diagramβ¦
Overview of RDBMS and SQL
When you first open Standard SQL, the big picture matters more than individual commands. This section starts with the problems the concepts ahead were created to solve.
A relational database management system (RDBMS) manages data as two-dimensional tables made of rows and columns, and SQL is the standard language for controlling this data. Each RDB product (Oracle, MySQL, PostgreSQL, and so on) builds on standard SQL (ANSI SQL) and adds its own dialect details.
SQL category
Description
Typical commands
DDL (Data Definition Language)
Defines and changes data structures (tables, indexes, and so on)
CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language)
Inserts, queries, updates, and deletes data
SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language)
Database access privileges and security settings
GRANT, REVOKE
TCL (Transaction Control Language)
Controls transactions and guarantees consistency
COMMIT, ROLLBACK, SAVEPOINT
Table definitions & constraints (DDL)
Here you look at Table definitions & constraints (DDL) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
RDB design starts with correctly defining constraints such as the primary key (PK), foreign key (FK), Not Null, Unique, and Check to guarantee data integrity.
ddl_schema.sqlSQL
-- Create the departments tableCREATE TABLE departments ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) NOT NULL UNIQUE);-- Create the employees tableCREATE TABLE employees ( emp_id INT PRIMARY KEY, emp_name VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE, salary DECIMAL(10, 2) CHECK (salary > 0), dept_id INT, hire_date DATE DEFAULT CURRENT_DATE, CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id) ON DELETE SET NULL);
Querying & manipulating data (DML)
Here you look at Querying & manipulating data (DML) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
Covers basic CRUD operations along with sorting, filtering, and paging.
dml_examples.sqlSQL
-- Insert dataINSERT INTO departments (dept_id, dept_name) VALUES (10, 'Engineering');INSERT INTO employees (emp_id, emp_name, salary, dept_id) VALUES (1, 'John Doe', 75000.00, 10);-- Query data (filtering and sorting)SELECT emp_name, salaryFROM employeesWHERE salary >= 50000ORDER BY salary DESC;-- Update and delete dataUPDATE employees SET salary = salary * 1.1 WHERE emp_id = 1;DELETE FROM employees WHERE emp_id = 1;
Multi-table joins (JOIN)
Here you look at Multi-table joins (JOIN) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
A technique for combining related data stored across several tables into a single result set. You need a clear understanding of how the result set differs by type of join.
joins.sqlSQL
-- INNER JOIN: returns only rows that match in both tablesSELECT e.emp_name, d.dept_nameFROM employees eINNER JOIN departments d ON e.dept_id = d.dept_id;-- LEFT OUTER JOIN: returns all of the left table and the matching rows of the right (NULL where there is no match)SELECT e.emp_name, d.dept_nameFROM employees eLEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;
Grouping and aggregation (GROUP BY / HAVING)
Here you look at Grouping and aggregation (GROUP BY / HAVING) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
Groups a large number of rows by particular fields and applies aggregate functions such as sum, avg, and count. HAVING is used to filter the aggregated result groups by a condition.
aggregation.sqlSQL
-- Employee count and average salary per department (only departments with an average salary of 60000 or more)SELECT dept_id, COUNT(*) AS emp_count, AVG(salary) AS avg_salaryFROM employeesGROUP BY dept_idHAVING AVG(salary) >= 60000;
Here you look at DCL privilege management & security (GRANT / REVOKE) alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
DCL (GRANT, REVOKE) is used to control access security for database resources. Privileges are divided into system privileges, the rights to create/alter system resources, and object privileges, the rights to manipulate data in specific tables/views.
dcl_privileges.sqlSQL
-- 1. Create a user (common RDB concept)CREATE USER read_only_user IDENTIFIED BY 'secure_password';-- 2. Grant an object privilege (allow only reads on a specific table)GRANT SELECT ON employees TO read_only_user;-- 3. Create and grant a role that bundles several privilegesCREATE ROLE app_developer_role;GRANT SELECT, INSERT, UPDATE ON employees TO app_developer_role;GRANT SELECT, INSERT ON departments TO app_developer_role;GRANT app_developer_role TO read_only_user;-- 4. Revoke a privilegeREVOKE UPDATE ON employees FROM app_developer_role;
Guide to character sets and encoding
Here you look at Guide to character sets and encoding alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
The database character set determines the bit scheme (encoding) used to store and represent characters, and an encoding mismatch causes garbled data (mojibake), failed searches, and wasted storage. When designing a system, choose a character set that fits the use and map a collation (sorting rule) to it.
charset_check.sqlSQL
-- ββ Common queries for checking the database character set ββ-- [MySQL / MariaDB]SHOW VARIABLES LIKE 'character_set_database';SHOW VARIABLES LIKE 'collation_database';-- [Oracle]SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');-- [PostgreSQL]SELECT datname, pg_encoding_to_char(encoding), datcollate, datctype FROM pg_database WHERE datname = current_database();
Character set
Encoding scheme
1 Latin letter
1 Hangul (Korean) character
Emoji support
Characteristics and recommended use
ASCII
Single-byte (7-bit)
1 Byte
Not supported
β
Supports only English letters and symbols; ultra-light storage
EUC-KR
Multi-byte precomposed
1 Byte
2 Bytes
β
Precomposed Hangul only; legacy systems and where storage must be saved
MS949
Multi-byte extended precomposed
1 Byte
2 Bytes
β
The Korean Windows default; an extension covering the Hangul that EUC-KR lacks
UTF-8
Variable-byte (Unicode)
1 Byte
3 Bytes
β (partial)
The multilingual web standard worldwide; supports most characters
utf8mb4 (MySQL)
Variable-byte (Unicode)
1 Byte
3 Bytes
β (4 Bytes)
The global standard; full support for emoji and ancient scripts (recommended for MySQL)
UTF-16
Fixed/Variable (2-4 Bytes)
2 Bytes
2 Bytes
β (4 Bytes)
The standard internal string representation of Java and Windows; advantageous when storing large volumes of East Asian text
Basics of indexes and execution plans
Here you look at Basics of indexes and execution plans alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
An index is a lookup structure that dramatically improves search speed. Indiscriminate indexes, however, degrade write performance (INSERT, UPDATE), so they must be designed carefully on the basis of query patterns and execution plans.
index_explain.sqlSQL
-- Create an indexCREATE INDEX idx_emp_salary ON employees(salary);-- Check the execution plan (the EXPLAIN format differs between DBMS products)EXPLAINSELECT emp_name, salaryFROM employeesWHERE salary > 80000;
The database tuning process
Here you look at The database tuning process alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
Database tuning is not "fixing what seems slow". It is measuring to find the single biggest cause, fixing it, and measuring again. Adding indexes or changing settings on a guess either does nothing or slows down other queries.
There are broadly four layers to tune. The effect shrinks and the cost grows as you go down the list. Most performance problems are solved at queries and indexes; server settings and bigger hardware come after that.
Tuning processTEXT
1. Measure Find which queries use the most total time
(a query with a large "executions Γ average time" comes before one that is slow per run)
2. Reproduce Run that query on production-like data and read its execution plan
3. Diagnose Find the step that reads the most rows, and steps where estimated and actual row counts differ widely
4. Fix Change one thing at a time (consider in order: query β index β statistics β settings)
5. Verify Measure again under the same conditions and confirm the amount read and the time went down
6. Record Keep what was changed and the before/after numbers, and watch that other queries did not slow down
Layer
What changes
Examples
Effect
1. Queries Β· access patterns
SQL statements, number of calls, the range fetched
Removing N+1 calls, selecting only needed columns, changing how pagination works
Largest (tens to thousands of times)
2. Indexes Β· statistics
Adding Β· changing indexes, refreshing statistics
A composite index that matches the conditions and sort order
Large
3. Schema Β· data model
Table structure, partitions, summary tables
Separating old data, storing aggregates ahead of time
Large in the right situation
4. Server settings Β· hardware
Memory, connections, disks
Buffer cache size, sort memory
Helps only when it is the actual cause
Reading an execution plan
Here you look at Reading an execution plan alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
An execution plan describes the order and methods the database uses to process a query. SQL states only "what you want", so whether the table is read from start to finish or a few rows are read through an index is visible only in the plan.
There are three things to look at: how the table is accessed (full read or index), how joins are performed, and whether estimated and actual row counts are close. Below are real results from PostgreSQL for a query that finds one customer's 30 orders in a table of 300,000 orders.
Without an index (PostgreSQL 18)SQL
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)SELECT * FROM orders WHERE customer_id = 42;-- Gather (actual rows=30.00 loops=1)-- Workers Planned: 1-- Workers Launched: 1-- Buffers: shared hit=2239-- -> Parallel Seq Scan on orders (actual rows=15.00 loops=2)-- Filter: (customer_id = 42)-- Rows Removed by Filter: 149985 β read and discarded all 300,000 rows to find 30-- Buffers: shared hit=2239 β number of blocks read
After creating an indexSQL
CREATE INDEX ix_orders_customer ON orders (customer_id);EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)SELECT * FROM orders WHERE customer_id = 42;-- Bitmap Heap Scan on orders (actual rows=30.00 loops=1)-- Recheck Cond: (customer_id = 42)-- Heap Blocks: exact=30-- Buffers: shared hit=30 read=2 β 2,239 blocks β 32 blocks-- -> Bitmap Index Scan on ix_orders_customer (actual rows=30.00 loops=1)-- Index Cond: (customer_id = 42)
Comparing estimated and actual row countsSQL
EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = 'CANCELLED';-- Gather (cost=1000.00..5735.88 rows=2910 width=31) (actual time=0.173..37.578 rows=3000.00 loops=1)-- β estimated 2,910 β actual 3,000---- When the two numbers are close, the statistics are accurate.-- A difference of 10x or more means the optimizer planned with wrong information, so refresh the statistics first.
What the plan shows
Meaning
How to judge it
Full read (Seq Scan, Table scan, TABLE ACCESS FULL, COLLSCAN)
Reads every row of the table.
When it appears for a query that finds few rows, there is no index or the condition cannot use one. It is normal for a query that reads most of the rows.
Index read (Index Scan, Index lookup, INDEX RANGE SCAN, IXSCAN)
Finds positions through the index and reads only the rows needed.
The fewer rows sought, the better it does.
Index-only read (Index Only Scan, Covering index, PROJECTION_COVERED)
Answers from the index without going to the table.
The approach that reads the least.
Nested Loop join
For each row on one side, looks up the other side through an index.
Good when one side is small and the other side has an index. Very slow when both sides are large.
Hash join
Builds a hash table from one side and probes it with the other.
Good for joining many rows on equal values. Uses memory.
Merge join
Sorts both sides and matches them in order.
Used when the inputs are already sorted or for range joins.
Sort (Sort, filesort, SORT ORDER BY)
Sorts the result.
Spills to disk and slows down when it exceeds memory. See whether index order can replace it.
Rows filtered out (Rows Removed by Filter, filtered)
Rows read and then discarded because they did not match
When far larger than the rows found, the index did not narrow the condition enough.
Index design and conditions that cannot use an index
Here you look at Index design and conditions that cannot use an index alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
An index is a separate structure in which values are sorted. A phone book sorted by name finds a name immediately but has to be read from the start to find a phone number. Every rule about indexes follows from this "it is sorted" property.
The most common reason an existing index goes unused is that the column is transformed before it is compared. The index holds the original values of the column in sorted order, so a value produced by a function or a type conversion cannot be looked up in it.
A function on the column prevents index use (measured on PostgreSQL)SQL
-- Reads everything even though created_at is indexedSELECT count(*) FROM orders WHERE date_trunc('day', created_at) = '2026-03-01';-- Parallel Seq Scan on orders-- Rows Removed by Filter: 149280-- Buffers: shared hit=2239-- Leave the column alone and turn the compared value into a rangeSELECT count(*) FROM orders WHERE created_at >= '2026-03-01' AND created_at < '2026-03-02';-- Index Only Scan using ix_orders_created on orders (actual rows=1440.00)-- Buffers: shared hit=11 read=7 β 2,239 blocks β 18 blocks
Composite index: equality β sort β rangeSQL
-- "The 5 most recent orders of customer 42"CREATE INDEX ix_orders_cust_created ON orders (customer_id, created_at DESC);SELECT id, created_at FROM ordersWHERE customer_id = 42ORDER BY created_at DESCLIMIT 5;-- Limit (actual rows=5.00)-- -> Index Scan using ix_orders_cust_created on orders (actual rows=5.00)-- Index Cond: (customer_id = 42)-- Buffers: shared hit=5 read=3 β no sort step: reads 5 rows in index order and stops
Condition that cannot use an index
Why
How to fix it
WHERE DATE(created_at) = '2026-03-01'
A function is applied to the column.
created_at >= '2026-03-01' AND created_at < '2026-03-02'
WHERE amount * 1.1 > 1000
The column is used in a calculation.
WHERE amount > 1000 / 1.1
WHERE name = 42 (name is a character column)
The types differ, so the column side is converted.
WHERE name = '42' β match the type of the value to the column.
WHERE name LIKE '%son'
The leading part is not fixed, so the sort order cannot be used.
Fix the leading part, as in LIKE 'John%', or use full-text search.
WHERE a = 1 OR b = 2
One index cannot narrow both conditions together.
Put an index on each column or split the query with UNION ALL.
WHERE status <> 'DONE'
"Not equal" covers a wide range, so an index does not help.
Name the values you want, as in WHERE status IN ('PENDING', 'CANCELLED').
WHERE b = 1 on a composite index (a, b)
The second column alone cannot use the sort order.
Include a in the condition or create an index that starts with b.
A condition that matches most rows
The database decides that reading everything is faster.
This is normal. It is not a problem an index solves.
Slow query patterns and how to fix them
Here you look at Slow query patterns and how to fix them alongside real code. Rather than copying the example as-is, read it with an eye on the inputs, the outputs, and the parts most likely to change.
The causes of slow queries mostly come down to a handful of patterns. Before adding an index, check the query and the way it is called for the patterns below. Fixing the query has the largest effect and no side effects.
Deep pages: continue from a key instead of OFFSET (measured on PostgreSQL)SQL
-- OFFSET reads every skipped row and then throws it awaySELECT id FROM orders ORDER BY id LIMIT 20 OFFSET 200000;-- Index Only Scan using orders_pkey on orders (actual rows=200020.00)-- Buffers: shared hit=2042 β read 200,020 rows to return 20-- Read from just after the last key of the previous pageSELECT id FROM orders WHERE id > 200000 ORDER BY id LIMIT 20;-- Index Only Scan using orders_pkey on orders (actual rows=20.00)-- Index Cond: (id > 200000)-- Buffers: shared hit=7 β reads only 20 rows
N+1 calls: one join instead of repeated lookupsSQL
-- The application fetches 100 orders, then queries the customer separately for each order (101 queries)SELECT * FROM orders WHERE created_at >= '2026-07-25' LIMIT 100;SELECT * FROM customers WHERE id = ?; -- Γ 100-- Fetch it in one go (1 query)SELECT o.id, o.amount, c.nameFROM orders oJOIN customers c ON c.id = o.customer_idWHERE o.created_at >= '2026-07-25'LIMIT 100;
Pattern
Problem
How to fix it
N+1 calls
After fetching a list, a query is sent again per item. Each query is fast; the number of round trips is the problem.
Fetch in one go with a join or IN. Check the lazy loading settings of the ORM.
SELECT *
Unused columns are read and transferred, and a covering index cannot be used.
List only the columns you need.
Deep OFFSET
The further back the page, the more skipped rows are read.
Read from just after the last key seen (keyset pagination).
Function Β· calculation on a column
The index cannot be used.
Change the compared value instead (the section above).
Correlated subquery
The subquery may run again for every outer row.
Rewrite as a join or EXISTS and confirm with the execution plan.
COUNT(*) on a large table
Every matching row has to be counted.
Consider whether an exact value is really needed, and use a summary table or an approximation.
Unnecessary DISTINCT Β· ORDER BY
Sorting or deduplication uses memory and time.
Check that the result really needs it, and that duplicates do not come from a wrong join.
Unbounded queries
A wide condition fetches hundreds of thousands of rows.
Always put a LIMIT on screens and APIs.
Long IN lists
Listing thousands of values slows parsing and planning.
Load them into a temporary table and join, or run in batches.
Row-by-row INSERT in a loop
Every row costs a round trip and a commit.
Insert many rows in one statement or use the batch feature.
Standard SQL practical design
Standard SQL practical design is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.
The core of SQL design is the balance between normalization and denormalization, control of query complexity, and the design of database access control (DCL). On the principle of least privilege, the right to create schema resources and the right to access objects must be strictly separated.
Decision point
Question to ask
Practical guideline
Boundaries
Which parts of the Standard SQL code are most likely to change?
Separate input/output, configuration, external integrations, and the core rules.
State
Where is state created and where does it go away?
Make the owner and lifecycle of state visible in the code.
Failure
What does the caller receive on failure?
Decide the timeout, fallback, and error contract first.
Standard SQL operating standards
This section covers Standard SQL operating standards from a practical perspective. Instead of memorizing the concept, focus on the situations in which you would reach for it.
In production, monitor slow queries, optimize the connection pool, and check per-account privileges continuously. When writing DB management scripts, the best practice is to use a user session with tightly minimal privileges.
Standard SQL verification strategy
Standard SQL verification strategy is a point where the options diverge. Using the table to compare what each approach is for and how it differs in operation makes later decisions much easier.
Beyond judging whether an index scan is used through EXPLAIN, SQL verification should also include privilege tests confirming that the account the application connects with cannot run unnecessary DDL/DCL.
Quality axis
How to verify
Definition of done
Correctness
Automate the normal/failure cases.
The core scenarios pass reproducibly.
Regression prevention
When fixing a bug, leave the same case behind as a test.
The same failure is not deployed again.
Operability
Check the logs, metrics, and alerts.
There is a path for tracing the cause when a problem occurs.
Standard SQL: frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only the rows that match in both tables, and LEFT JOIN returns every row from the left table, filling in NULL where the right side has no match. If customers with no orders must appear in the list, use LEFT JOIN.
How do WHERE and HAVING differ?
WHERE filters rows before they are grouped, and HAVING filters the aggregated results after GROUP BY. To see only customers whose total exceeds an amount, use HAVING; to limit the query to orders in a period, use WHERE.
Why does an index speed up queries, and why should I not create many?
An index is a separate structure with sorted values, so the database finds the location of the rows it needs without reading the whole table. In exchange, every insert or update has to update the index too, which slows writes and uses more storage. Create only the ones that match the conditions and sort orders you actually use often.