SQL Reference Guide — Developer Sandbox

SELECT structure, aliases, sorting, pagination, data types, and query-writing order.

Panel 1 / 6

01 — Core Querying

Core Querying

SELECT Anatomy

SELECT column1, column2

Return selected columns.

SELECT *

Return every column; useful for inspection, usually avoid in production APIs.

FROM table_name

Choose the source table or relation.

SELECT DISTINCT column

Remove duplicate result rows for the selected expression set.

AS alias

Rename an output column or table reference for the query.

SELECT expression

Return calculations, literals, function results, or CASE expressions.

Core Querying

Aliases & Qualification

users AS u

Give a table a shorter query alias.

u.id

Qualify a column with its table alias; important in joins.

total * tax_rate AS tax

Alias a calculated output column.

"Order" / `Order`

Quoted identifiers are dialect-specific; avoid reserved words for object names.

Core Querying

Sorting & Pagination

ORDER BY created_at ASC

Sort from lowest/oldest to highest/newest.

ORDER BY created_at DESC

Sort from highest/newest to lowest/oldest.

ORDER BY last_name, first_name

Sort by multiple keys in sequence.

LIMIT 25

Return at most 25 rows in PostgreSQL, MySQL, and SQLite.

OFFSET 50

Skip rows before returning the next page; large offsets can become expensive.

FETCH FIRST 25 ROWS ONLY

Standard-style row limiting supported by some systems.

Core Querying

Common Data Types

INTEGER / INT

Whole numbers.

BIGINT

Large whole numbers and large generated identifiers.

DECIMAL(p,s) / NUMERIC(p,s)

Exact fixed-precision values; preferred for money-like calculations.

REAL / FLOAT / DOUBLE

Approximate floating-point numeric values.

CHAR(n) / VARCHAR(n) / TEXT

Character data; exact behavior and limits vary by database.

BOOLEAN

True/false values; storage details vary by database.

DATE

Calendar date without a time.

TIME

Time of day.

TIMESTAMP

Date and time; timezone behavior depends on type and database.

BLOB / BYTEA / BINARY

Binary data; names differ across engines.

JSON / JSONB

Structured JSON where supported; PostgreSQL distinguishes JSON and JSONB.

Core Querying

Written vs Logical Query Order

Written

SELECT → FROM/JOIN → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT/FETCH.

Logical processing

FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT.

Why it matters

A SELECT alias often cannot be referenced in WHERE because WHERE is logically evaluated earlier.

WHERE predicates, NULL handling, CASE expressions, pattern matching, and common scalar functions.

Panel 2 / 6

02 — Filtering, Expressions & Functions

Filtering, Expressions & Functions

Comparison & Boolean Logic

=

Equal.

<>

Standard SQL not-equal operator.

!=

Common not-equal synonym; support is widespread but <> is standard.

> < >= <=

Numeric, textual, or date comparisons when types are comparable.

AND

Require both predicates to be true.

OR

Require either predicate to be true.

NOT

Negate a predicate.

( ... )

Use parentheses to make mixed AND/OR logic explicit.

Filtering, Expressions & Functions

WHERE Patterns

WHERE status = 'active'

Filter rows by an exact value.

WHERE price BETWEEN 10 AND 50

Inclusive range test.

WHERE id IN (1, 2, 3)

Match any value in a list or subquery.

WHERE id NOT IN (...)

Exclude listed values; NULLs in the list can create surprising results.

WHERE EXISTS (subquery)

True when the correlated or uncorrelated subquery returns a row.

WHERE NOT EXISTS (subquery)

True when the subquery returns no rows.

Filtering, Expressions & Functions

Pattern Matching

LIKE 'Ada%'

% matches zero or more characters.

LIKE '_at'

_ matches exactly one character.

NOT LIKE pattern

Exclude matches.

ILIKE pattern

PostgreSQL case-insensitive LIKE extension.

LOWER(name) LIKE 'ada%'

Portable-style case folding, though function use can affect index use.

Filtering, Expressions & Functions

NULL Rules

IS NULL

Correct test for a missing/unknown value.

IS NOT NULL

Correct test for a non-NULL value.

column = NULL

