SQL for Testers: Queries Every QA Engineer Should Know

A customer changed her delivery address, the UI displayed the new city, and the next parcel went to the old one. One query showed the profile row had changed. A second query showed the open order still referenced an address snapshot created at checkout. SQL did not merely confirm a bug. It located the boundary between expected historical data and the field the fulfillment job actually read.

Testers need queries that answer focused product questions. The examples use PostgreSQL and a fictional commerce schema. LIMIT, filtered aggregates, interval syntax, and case-insensitive matching vary by database, so adapt them deliberately.

Learn the schema through one business flow

Choose a flow such as checkout and trace its identifiers through customers, addresses, orders, order_items, payments, and shipments. Ask for the schema definition, foreign keys, status meanings, money representation, time zone policy, and deletion strategy. Guessing column meaning is more dangerous than not knowing syntax.

Use a simple relationship map:

customers 1 -> many addresses
customers 1 -> many orders
orders    1 -> many order_items
orders    1 -> many payment attempts
orders    1 -> zero or many shipments
orders    1 -> one shipping-address snapshot

The text block describes cardinality. It explains why joining orders, items, and payments directly can multiply rows. Confirm the map against actual constraints and application behavior.

Select the exact record you created

Begin with SELECT, explicit columns, and a unique test marker. Avoid SELECT * in evidence because schema changes add noise and sensitive fields may appear unexpectedly.

SELECT order_id, client_ref, status, currency_code, total_minor, created_at
FROM orders
WHERE client_ref = 'SQL-LEARN-0710-01';

This PostgreSQL sql query should return exactly one row for a uniquely constrained reference. Zero rows means the write did not persist or the environment is wrong. More than one row indicates weak data identity or a missing uniqueness rule.

Combine conditions with parentheses when AND and OR are mixed. Filter timestamps with a half-open interval so adjacent windows do not overlap:

SELECT order_id, created_at
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-07-10 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-07-11 00:00:00+00'
ORDER BY created_at, order_id;

The sql query returns one UTC calendar day in deterministic order. Confirm whether the product’s “day” uses UTC, customer time, or store time before copying it into a report test.

Treat NULL as unknown, not empty

NULL is tested with IS NULL and IS NOT NULL, never = NULL. It differs from an empty string, zero, and false. That distinction often explains missing notifications or incomplete migrations.

SELECT customer_id, phone_number
FROM customers
WHERE phone_number IS NULL OR phone_number = '';

The PostgreSQL sql query deliberately returns two categories. Review them separately before declaring both invalid. The product may allow unknown phone numbers while rejecting blank submitted values.

NOT IN can surprise when its subquery contains NULL. For absence checks, prefer NOT EXISTS with a correlated condition:

SELECT o.order_id
FROM orders o
WHERE o.status = 'PAID'
  AND NOT EXISTS (
    SELECT 1
    FROM payments p
    WHERE p.order_id = o.order_id
      AND p.status = 'CAPTURED'
  );

This sql query should return zero rows if every paid order has a captured payment. Returned IDs are high-value defect candidates, though the team must confirm whether legacy imports are exempt.

Join tables without changing the question

Choose the starting table at the grain of the question. To inspect one order’s lines, join one-to-many and expect multiple rows. To assert one order total, aggregate lines before joining other one-to-many tables.

WITH item_totals AS (
  SELECT order_id,
         sum(quantity) AS units,
         sum(quantity * unit_price_minor) AS subtotal_minor
  FROM order_items
  WHERE status <> 'VOID'
  GROUP BY order_id
)
SELECT o.order_id, o.subtotal_minor, i.units, i.subtotal_minor AS calculated
FROM orders o
JOIN item_totals i USING (order_id)
WHERE o.client_ref = 'SQL-LEARN-0710-01';

The PostgreSQL sql query should return one order-level row. If you add payments later, aggregate them in another common table expression first. Counting rows after a broad join is a frequent source of false defects.

Use LEFT JOIN when the absence of a related record matters, such as orders without shipments. A condition on the right table in the WHERE clause can accidentally turn a left join into an inner join. Put relationship filters in ON when missing rows must remain visible.

Aggregate for reports and reconciliation

