SQL Queries: Describing the Data You Want

Relational databases store data in tables, but applications need a language for asking questions and making changes.

That language is SQL, commonly pronounced either "S-Q-L" or "sequel."

SQL is a declarative language: you describe the data operation or result you want, and the database decides how to perform it.

You specify the logical request rather than writing a loop that reads every disk page.

A concrete example: unpaid invoices

Suppose an invoices table contains invoice ID, customer ID, status, due date, and total.

SELECT invoice_id, due_date, total
FROM invoices
WHERE customer_id = 1042
  AND status = 'unpaid'
ORDER BY due_date;

This query asks for selected columns from matching rows in due-date order.

It does not say whether to scan the whole table, use an index, or read records in parallel. The database's query planner chooses an execution strategy.

SELECT chooses a result

The basic shape is:

SELECT column_a, column_b
FROM table_name;

SELECT * requests every column. It is convenient for exploration, but production code often names required columns because:

Less data may cross the network.
Callers document what they depend on.
Added columns do not silently change results.
Sensitive fields are less likely to be exposed.

The result of a query is itself table-shaped, even when it combines or calculates values from several sources.

WHERE filters rows

WHERE defines conditions:

SELECT product_id, name, price
FROM products
WHERE active = TRUE
  AND price BETWEEN 10 AND 50;

Conditions can compare values, test membership, match patterns, and combine Boolean logic.

Parentheses make complex intent explicit:

WHERE active = TRUE
  AND (category = 'books' OR category = 'courses')

Missing values require IS NULL or IS NOT NULL, because null means unknown or absent rather than an ordinary comparable value.

ORDER BY makes ordering explicit

SQL does not guarantee row order without ORDER BY.

ORDER BY created_at DESC, invoice_id DESC

The second field provides deterministic ordering when multiple rows share the same timestamp.

For large results, ordering can be expensive because the database may need to sort many rows. A suitable index can sometimes provide the requested order more efficiently.

LIMIT reduces the returned result

SELECT id, title
FROM articles
ORDER BY published_at DESC
LIMIT 20;

LIMIT is useful for previews and pagination, but it should normally accompany deterministic ordering.

Offset-based pagination is simple:

LIMIT 20 OFFSET 40;

For deep pages or frequently changing data, cursor-based pagination using the last seen ordered values can be faster and more stable.

An order stores a customer ID, while the customer name lives in another table.

SELECT
  orders.order_id,
  customers.name,
  orders.total
FROM orders
JOIN customers
  ON customers.customer_id = orders.customer_id;

An inner JOIN returns rows with matches on both sides.

A LEFT JOIN preserves every row from the left table even when no related row exists. Columns from the missing side become null.

Joins express relationships. Incorrect join conditions can multiply rows or silently omit data, so keys and expected cardinality matter.

GROUP BY summarizes records

Aggregate functions calculate values across groups:

SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;

This produces one row per customer rather than one row per order.

Common aggregates include:

COUNT
SUM
AVG
MIN
MAX

HAVING filters groups after aggregation, while WHERE filters input rows before grouping.

INSERT creates records

INSERT INTO customers (name, email, joined_at)
VALUES ('Mira Rao', 'mira@example.com', CURRENT_DATE);

Naming columns makes the statement clearer and safer when table definitions change.

Many databases can return generated values:

INSERT INTO customers (name, email)
VALUES ('Mira Rao', 'mira@example.com')
RETURNING customer_id;

Bulk inserts can be much more efficient than sending one network request per row.

UPDATE changes matching rows

UPDATE invoices
SET status = 'paid', paid_at = CURRENT_TIMESTAMP
WHERE invoice_id = 9001;

An omitted or incorrect WHERE can change every row.

Before a high-impact update, teams often:

Run a SELECT with the same condition.
Confirm the expected row count.
Execute inside a transaction.
Verify the result before committing.

Permissions and backups add further protection, but careful query review remains essential.

DELETE removes matching rows

DELETE FROM sessions
WHERE expires_at < CURRENT_TIMESTAMP;

Deletion may interact with foreign keys. A database can reject removal of a referenced row, cascade deletion to dependents, or set references to null according to schema rules.

Some applications use soft deletion by recording deleted_at. That preserves history but makes every query responsible for excluding inactive rows and does not replace retention or privacy deletion requirements.

Parameters prevent SQL injection

Never build SQL by directly joining untrusted text:

// Unsafe
const query = `SELECT * FROM users WHERE email = '${email}'`;

An attacker may provide text that changes the query's meaning.

Use parameterized queries:

const result = await db.query(
  "SELECT user_id, name FROM users WHERE email = $1",
  [email]
);

The database treats the argument as data, not executable SQL syntax.

Parameters are a core security boundary, not a formatting preference.

A checkout may need to create an order, reduce inventory, and record payment status.

If those changes must succeed or fail together, execute them in a transaction:

BEGIN;
-- related statements
COMMIT;

On failure, ROLLBACK discards uncommitted changes.

Transactions also define how simultaneous work is observed. The appropriate isolation level depends on which anomalies the application must prevent.

The query planner chooses a path

Databases consider table statistics, indexes, join strategies, and estimated row counts.

An EXPLAIN command reveals the proposed or measured plan:

EXPLAIN
SELECT *
FROM invoices
WHERE customer_id = 1042;

A slow query is not always fixed by adding an index. It may return too much data, use an avoidable calculation, join on the wrong fields, or suffer from stale statistics.

Measure with realistic data before optimizing.

SQL has dialects

The SQL standard provides shared concepts, but PostgreSQL, MySQL, SQLite, SQL Server, Oracle, and other systems differ in:

Functions
Data types
Upsert syntax
Date operations
Generated identifiers
Administrative commands

Core relational thinking transfers, but production queries should follow the documentation and behavior of the actual database.

Knowledge check

Why is SQL called declarative?
What is the difference between WHERE and HAVING?
When would a LEFT JOIN be preferable to an inner join?
Why should application code use parameterized queries?
What information can an execution plan provide?

The one idea to remember

SQL describes the relational result or change you want. The database plans the physical work, while you remain responsible for clear conditions, safe parameters, correct relationships, and controlled changes.