Do not use; comparisons with NULL evaluate to UNKNOWN rather than true.

COALESCE(a, b, c)

Return the first non-NULL expression.

NULLIF(a, b)

Return NULL when a equals b; otherwise return a.

COUNT(column)

Counts non-NULL values only.

COUNT(*)

Counts rows, regardless of NULL values.

Filtering, Expressions & Functions

CASE Expressions

CASE WHEN condition THEN value ELSE other END

General conditional expression.

CASE status WHEN 'A' THEN 'Active' ELSE 'Other' END

Simple CASE comparing one expression to several values.

ORDER BY CASE ... END

Use conditional logic to define a custom sort order.

SUM(CASE WHEN ... THEN 1 ELSE 0 END)

Portable conditional counting pattern.

Filtering, Expressions & Functions

String Functions

LOWER(text)

Convert text to lowercase.

UPPER(text)

Convert text to uppercase.

TRIM(text)

Remove surrounding whitespace or configured characters.

LENGTH(text)

Return string length; byte/character semantics vary by database and function.

SUBSTRING(text FROM start FOR count)

Standard-style substring syntax; dialect alternatives exist.

REPLACE(text, old, new)

Replace occurrences inside text.

CONCAT(a, b)

Concatenate values where supported.

a || b

Standard concatenation operator; MySQL behavior differs unless configured.

Filtering, Expressions & Functions

Numeric & Date/Time Functions

ABS(n)

Absolute numeric value.

ROUND(n, digits)

Round a number.

CEIL(n) / CEILING(n)

Round upward.

FLOOR(n)

Round downward.

CURRENT_DATE

Current date.

CURRENT_TIME

Current time.

CURRENT_TIMESTAMP

Current date and time.

EXTRACT(YEAR FROM ts)

Read a date/time field where supported.

Date arithmetic

Intervals, date-add functions, and syntax vary significantly by database.

Aggregates, GROUP BY, joins, subqueries, set operators, CTEs, and window functions.

Panel 3 / 6

03 — Grouping, Joins & Result Sets

Grouping, Joins & Result Sets

Aggregate Functions

COUNT(*)

Count rows.

COUNT(DISTINCT user_id)

Count distinct non-NULL values.

SUM(amount)

Total numeric values.

AVG(amount)

Average non-NULL numeric values.

MIN(value)

Smallest value.

MAX(value)

Largest value.

Grouping, Joins & Result Sets

GROUP BY & HAVING

GROUP BY department

Collapse rows into one result group per department.

GROUP BY department, status

Group by multiple expressions.

HAVING COUNT(*) > 5

Filter groups after aggregation.

WHERE ...

Filter individual rows before grouping.

HAVING ...

Filter aggregate groups after GROUP BY.

Grouping, Joins & Result Sets

JOIN Types

INNER JOIN

Return matching rows from both sides.

LEFT JOIN

Keep every left-side row and matching right-side rows; unmatched right values become NULL.

RIGHT JOIN

Keep every right-side row where the engine supports it.

FULL OUTER JOIN

Keep matched rows plus unmatched rows from both sides where supported.

CROSS JOIN

Cartesian product: every left row paired with every right row.

SELF JOIN

Join a table to itself using aliases; useful for hierarchies and comparisons.

Grouping, Joins & Result Sets

JOIN Syntax & Safety

... JOIN orders o ON o.user_id = u.id

Put relationship conditions in ON.

LEFT JOIN ... ON ...

Filtering the right table in WHERE can unintentionally turn a LEFT JOIN into an inner-style result.

USING (id)

Concise join syntax when both sides share the same join-column name.

Qualified columns

Use aliases such as u.id and o.id to avoid ambiguity.

Many-to-many

Join through the relationship table rather than storing repeated lists in one column.

Grouping, Joins & Result Sets

Subqueries

WHERE id IN (SELECT ...)

Use a subquery as a set of candidate values.

WHERE EXISTS (SELECT 1 ...)

Efficient semantic pattern for existence checks.

FROM (SELECT ...) AS x

Use a derived table as a query source.

SELECT (SELECT ...)

Scalar subquery must return at most one value.

Correlated subquery

References the outer query; can be expressive but may need performance review.

