Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment Β· evaluation Β· observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification Β· coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
πŸ’Ύ Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
πŸ€– AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
🧠 AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
🧠 AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
🌱 Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
🐳 DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
🧱 Infrastructure
Infrastructure Intro & RoadmapNginxRedis
☁️ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
🎨 Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
πŸ“± Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
βš™οΈ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
πŸ§ͺ Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

Β 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
Β© 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
  1. Home
  2. Learn
  3. Database
  4. Standard SQL
Guide to standard SQL for relational databases (RDB)

πŸ’Ύ Standard SQL Complete Guide

Visitors

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

Related frameworks & environments

πŸ”΄Oracleβ†’πŸ¬MySQLβ†’πŸ˜PostgreSQLβ†’πŸƒMongoDBβ†’

Contents

0 / 18
  1. How to use this guide
  2. Architecture diagrams
  3. Overview of RDBMS and SQL
  4. Table definitions & constraints (DDL)
  5. Querying & manipulating data (DML)
  6. Multi-table joins (JOIN)
  7. Grouping and aggregation (GROUP BY / HAVING)
  8. DCL privilege management & security (GRANT / REVOKE)
  9. Guide to character sets and encoding
  10. Basics of indexes and execution plans
  11. The database tuning process
  12. Reading an execution plan
  13. Index design and conditions that cannot use an index
  14. Slow query patterns and how to fix them
  15. Standard SQL design
  16. Operating standards
  17. Verification strategy
  18. Frequently asked questions
Contents 18 sections
  1. How to use this guide
  2. Architecture diagrams
  3. Overview of RDBMS and SQL
  4. Table definitions & constraints (DDL)
  5. Querying & manipulating data (DML)
  6. Multi-table joins (JOIN)
  7. Grouping and aggregation (GROUP BY / HAVING)
  8. DCL privilege management & security (GRANT / REVOKE)
  9. Guide to character sets and encoding
  10. Basics of indexes and execution plans
  11. The database tuning process
  12. Reading an execution plan
  13. Index design and conditions that cannot use an index
  14. Slow query patterns and how to fix them
  15. Standard SQL design
  16. Operating standards
  17. Verification strategy
  18. Frequently asked questions

How to use this guide

How to read it

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 categoryDescriptionTypical 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 dataSELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language)Database access privileges and security settingsGRANT, REVOKE
TCL (Transaction Control Language)Controls transactions and guarantees consistencyCOMMIT, 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 table
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);

-- Create the employees table
CREATE 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 data
INSERT 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, salary
FROM employees
WHERE salary >= 50000
ORDER BY salary DESC;

-- Update and delete data
UPDATE 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 tables
SELECT e.emp_name, d.dept_name
FROM employees e
INNER 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_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.dept_id;

Tip

  • Create indexes on join columns wherever possible to avoid a full table scan.
  • With an outer join, the result can differ completely depending on where the condition goes (ON vs WHERE), so take care.

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_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) >= 60000;

DCL privilege management & security (GRANT / REVOKE)

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 privileges
CREATE 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 privilege
REVOKE UPDATE ON employees FROM app_developer_role;

Tip

  • Following the Principle of Least Privilege, grant the account the application connects with only the minimum object privileges it needs.
  • Rather than granting privileges directly to individual user accounts, it is easier to manage if you create roles per job function, map privileges to the roles, and then grant the roles to users.

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 setEncoding scheme1 Latin letter1 Hangul (Korean) characterEmoji supportCharacteristics and recommended use
ASCIISingle-byte (7-bit)1 ByteNot supported❌Supports only English letters and symbols; ultra-light storage
EUC-KRMulti-byte precomposed1 Byte2 Bytes❌Precomposed Hangul only; legacy systems and where storage must be saved
MS949Multi-byte extended precomposed1 Byte2 Bytes❌The Korean Windows default; an extension covering the Hangul that EUC-KR lacks
UTF-8Variable-byte (Unicode)1 Byte3 Bytes❌ (partial)The multilingual web standard worldwide; supports most characters
utf8mb4 (MySQL)Variable-byte (Unicode)1 Byte3 Bytes⭐ (4 Bytes)The global standard; full support for emoji and ancient scripts (recommended for MySQL)
UTF-16Fixed/Variable (2-4 Bytes)2 Bytes2 Bytes⭐ (4 Bytes)The standard internal string representation of Java and Windows; advantageous when storing large volumes of East Asian text

Tip

  • When character set settings do not match, non-ASCII text gets garbled during data migration (mojibake), and differences in byte size per character can cause string truncation errors (ORA-12899 and the like).
  • A collation defines the rules for comparing characters. Design with an understanding of the difference between ci (case-insensitive) and bin (binary), a pure byte comparison, to prevent search errors.
  • The client encoding of the web application that connects to the DB must also match the database character set, to prevent the data corruption that can occur during automatic encoding conversion.

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 index
CREATE INDEX idx_emp_salary ON employees(salary);

