SQL for Testers

Before you run anything: safe practice

A SQL client connected to a shared database is a powerful tool and an easy way to cause an incident. Verification is reading, so most of the time you should not be able to write at all. When you do need to change data on your own environment, prove the scope of the change before you make it.

• Use a read-only account for verification. If your account can write, one slip becomes an outage or lost test data for the whole team.
• Never run UPDATE or DELETE on a shared environment (staging, UAT, a shared test database) without the agreement of whoever owns it. Announce it, and keep the exact statement in the ticket.
• Before any UPDATE or DELETE, run the same WHERE clause as SELECT COUNT(*) and check the number. Then run the change inside a transaction and compare the rows-affected count before you COMMIT.
• While exploring a large table, add LIMIT (or your dialect’s equivalent) and filter on indexed columns such as the primary key or a date range.
• Do not browse production personal data for convenience. If production access is part of your role, follow the access and logging rules that come with it.

-- On your own test database only
BEGIN;

SELECT COUNT(*) AS will_change
FROM orders
WHERE status = 'PENDING' AND created_at < '2026-01-01';
-- expect: 3

UPDATE orders
SET status = 'CANCELLED'
WHERE status = 'PENDING' AND created_at < '2026-01-01';
-- the client reports rows affected: must also be 3

ROLLBACK;  -- COMMIT only when every number matches

The example schema

The examples use four tables. A customer places orders; each order has one or more item lines and zero or more payments. The schema declares primary keys, a unique email, NOT NULL columns, CHECK constraints for status values and amounts, and foreign keys. It uses table-level constraint clauses, which PostgreSQL, MySQL 8 and SQLite all accept; remember that MySQL enforces CHECK only from 8.0.16 and SQLite enforces foreign keys only with PRAGMA foreign_keys = ON.

Three business rules at the bottom are the oracle for the checks that follow. Your own system will have different rules; the first step of database testing is always to write them down in this form, because a rule written as a sentence can be turned into a query that finds its violations.

CREATE TABLE customers (
  customer_id  INTEGER       NOT NULL,
  email        VARCHAR(254)  NOT NULL,
  full_name    VARCHAR(200)  NOT NULL,
  created_at   TIMESTAMP     NOT NULL,
  CONSTRAINT pk_customers PRIMARY KEY (customer_id),
  CONSTRAINT uq_customers_email UNIQUE (email)
);

CREATE TABLE orders (
  order_id     INTEGER       NOT NULL,
  customer_id  INTEGER       NOT NULL,
  status       VARCHAR(20)   NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL,
  created_at   TIMESTAMP     NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
  CONSTRAINT ck_orders_status CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
  CONSTRAINT ck_orders_total CHECK (total_amount >= 0)
);