Grouping, Joins & Result Sets

Set Operators

UNION

Combine compatible result sets and remove duplicates.

UNION ALL

Combine compatible result sets without duplicate elimination; usually faster.

INTERSECT

Rows present in both result sets where supported.

EXCEPT

Rows in the first result but not the second where supported.

Column compatibility

Each SELECT must return the same number of columns with compatible types.

Grouping, Joins & Result Sets

Common Table Expressions

WITH recent AS (SELECT ...) SELECT ... FROM recent

Name a query expression for readability and reuse in one statement.

Multiple CTEs

Separate CTE definitions with commas.

WITH RECURSIVE

Express hierarchies, trees, graphs, and iterative relationships where supported.

CTE optimization

Materialization/inlining behavior differs by engine and version; inspect plans for performance-critical queries.

Grouping, Joins & Result Sets

Window Functions

ROW_NUMBER() OVER (...)

Assign a unique sequence within the window ordering.

RANK() OVER (...)

Ranking with gaps after ties.

DENSE_RANK() OVER (...)

Ranking without gaps after ties.

SUM(amount) OVER (...)

Running or partitioned totals without collapsing rows.

LAG(value) OVER (...)

Read a previous row's value.

LEAD(value) OVER (...)

Read a following row's value.

PARTITION BY department

Restart the window calculation for each partition.

ORDER BY created_at

Define order inside a window.

INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, views, constraints, and safe change patterns.

Panel 4 / 6

04 — Data Changes & Schema

Data Changes & Schema

INSERT

INSERT INTO users (name,email) VALUES ('Ada','a@example.com');

Insert one row.

INSERT INTO t (a,b) VALUES (1,2), (3,4);

Insert multiple rows where supported.

INSERT INTO archive (...) SELECT ...;

Insert rows produced by a query.

DEFAULT

Use a column's configured default value.

RETURNING ...

PostgreSQL and some other engines can return affected rows directly.

Data Changes & Schema

UPDATE

UPDATE users SET active = 0 WHERE id = 42;

Change matching rows.

SET a = 1, b = 2

Update multiple columns.

WHERE ...

Critical safety boundary; without it every row may be updated.

UPDATE ... FROM ...

Join-style update syntax exists in PostgreSQL and some engines; details differ.

Preview first

Run the equivalent SELECT with the same WHERE before a broad update.

Data Changes & Schema

DELETE

DELETE FROM users WHERE id = 42;

Delete matching rows.

DELETE FROM users;

Delete every row while keeping the table.

WHERE ...

Critical safety boundary for selective deletion.

Foreign keys

Child references may block deletion or trigger configured referential actions.

Preview first

Run SELECT with the same WHERE before a broad delete.

Data Changes & Schema

UPSERT Patterns

PostgreSQL / SQLite

INSERT ... ON CONFLICT (...) DO UPDATE / DO NOTHING.

MySQL

INSERT ... ON DUPLICATE KEY UPDATE ...

MERGE

Standard-style matched/not-matched data-change statement; support and syntax vary.

Rule

Choose the conflict key deliberately and understand which constraint triggers the alternative action.

Data Changes & Schema

CREATE TABLE

CREATE TABLE users (...);

Create a table.

id INTEGER PRIMARY KEY

Define a primary key inline.

email TEXT NOT NULL UNIQUE

Combine required-value and uniqueness constraints.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Apply a default expression.

FOREIGN KEY (...) REFERENCES ...

Define an inter-table relationship.

Data Changes & Schema

ALTER / DROP / TRUNCATE

ALTER TABLE t ADD COLUMN x ...

Add a column.

ALTER TABLE t DROP COLUMN x

Remove a column where supported.

ALTER TABLE t RENAME COLUMN a TO b

Rename a column where supported.

ALTER TABLE t ADD CONSTRAINT ...

Add table-level constraints; support differs.

DROP TABLE t

Remove table structure and data.

DROP TABLE IF EXISTS t

Conditionally remove a table.

TRUNCATE TABLE t

Quickly remove all rows where supported; transactional behavior differs by engine.

Data Changes & Schema

Views

CREATE VIEW active_users AS SELECT ...

Save a query as a virtual relation.