COUNT, SUM, MIN, MAX, and AVG help compare reports with source data. State the grouping grain and filters. COUNT(*) counts rows; COUNT(column) ignores nulls; COUNT(DISTINCT key) counts unique non-null keys.

SELECT currency_code,
       count(DISTINCT order_id) AS orders,
       sum(total_minor) AS booked_minor
FROM orders
WHERE status IN ('PAID', 'FULFILLED')
  AND created_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND created_at <  TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY currency_code
HAVING sum(total_minor) <> 0
ORDER BY currency_code;

The sql query creates July controls by currency. Do not add different currencies together unless a documented conversion rule and effective rate are applied. Compare the report’s status, date, refund, and time zone rules before filing a mismatch.

HAVING filters groups after aggregation; WHERE filters source rows before aggregation. Confusing them changes which data participates in the total.

Find duplicates and sequence defects

Group by the business key to find duplicate records:

SELECT provider_reference, count(*) AS occurrences
FROM payments
WHERE provider_reference IS NOT NULL
GROUP BY provider_reference
HAVING count(*) > 1
ORDER BY occurrences DESC, provider_reference;

This PostgreSQL sql query should return zero rows if provider references are unique. A duplicate may be a retry defect, duplicate import, or legitimate multi-entry accounting design. Check the rule before deleting anything.

Window functions compare rows without collapsing them. They are useful for audit sequences and repeated status changes:

SELECT order_id, status, changed_at,
       lag(status) OVER (
         PARTITION BY order_id ORDER BY changed_at, audit_id
       ) AS previous_status
FROM order_status_audit
WHERE order_id = 910071;

The sql output should show the ordered history while retaining each row. Look for impossible transitions such as CANCELLED to FULFILLED, repeated transitions caused by retries, or identical timestamps that require a stable tie-breaker.

Use CASE and subqueries to express rules

CASE makes diagnostic categories visible. It should clarify a rule, not hide an unexplained collection of magic values.

SELECT shipment_id,
       CASE
         WHEN delivered_at IS NOT NULL THEN 'DELIVERED'
         WHEN shipped_at IS NOT NULL THEN 'IN_TRANSIT'
         WHEN label_created_at IS NOT NULL THEN 'LABEL_CREATED'
         ELSE 'NOT_STARTED'
       END AS derived_state,
       status AS stored_state
FROM shipments
WHERE order_id = 910071;

The PostgreSQL sql result lets the tester compare a derived state with the stored state. Any disagreement needs product-rule review. A subquery or common table expression is preferable when the derivation needs multiple aggregates or would be repeated.

Readable SQL is test evidence. Format joins and filters, name derived columns, and add comments only for non-obvious business assumptions.

Change data only inside a safety model

Most validation queries should run with read-only credentials. Direct UPDATE and DELETE bypass application authorization, events, cache invalidation, audit behavior, and downstream calls. Use supported APIs or fixtures for normal setup.

Know whether your connection reads the primary database or a replica. A successful API write followed by an empty replica query may be normal replication delay, while the same empty result on the primary indicates another boundary. Record the connection target and transaction isolation when timing matters. Repeating a query until it passes can conceal a user-visible consistency defect, so compare the observed delay with the product’s declared read-after-write behavior.

If a controlled database exercise requires a write, verify the environment and target rows first, start a transaction, perform the change, validate it, and roll back:

BEGIN;

UPDATE customers
SET loyalty_tier = 'GOLD'
WHERE external_ref = 'SQL-SANDBOX-ONLY';

SELECT customer_id, loyalty_tier
FROM customers
WHERE external_ref = 'SQL-SANDBOX-ONLY';

ROLLBACK;

The PostgreSQL sql block should report one updated row, show GOLD inside the transaction, and restore the original value afterward. Do not run it where the account, environment policy, or data ownership is uncertain.

Turn query output into defect evidence

Record database name, schema version, query, parameters, execution time, row count, and a minimal redacted result. Tie the result to a request ID or test reference. Never expose password hashes, tokens, personal addresses, or full payment data in tickets.

Before trusting an unexpected result, verify the environment, transaction visibility, replica lag, time zone, join grain, status filters, null handling, and whether a background worker is still running. Ask a peer to review complex reconciliation SQL. The best tester query is not the cleverest one. It is the smallest query that another person can rerun and use to reach the same conclusion.