-- Check the execution plan (the EXPLAIN format differs between DBMS products)
EXPLAIN
SELECT emp_name, salary
FROM employees
WHERE salary > 80000;

Tip

  • For the primary key (PK), the database creates a unique index automatically.
  • When creating a composite index, putting columns with high cardinality (few duplicates, many unique values) first in the column order is better for performance.

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
LayerWhat changesExamplesEffect
1. Queries Β· access patternsSQL statements, number of calls, the range fetchedRemoving N+1 calls, selecting only needed columns, changing how pagination worksLargest (tens to thousands of times)
2. Indexes Β· statisticsAdding Β· changing indexes, refreshing statisticsA composite index that matches the conditions and sort orderLarge
3. Schema Β· data modelTable structure, partitions, summary tablesSeparating old data, storing aggregates ahead of timeLarge in the right situation
4. Server settings Β· hardwareMemory, connections, disksBuffer cache size, sort memoryHelps only when it is the actual cause

Tip

  • Every query is fast on a development database with a few thousand rows. Plans depend on data volume and distribution, so check them on data of production-like size.
  • Look at the amount read (rows, blocks), not only the time (ms). Time swings with cache state and server load, but the amount read is constant under the same conditions.
  • An index speeds up reads at the price of slower inserts, updates, and deletes, and it uses storage. Remember that each one you add raises the cost of writes.
  • The per-database tools are in each guide: Oracle (DBMS_XPLAN, hints), MySQL (EXPLAIN ANALYZE, optimizer hints), PostgreSQL (EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements), MongoDB (explain, hint).

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 showsMeaningHow 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 joinFor 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 joinBuilds a hash table from one side and probes it with the other.Good for joining many rows on equal values. Uses memory.
Merge joinSorts 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 matchWhen far larger than the rows found, the index did not narrow the condition enough.

Tip

  • Read a plan from the inside (the most indented lines) outward. The innermost step runs first.
  • A plan with ANALYZE actually runs the query. On INSERT Β· UPDATE Β· DELETE it changes data, so run it inside a transaction and roll back.
  • The cost shown in a plan is a relative unit internal to the database, not time. Use it only to compare two plans for the same query.

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 indexed
SELECT 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 range
SELECT 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 orders
WHERE  customer_id = 42
ORDER  BY created_at DESC
LIMIT  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 indexWhyHow 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 > 1000The 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 = 2One 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 rowsThe database decides that reading everything is faster.This is normal. It is not a problem an index solves.

Tip

  • The starting point for column order in a composite index is "equality (=) columns β†’ sort columns β†’ range columns".
  • With an index on (a, b), an index on (a) alone is mostly unnecessary. Overlapping indexes only slow down writes.
  • When every selected column is in the index, the table is not visited (a covering index). It pays off on frequently used list queries.
  • An index on a column with few distinct values (gender, status) alone is rarely used. Combine it with other columns, or consider a partial index that covers only the rare values.
  • Find and drop unused indexes periodically. Each database has a view that shows how often an index is used.

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 away
SELECT 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 page
SELECT 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.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id
WHERE  o.created_at >= '2026-07-25'
LIMIT  100;
PatternProblemHow to fix it
N+1 callsAfter 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 OFFSETThe further back the page, the more skipped rows are read.Read from just after the last key seen (keyset pagination).
Function Β· calculation on a columnThe index cannot be used.Change the compared value instead (the section above).
Correlated subqueryThe subquery may run again for every outer row.Rewrite as a join or EXISTS and confirm with the execution plan.
COUNT(*) on a large tableEvery 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 BYSorting or deduplication uses memory and time.Check that the result really needs it, and that duplicates do not come from a wrong join.
Unbounded queriesA wide condition fetches hundreds of thousands of rows.Always put a LIMIT on screens and APIs.
Long IN listsListing 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 loopEvery row costs a round trip and a commit.Insert many rows in one statement or use the batch feature.

Tip

  • After a fix, always check the execution plan and the amount read again. Confirm with numbers, not with "it seems faster".
  • Keyset pagination cannot "jump straight to page N", but it is equally fast at any depth. It suits infinite scroll and next Β· previous navigation.
  • Fixing the few most frequently executed queries affects overall performance more than perfecting a single query.

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 pointQuestion to askPractical guideline
BoundariesWhich parts of the Standard SQL code are most likely to change?Separate input/output, configuration, external integrations, and the core rules.
StateWhere is state created and where does it go away?Make the owner and lifecycle of state visible in the code.
FailureWhat 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.

Tip

  • Verify the type/Extra columns of the EXPLAIN plan
  • Check for lock contention and deadlock potential
  • Verify minimal privileges on the application account
  • Security audit of the DCL (GRANT/REVOKE) history

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 axisHow to verifyDefinition of done
CorrectnessAutomate the normal/failure cases.The core scenarios pass reproducibly.
Regression preventionWhen fixing a bug, leave the same case behind as a test.The same failure is not deployed again.
OperabilityCheck 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.

← Previous guideDB Intro & RoadmapNext guide β†’Oracle