DROP VIEW active_users

Remove a view definition.

Materialized view

Stores query results physically where supported; refresh semantics are engine-specific.

Use cases

Simplify complex joins, centralize reusable query logic, and expose limited projections.

Keys, constraints, normalization, indexing, query plans, schema design, and secure application access.

Panel 5 / 6

05 — Design, Integrity & Performance

Design, Integrity & Performance

Keys & Constraints

PRIMARY KEY

Uniquely identifies each row; implies uniqueness and non-NULL semantics.

FOREIGN KEY

Requires referenced values to exist according to the relationship definition.

UNIQUE

Prevent duplicate values or duplicate column combinations.

NOT NULL

Require a value.

CHECK (condition)

Enforce a row-level business rule where supported.

DEFAULT expression

Provide a value when INSERT omits the column.

Composite key

A key or constraint defined across multiple columns.

Design, Integrity & Performance

Referential Actions

ON DELETE CASCADE

Delete referencing child rows automatically.

ON DELETE SET NULL

Set the foreign-key column to NULL when the parent is deleted.

ON DELETE RESTRICT / NO ACTION

Prevent parent deletion when references exist; timing details vary.

ON UPDATE ...

Control behavior when a referenced key changes; support and defaults vary.

Rule

Choose cascading behavior based on ownership semantics, not convenience.

Design, Integrity & Performance

Normalization Quick Read

1NF

Store atomic values and avoid repeating groups inside a row.

2NF

For composite keys, non-key attributes depend on the whole key.

3NF

Non-key attributes should not depend transitively on other non-key attributes.

Junction table

Represent many-to-many relationships with a relationship table.

Denormalization

Intentional duplication for performance/read models; document why it exists.

Design, Integrity & Performance

Indexes

CREATE INDEX idx_users_email ON users(email);

Create a non-unique index.

CREATE UNIQUE INDEX ...

Create an index that also enforces uniqueness.

CREATE INDEX ... ON orders(user_id, created_at);

Composite index; column order matters.

DROP INDEX ...

Remove an index; syntax varies by database.

Good candidates

Frequently filtered, joined, sorted, or uniqueness-enforced columns.

Tradeoff

Indexes improve many reads but consume storage and add write/update maintenance cost.

Design, Integrity & Performance

Query Plans

EXPLAIN SELECT ...

Inspect the optimizer's planned operations.

EXPLAIN ANALYZE ...

Execute and measure the query in supporting systems; use carefully on writes or expensive queries.

Table/sequence scan

May be correct for small tables or broad result sets; not automatically a problem.

Index scan/search

Uses an index to locate rows.

Join strategy

Nested loop, hash, merge, and other strategies depend on engine and statistics.

Statistics

Optimizers depend on current table/index statistics to estimate row counts and costs.

Design, Integrity & Performance

Sargability & Query Habits

Indexed column directly

WHERE email = ? can use a suitable index more easily.

Function on indexed column

WHERE LOWER(email) = ? may require an expression/function index or prevent a normal index seek.

Leading wildcard

LIKE '%term' often cannot use a normal B-tree prefix efficiently.

Select needed columns

Avoid transferring unused data and reduce coupling to schema changes.

Filter early

Reduce rows before expensive joins/aggregations when semantics permit.

Design, Integrity & Performance

Prepared Statements & Injection Safety

SELECT ... WHERE id = ?

Use positional placeholders for data values.

... WHERE email = :email

Use named placeholders when supported by the client library.

Do not concatenate input

Never build executable SQL by directly inserting untrusted user values.

Identifiers

Placeholders represent values, not arbitrary table/column names; whitelist dynamic identifiers.

Least privilege

Application credentials should have only the database permissions the application needs.

Transactions, isolation, locking, permissions, metadata, dialect differences, and high-risk operations.

Panel 6 / 6

06 — Transactions, Advanced SQL & Dialects

Transactions, Advanced SQL & Dialects

Transaction Control

BEGIN / START TRANSACTION

Start an explicit transaction; exact syntax varies.

COMMIT

Make the transaction's changes durable.

ROLLBACK

Undo uncommitted work.

SAVEPOINT name

Create an intermediate rollback point.

