A screen shows you what the application chooses to display. The database shows you what actually happened. An order confirmation page can say “Payment successful” while the payments table holds two charges for that order, or none at all. A tester who can query the database checks the truth beneath the interface, and the single most useful SQL skill for that job is understanding joins, especially the difference between an inner join and a left join.

A tiny dataset with three hidden problems

Consider two tables in an online store’s test database. The orders table has five orders, O1 to O5. The payments table has five payments, each with an order_id column saying which order it belongs to:

payment_idorder_idamount (₹)
P1O11,200
P2O2850
P3O32,400
P4O32,400
P5O9600

Look closely and there are three problems. Orders O4 and O5 have no payment. Order O3 was paid twice. Payment P5 points to an order O9 that does not exist. None of these would be obvious from the application’s order history screen. Joins find all three.

Inner join: only the matches

An inner join between orders and payments on the order ID returns only rows where a match exists on both sides. Here that gives four rows: O1 with P1, O2 with P2, O3 with P3, and O3 with P4.

Two things to notice. First, O4 and O5 vanish entirely, because they have no matching payment. Second, O3 appears twice, because it matches two payments. An inner join answers “show me orders together with their payments”, and it silently drops anything unmatched. That silence is exactly why it is dangerous for verification: a report built on an inner join will look clean even when records are missing.

Left join: everything from the left, matched where possible

A left join from orders to payments keeps every order, whether or not it has a payment. Where there is a match, the payment columns are filled in; where there is none, they come back as NULL. The result has six rows: the four matched rows above, plus O4 with empty payment columns and O5 with empty payment columns.

Now add a filter keeping only rows where the payment ID is NULL. What remains is O4 and O5: the orders with no payment. This pattern, a left join followed by a filter on the right side being NULL, is often called an anti-join, and it is one of the most valuable checks a tester can run. It answers “which records are missing their counterpart?”

Turn it around, joining from payments to orders, and the same pattern finds P5: a payment with no matching order. That is an orphan record, and in a payments context it usually signals a serious problem, perhaps a failed order creation after a successful charge, which means a customer paid for nothing.

Grouping: finding the duplicate

The double payment on O3 needs a different tool. Group the payments table by order ID, count the rows in each group, and keep only groups with a count greater than one, using GROUP BY with a HAVING condition. O3 appears with a count of 2. In a real system that might be a retry that was not handled idempotently, a double-click on the Pay button, or a payment gateway callback processed twice.

Turning this into test practice

These patterns map onto concrete checks during functional and integration testing:

  • After a UI action, confirm persistence. When a test registers a new user through the screen, look up that user by email in the users table and check that every field was stored as entered, including those the screen does not show, such as created timestamp, status flag and consent version.
  • Check related records were created together. A new user might need a row in users, a row in user_profiles and a row in wallet_accounts. An anti-join from users to wallet_accounts finds users whose wallet was never created.
  • Check no duplicates appear on retry. Submit the same action twice quickly, then group and count.
  • Reconcile counts. After a batch job that imports 10,000 rows, compare the source count with the target count, and use an anti-join to list exactly which rows failed to arrive.
  • Verify deletes and soft deletes. After a customer closes an account, check what happened to their dependent records. Hard-deleted parents with surviving children create orphans; soft-deleted ones should carry the right status flag.

Mistakes that give the wrong answer quietly

Using an inner join to check completeness. If you count orders using an inner join to payments and compare with the expected order count, unpaid orders disappear from your count while paid-twice orders inflate it. In this example the inner join gives four rows, and five orders exist. The numbers are close enough to look like a rounding issue, but they are wrong for two separate reasons that happen to offset.

Filtering in the wrong place. With a left join, a condition on the right table placed in the WHERE clause, such as “payment status is SUCCESS”, removes the unmatched rows and turns the left join back into an inner join in effect. If you want to keep all orders but only match successful payments, the condition belongs in the join’s ON clause instead. This one catches experienced people.

Comparing with equals NULL. In SQL, nothing equals NULL, not even NULL. The anti-join filter must use IS NULL, or it returns no rows and gives the false impression that nothing is missing.

A note on access and data

Testers should normally have read-only access to test databases and no direct access to production data. Where production checks are needed for investigating incidents, organisations typically provide masked replicas or controlled reporting views. Under India’s Digital Personal Data Protection Act, 2023, and under PCI DSS for card data, casual querying of real personal or card information in test environments is a compliance risk as well as an ethical one. The techniques above work just as well on synthetic or masked data.

Syntax details vary slightly across MySQL, PostgreSQL, Oracle and SQL Server, but inner joins, left joins, grouping and NULL handling behave the same way in all of them. Learn these few patterns well, and the database stops being a black box behind the application and becomes the most honest witness in your test.

Written by the RP Strativa Team. The tables and amounts are illustrative.

Leave a Reply

Trending