CREATE TABLE order_items (
  order_item_id INTEGER       NOT NULL,
  order_id      INTEGER       NOT NULL,
  product_sku   VARCHAR(40)   NOT NULL,
  quantity      INTEGER       NOT NULL,
  unit_price    NUMERIC(12,2) NOT NULL,
  CONSTRAINT pk_order_items PRIMARY KEY (order_item_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT ck_items_quantity CHECK (quantity > 0),
  CONSTRAINT ck_items_price CHECK (unit_price >= 0)
);

CREATE TABLE payments (
  payment_id   INTEGER       NOT NULL,
  order_id     INTEGER       NOT NULL,
  amount       NUMERIC(12,2) NOT NULL,
  status       VARCHAR(20)   NOT NULL,
  paid_at      TIMESTAMP     NULL,
  CONSTRAINT pk_payments PRIMARY KEY (payment_id),
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT ck_payments_status CHECK (status IN ('CAPTURED', 'FAILED', 'REFUNDED'))
);

-- Business rules the checks below verify:
-- R1 orders.total_amount = SUM(quantity * unit_price) of its items (no tax or discount here)
-- R2 a PAID or SHIPPED order has CAPTURED payments that add up to total_amount
-- R3 a CAPTURED payment has paid_at, and paid_at is not earlier than the order's created_at

Try it: SQL Dataset Generator Try it: SQL INSERT Generator

Verify one change: look it up by key

After an action in the UI or API, find the affected rows by the key the application gave you — the order id in the API response, the id in the page URL — rather than by “the latest row”, which another tester may have created a second earlier. Select explicit columns, not SELECT *, so the result shows exactly the fields you are checking and does not break when a column is added.

Check the parent and the children together. After a create, the item lines must exist with the right quantities and prices; after a delete, the question is what remains, and one row with three counts answers it in a single query.

-- The API returned order_id 1042
SELECT o.order_id, o.status, o.total_amount, o.created_at, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_id = 1042;

SELECT order_item_id, product_sku, quantity, unit_price
FROM order_items
WHERE order_id = 1042
ORDER BY order_item_id;

-- After a hard delete of order 1042: nothing left behind anywhere
SELECT
  (SELECT COUNT(*) FROM orders      WHERE order_id = 1042) AS orders_left,
  (SELECT COUNT(*) FROM order_items WHERE order_id = 1042) AS items_left,
  (SELECT COUNT(*) FROM payments    WHERE order_id = 1042) AS payments_left;

Count and sum reconciliation

Reconciliation compares two numbers that must agree. Inside one database this is a header against its lines (R1) or orders against payments (R2); across systems it is the same count and sum computed in two places — the order database and the invoicing service, the database and a finance report. GROUP BY with HAVING returns only the groups that disagree, so an empty result is a pass.

Two details prevent false alarms. Compare money only in exact decimal types such as NUMERIC or DECIMAL; if amounts are stored as floating point, that is a finding in itself. And use COALESCE when one side may have no rows, because SUM over no rows is NULL and a comparison with NULL is never true, which would hide an order with no payments at all.

-- R1: order header total vs the sum of its lines
SELECT o.order_id, o.total_amount,
       SUM(oi.quantity * oi.unit_price) AS lines_total
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount <> SUM(oi.quantity * oi.unit_price);

-- R2: paid orders vs captured payments
SELECT o.order_id, o.total_amount,
       COALESCE(SUM(p.amount), 0) AS captured
FROM orders o
LEFT JOIN payments p
  ON p.order_id = o.order_id AND p.status = 'CAPTURED'
WHERE o.status IN ('PAID', 'SHIPPED')
GROUP BY o.order_id, o.total_amount
HAVING COALESCE(SUM(p.amount), 0) <> o.total_amount;

-- Count and sum per status for one month, to compare with a report or another service
SELECT status, COUNT(*) AS order_count, SUM(total_amount) AS amount_sum
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01'
GROUP BY status
ORDER BY status;

Try it: SQL Formatter

Orphan detection with LEFT JOIN … IS NULL

An orphan is a child row whose parent does not exist. A LEFT JOIN keeps every child row and fills the parent columns with NULL when no parent matches, so filtering on the parent key IS NULL lists exactly the orphans. The same shape run the other way finds parents without the children they must have, such as an order with no items.

With foreign keys declared and enforced, orphans cannot be created — but they still appear when constraints were disabled for a bulk load, when SQLite runs without the foreign-key pragma, when a relationship spans two services’ databases, or when the constraint was never created. Avoid NOT IN with a subquery for this check: if the subquery returns a single NULL, NOT IN returns no rows at all and the check passes silently. NOT EXISTS and LEFT JOIN … IS NULL do not have this problem.

-- Items whose order does not exist
SELECT oi.order_item_id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE o.order_id IS NULL;

-- Payments whose order does not exist
SELECT p.payment_id, p.order_id
FROM payments p
LEFT JOIN orders o ON o.order_id = p.order_id
WHERE o.order_id IS NULL;

-- The reverse: orders that have no items at all
SELECT o.order_id, o.status, o.created_at
FROM orders o
LEFT JOIN order_items oi ON oi.order_id = o.order_id
WHERE oi.order_item_id IS NULL;

-- The same check written with NOT EXISTS (safe when NULLs are present)
SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id
);

Duplicate detection with GROUP BY … HAVING COUNT(*) > 1

Group by the value that should be unique and keep the groups that occur more than once. Normalise first when the business treats variants as the same thing: LOWER and TRIM catch emails that differ only in case or spaces. A UNIQUE constraint on the raw column does not stop those — although in MySQL the default case-insensitive collations already treat “Layla.H@example.com” and “layla.h@example.com” as equal, while PostgreSQL compares text case-sensitively.

Duplicates are also a symptom of missing idempotency: a double-clicked button or a retried request that created two orders or captured one payment twice. If your design allows split payments, compare the captured sum with the order total (R2) instead of counting rows.

-- Emails that differ only in letter case or surrounding spaces
SELECT LOWER(TRIM(email)) AS email_key, COUNT(*) AS copies
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

-- List the duplicate rows themselves, grouped together
SELECT c.customer_id, c.email, c.created_at
FROM customers c
JOIN (
  SELECT LOWER(TRIM(email)) AS email_key
  FROM customers
  GROUP BY LOWER(TRIM(email))
  HAVING COUNT(*) > 1
) d ON d.email_key = LOWER(TRIM(c.email))
ORDER BY d.email_key, c.customer_id;

-- An order charged more than once
SELECT order_id, COUNT(*) AS captured_payments, SUM(amount) AS captured_total
FROM payments
WHERE status = 'CAPTURED'
GROUP BY order_id
HAVING COUNT(*) > 1;

NULL checks and date-range checks

NULL means “unknown”, and any comparison with it — including column = NULL — is neither true nor false, so such rows silently drop out of the result. Always test with IS NULL or IS NOT NULL. Remember also that COUNT(column) skips NULLs while COUNT(*) counts rows, and that an empty string is not NULL in PostgreSQL, MySQL or SQLite (Oracle Database treats an empty string as NULL).

