Use SQL to Check a Business Rule Before UAT
Turn a business rule into a SQL query that finds the rows breaking it. Prove 'every order has a customer' by comparing counts and finding the order that fails, then check a second rule, on a sample online shop database.
Step by step
A business rule
- Every order has a customer. The requirement says every order must belong to a customer. Before user acceptance testing (UAT) starts, check this against the data. In SQL, you write a query that finds the rows that break the rule.
- The orders table. Each order in the orders table holds a customer_id. That customer_id should match a row in the customers table.
Count both sides
- Count the orders. Start simple. Count the orders with SELECT COUNT(*) AS orders FROM orders; It returns 1 row: 141 orders.
SELECT COUNT(*) AS orders FROM orders; - Count the matches. Now count only the orders that find their customer. A JOIN to customers ON c.customer_id = o.customer_id keeps only the orders with a matching customer_id. It returns 140. That's 1 fewer than the total, so 1 order has no customer. The rule is broken. Next, find which order it is.
SELECT COUNT(*) AS matched FROM orders AS o JOIN customers AS c ON c.customer_id = o.customer_id;
Find the rule breaker
- Find the order. Change the same query. Replace SELECT COUNT(*) AS matched with SELECT o.order_id, o.customer_id, so it lists the orders instead of counting them. Change JOIN to LEFT JOIN. Now orders with no matching customer stay in the result, with NULL in the customer columns. Then add WHERE c.customer_id IS NULL before the semicolon. This keeps only the orders where no customer was found. It returns 1 row: order 1141 points to customer 99, who isn't in the customers table. Log it as a defect before testing starts.
SELECT o.order_id, o.customer_idLEFT JOIN customersWHERE c.customer_id IS NULL;
A second rule
- Another rule. The same idea works for any rule. Every Shipped or Delivered order must have a shipped date. So look for orders WHERE status IN ('Shipped', 'Delivered') AND shipped_date IS NULL. It returns 0 rows. Nothing breaks this rule, so it passes. Save the query and its result as evidence for the test report.
SELECT order_id, status, shipped_date FROM orders WHERE status IN ('Shipped', 'Delivered') AND shipped_date IS NULL;
Recap
- Test rules with SQL. Turn each business rule into a query that finds the rows breaking it. 0 rows means the rule holds for the current data. Any rows that come back go in the defect log before UAT.
Common mistakes
- Using a plain JOIN to find missing customers. A plain (inner) JOIN drops orders that have no matching customer, so WHERE c.customer_id IS NULL finds nothing and the rule looks fine. Use LEFT JOIN so those orders stay in the result.
- Writing = NULL instead of IS NULL. In SQL, c.customer_id = NULL is never true, so the query always returns 0 rows and a broken rule looks like a pass. Always write IS NULL.
- Checking the orders column for NULL instead of the customers column. WHERE o.customer_id IS NULL only finds orders with a blank customer_id. It misses order 1141, which has the ID 99 but no matching customer. Check c.customer_id, the column from the joined table.
- Status values that don't match exactly. IN ('Shipped', 'Delivered') only matches those exact words. Depending on the database, 'shipped' or 'Shipped ' with a trailing space may not match. Run SELECT DISTINCT status FROM orders first to see the real values.
When a business analyst uses this
Before UAT sign-off, a business analyst turns each rule in the requirements log into one query that finds the rows that break it. They save each query and its row count as evidence, and log any rows that come back, like order 1141 with no customer, as defects for the team to fix before testing starts.
Questions people ask
How do I find orders without a matching customer in SQL?
LEFT JOIN orders to customers ON c.customer_id = o.customer_id, then add WHERE c.customer_id IS NULL. Every row that comes back is an order whose customer doesn't exist.
Why use LEFT JOIN instead of JOIN to find missing records?
A JOIN keeps only the rows that match on both sides, so the rows you're looking for disappear. A LEFT JOIN keeps every order and fills the customer columns with NULL when there is no match, so you can filter for them.
What does 0 rows mean when testing a business rule with SQL?
It means no row in the data breaks the rule right now, so the rule passes. It doesn't stop bad data from being added later, so run the query again before each test cycle.
