Stop 05 · SQL & Databases for QATopics · 2/8

Writing Test Queries (SELECT, JOIN)

In short

Real business rules span tables: users in one, orders in another, payments in a third. JOINs let you check a rule across all of them with one query.

Applying SQL specifically to verify test scenarios — combining data across related tables with JOINs to confirm a complex business rule was applied correctly at the database level.

The purple blob in a white cap matches stacked tiffin boxes on a wooden crate to a list in its hand.

Think of it like this

The dabbawala's matching

A dabbawala matches each tiffin's code to an office address on his list. Tiffin plus address gives a delivery. A JOIN matches rows in two tables by a shared code, like orders.user_id to users.id.

Watch it happen

INNER JOIN vs LEFT JOIN

INNER JOIN

Only rows that match in both tables
Users with orders, and their orders
Hides users who never ordered

LEFT JOIN

All left rows, matches if any
Users without orders show NULLs
Great for finding missing links

Use INNER JOIN to check matched data. Use LEFT JOIN with IS NULL to hunt for things that should exist but don't.

The big ideas

What you need to know

Join on keys

Tables link through ids: orders.user_id points to users.id. The ON clause says which columns match.

Aliases keep it short

FROM users u lets you write u.name instead of users.name everywhere.

Zero rows can mean pass

Write queries that find rule breakers. If nothing comes back, the rule held.

See it in code

Walk through the code

sql

1-- Rule: Gold members always get free delivery.2-- Find any Gold member who was charged a fee.3SELECT u.id, u.name, o.id AS order_id, o.delivery_fee4FROM users u5JOIN orders o ON o.user_id = u.id6WHERE u.tier = 'GOLD'7  AND o.delivery_fee > 0;

1/3Start from the business rule and flip it: look for violations.

A rule-breaker query. Zero rows means the rule held. Any row is a bug, with the evidence attached.

Save yourself the pain

Common mistakes

Oops

Forgetting the ON clause or joining on the wrong columns.

Do this instead

Always join on the matching key, like o.user_id = u.id. Wrong joins give huge, nonsense results.

Oops

Using INNER JOIN to look for missing records.

Do this instead

INNER JOIN hides unmatched rows. Use LEFT JOIN and check for NULL.

Your mission

Hunt a rule breaker

~30 min

Quick check

You want every user, including those with no orders, alongside their order count. Which join fits?

Go deeper

1Write a query joining 2 tables to verify a specific business rule (e.g. order totals matching line items).
2Learn the difference between INNER and LEFT JOIN and when each fits a verification scenario.
3Practice writing a query to find data inconsistencies (e.g. orphaned records).