For dates, use half-open ranges: created_at >= the first day AND created_at < the first day of the next period. BETWEEN with an end date of 2026-09-30 misses everything after midnight on the 30th when the column holds a time. Check that both sides of a comparison use the same time zone; a CURRENT_TIMESTAMP in one zone compared with values stored in another produces false “future” rows or hides real ones.

-- Required text that is NULL or blank
SELECT customer_id, email
FROM customers
WHERE full_name IS NULL OR TRIM(full_name) = '';

-- R3: captured payments without a payment time
SELECT payment_id, order_id
FROM payments
WHERE status = 'CAPTURED' AND paid_at IS NULL;

-- R3: payments recorded before their order existed
SELECT p.payment_id, o.order_id, o.created_at, p.paid_at
FROM payments p
JOIN orders o ON o.order_id = p.order_id
WHERE p.paid_at < o.created_at;

-- Timestamps in the future (clock or time-zone defects)
SELECT order_id, created_at
FROM orders
WHERE created_at > CURRENT_TIMESTAMP;

-- One calendar month as a half-open range
SELECT COUNT(*) AS september_orders
FROM orders
WHERE created_at >= '2026-09-01' AND created_at < '2026-10-01';

Try it: SQL Query Validator

Dialect differences that break portable queries

The SQL standard, ISO/IEC 9075, defines the language; each engine implements it to a different degree and adds its own syntax. The checks above avoid the differences, but these are the ones testers hit most often when they copy a query from one project to another:

• Row limits — LIMIT 50 in PostgreSQL, MySQL and SQLite; SELECT TOP (50) in SQL Server; the standard FETCH FIRST 50 ROWS ONLY in PostgreSQL and in Oracle Database 12c and later.
• String concatenation — || in PostgreSQL, SQLite and Oracle; in MySQL || is a logical OR by default, so use CONCAT(), which PostgreSQL, MySQL and SQL Server all provide.
• Date arithmetic — CURRENT_DATE - INTERVAL '7 days' in PostgreSQL, CURRENT_DATE - INTERVAL 7 DAY in MySQL, date('now', '-7 days') in SQLite, DATEADD(day, -7, CAST(GETDATE() AS date)) in SQL Server. Literal ISO dates such as '2026-09-01' are the most portable choice.
• Truncating a timestamp to a day — CAST(created_at AS DATE) in PostgreSQL, MySQL and SQL Server; date(created_at) in SQLite.
• Text comparison — PostgreSQL compares text case-sensitively; MySQL’s default collations are case-insensitive; SQLite’s = is case-sensitive, and its LIKE is case-insensitive for ASCII letters only.
• Identifier quoting — "double quotes" in the standard, PostgreSQL and SQLite; `backticks` in MySQL; [brackets] in SQL Server.
• SELECT without FROM — accepted by PostgreSQL, MySQL, SQLite and SQL Server; Oracle Database traditionally needs FROM dual.

Try it: SQL Formatter Try it: SQL Query Validator

From ad-hoc queries to a reusable check suite

Once a query has found a defect, keep it. A folder of named checks, each returning zero rows when the rule holds, becomes a regression suite for data: run it after deployments, after migrations and on a schedule on staging, and wire it into the automated tests so that an API call is followed by the matching database assertion.

Cross-system comparisons often start with a list of ids exported from another system. Turn the list into a derived table or an IN clause and join it to your own data, as below; the Mutqan VALUES and IN-clause generators produce the list syntax from pasted values.

• One check per rule, named after the rule — for example orders_total_matches_items.
• Zero rows means pass; any rows are the evidence you attach to the bug report together with the query.
• Read-only: a check never modifies data.
• Bounded: filter by date or batch so the check stays fast on large tables.
• Versioned beside the migrations and reviewed like code.

-- Ids exported from the invoicing service that the order database does not have.
-- PostgreSQL and SQLite syntax; MySQL 8.0.19+ writes VALUES ROW(1042), ROW(1043), ...
WITH exported (order_id) AS (
  VALUES (1042), (1043), (1044), (1051)
)
SELECT e.order_id
FROM exported e
LEFT JOIN orders o ON o.order_id = e.order_id
WHERE o.order_id IS NULL;

Try it: SQL VALUES Generator Try it: SQL IN Clause Generator Try it: CSV to SQL Try it: JSON to SQL

Frequently asked questions

References

• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupLexical structure of SQL in PostgreSQL: identifiers, quoting, string constants and escaping.
• SQL As Understood By SQLiteSQLiteThe SQL grammar accepted by SQLite, statement by statement, with railroad diagrams.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.
• Transactions — PostgreSQL DocumentationPostgreSQL Global Development GroupWhat a transaction is: several steps applied all-or-nothing with BEGIN, COMMIT and ROLLBACK, invisible to others until committed.
• OWASP Cheat Sheet Series: SQL Injection PreventionOWASP FoundationParameterised queries, stored procedures used safely, allow-list validation and least privilege — the defences a database test should confirm.