ROLLBACK TO SAVEPOINT name

Undo work after the savepoint while keeping the transaction open.

RELEASE SAVEPOINT name

Discard a savepoint where supported.

Transactions, Advanced SQL & Dialects

ACID

Atomicity

A transaction succeeds as a unit or is rolled back.

Consistency

Committed data obeys defined integrity rules.

Isolation

Concurrent work is controlled so transactions do not interfere unpredictably.

Durability

Committed changes survive normal failures according to the database's durability guarantees.

Transactions, Advanced SQL & Dialects

Isolation Levels

READ UNCOMMITTED

Weakest standard level; dirty reads may be possible in systems that implement it.

READ COMMITTED

Prevents dirty reads; repeated reads can still observe committed changes.

REPEATABLE READ

Stronger repeat-read guarantees; exact phantom behavior varies by database.

SERIALIZABLE

Strongest standard isolation; may reject/retry conflicting transactions.

Reality

Engines implement isolation differently; verify your database's documentation.

Transactions, Advanced SQL & Dialects

Concurrency & Locking

Short transactions

Reduce lock duration and contention.

Consistent access order

Acquire/update shared resources in a consistent order to reduce deadlock risk.

Deadlock

Database detects a dependency cycle and usually aborts one transaction.

Retry policy

Applications may need bounded retries for serialization failures or deadlock victims.

Long reports

Large reads can interact with vacuum/version storage/locks differently across engines.

Transactions, Advanced SQL & Dialects

Permissions

GRANT SELECT ON table TO user;

Give a principal permission to read a table where supported.

GRANT INSERT, UPDATE ...

Grant specific write capabilities.

REVOKE ...

Remove a granted privilege.

Roles

Group privileges through roles where the database supports them.

Schema/database privileges

Object hierarchy and privilege syntax differ substantially among engines.

Transactions, Advanced SQL & Dialects

Metadata & Introspection

INFORMATION_SCHEMA.TABLES

Portable metadata view available in many relational databases.

INFORMATION_SCHEMA.COLUMNS

Inspect columns, types, defaults, and nullability where available.

Catalogs

PostgreSQL pg_catalog, MySQL INFORMATION_SCHEMA, and SQLite PRAGMA/sqlite_schema expose engine-specific metadata.

Constraints/indexes

Metadata queries are dialect-specific beyond basic INFORMATION_SCHEMA coverage.

Transactions, Advanced SQL & Dialects

PostgreSQL / MySQL / SQLite Quick Differences

PostgreSQL

Rich SQL feature set, RETURNING, ILIKE, schemas, arrays/JSONB, strong window/CTE support.

MySQL

Uses AUTO_INCREMENT, ON DUPLICATE KEY UPDATE, backtick identifiers, and engine-specific date/string functions.

SQLite

Embedded single-file database, dynamic type affinity, PRAGMA metadata/configuration, ON CONFLICT upserts.

Identity columns

PostgreSQL IDENTITY/SERIAL, MySQL AUTO_INCREMENT, SQLite INTEGER PRIMARY KEY behavior differ.

Boolean

Representation and native typing differ.

Date/time

Storage types and arithmetic differ; never assume one engine's date functions are portable.

Concatenation

PostgreSQL/SQLite commonly use ||; MySQL normally uses CONCAT().

Transactions, Advanced SQL & Dialects

High-Risk SQL: Slow Down

UPDATE without WHERE

Can change every row.

DELETE without WHERE

Can delete every row.

DROP TABLE / DROP DATABASE

Removes database structures and data.

TRUNCATE

Removes all rows quickly and may have special transactional/identity behavior.

ALTER destructive changes

Dropping or changing columns/types can destroy or rewrite data.

Before running

Verify environment/database/schema, preview affected rows, back up important data, and prefer reversible migrations.

Transactions, Advanced SQL & Dialects

Compact Query Pattern

Read

SELECT columns FROM tables JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

Create

INSERT INTO table (columns) VALUES (values).

Change

UPDATE table SET column = value WHERE key = value.

Remove

DELETE FROM table WHERE key = value.

Define

CREATE / ALTER / DROP schema objects.

Protect

Constraints + transactions + prepared statements + least privilege + backups.