Structured Query Language (SQL) is the standard language for relational databases. Despite dozens of database systems existing (PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and more), the core SQL syntax is largely standardized. Learning SQL fundamentals gives you the ability to query and manipulate data in any relational database with minimal adjustment for dialect differences.
Basic Query Structure
Every SQL query follows a logical order of clauses. The SELECT statement retrieves data.
SELECT specifies which columns to return.
FROM specifies the table to query.
WHERE filters rows based on conditions.
GROUP BY groups rows for aggregation.
HAVING filters groups after aggregation.
ORDER BY sorts the result.
LIMIT restricts the number of rows returned.
The clauses are processed in a specific logical order even though you write them differently: FROM and JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT.
SELECT and Column Expressions
SELECT star retrieves all columns. SELECT column1, column2 retrieves named columns. Column aliases give a column a different name in the result using AS: SELECT price * quantity AS total.
DISTINCT removes duplicate rows: SELECT DISTINCT country FROM customers.
WHERE Clause Operators
Comparison: equals, not equals (both != and <>), less than, greater than, less than or equal, greater than or equal.
Range: BETWEEN min AND max (inclusive on both ends).
List membership: IN (value1, value2, value3).
Pattern matching: LIKE with percent wildcard (any sequence) and underscore wildcard (any single character). LIKE 'A%' matches values starting with A.
Null checks: IS NULL and IS NOT NULL. Never use = NULL because null comparisons require IS.
Logical operators: AND, OR, NOT.
JOIN Types
JOIN combines rows from multiple tables based on a related column.
INNER JOIN returns rows that have matching values in both tables.
LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table plus matching rows from the right. Unmatched right-table columns are null.
RIGHT JOIN returns all rows from the right table plus matching rows from the left.
FULL OUTER JOIN returns all rows from both tables with nulls where there is no match.
CROSS JOIN returns the Cartesian product (every combination) of rows from both tables.
SELF JOIN joins a table to itself, useful for hierarchical data.
Aggregation Functions
COUNT(column) counts non-null values. COUNT(*) counts all rows including nulls.
SUM(column) totals numeric values.
AVG(column) computes the mean.
MIN(column) and MAX(column) return the extreme values.
Aggregation functions work on groups defined by GROUP BY. Without GROUP BY, they apply to the entire result set.
Subqueries
A subquery is a query nested inside another query.
Scalar subquery: returns a single value and can appear in WHERE or SELECT.
Row subquery: returns a single row.
Table subquery (derived table): returns multiple rows and columns, used in FROM as if it were a table.
Correlated subquery: references columns from the outer query and is evaluated once per outer row.
EXISTS and NOT EXISTS check whether a subquery returns any rows.
Window Functions
Window functions compute values across a set of rows related to the current row without collapsing them (unlike GROUP BY). Common window functions:
ROW_NUMBER() assigns a sequential number to each row within a partition.
RANK() assigns a rank with gaps for ties.
DENSE_RANK() assigns a rank without gaps.
LAG(column, n) returns the value of a column n rows before the current row.
LEAD(column, n) returns the value n rows after.
SUM(column) OVER (PARTITION BY group) computes a running total within groups.
Window functions require an OVER clause specifying the window definition.
Data Modification
INSERT INTO table (col1, col2) VALUES (val1, val2) adds a new row.
UPDATE table SET col1 = val1 WHERE condition modifies existing rows.
DELETE FROM table WHERE condition removes rows. Without WHERE, deletes all rows.
Always include WHERE in UPDATE and DELETE unless you intend to affect every row.
Schema Definition
CREATE TABLE defines a new table with column names, data types, and constraints.
ALTER TABLE modifies an existing table: adding columns, changing types, adding constraints.
DROP TABLE removes a table and all its data.
CREATE INDEX adds an index on one or more columns to speed up queries.
Common Constraints
PRIMARY KEY: uniquely identifies each row.
UNIQUE: prevents duplicate values in a column.
NOT NULL: requires a value (null is not allowed).
FOREIGN KEY: enforces referential integrity between tables.
DEFAULT: provides a default value when none is specified.
CHECK: enforces a condition on column values.
Using the DevHexLab SQL Reference
Open the reference at /tools/reference/sql-reference. Browse SQL commands by category, view syntax examples, and copy patterns for use in your queries. Useful for both beginners learning SQL and experienced developers needing a quick syntax reminder.