Skip to main content

Module 7 · SQL for QA

Joins and verifying stored data safely

Combine tables to check consistency, without putting real data at risk.

About 11 min reading and 20 min practice (estimates)

Loading progress…

Read

Learning objectives

  • Use INNER JOIN and LEFT JOIN and explain the difference.
  • Write consistency checks with GROUP BY and HAVING.
  • Follow safe habits when querying shared databases.

Explanation

INNER JOIN returns rows that have a match in both tables. LEFT JOIN returns every row from the left table, with NULLs where the right table has no match — useful for finding things that are missing.

-- Customers with no orders
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
-- 3 | Test Customer C

Safe habits

  • Use a read-only account where possible; never query production without permission.
  • Start read-only transactions when exploring: BEGIN READ ONLY; … ROLLBACK;
  • Add LIMIT while exploring, and filter by test data you created.
  • Don't copy personal data into reports; share counts or test-record ids instead.

Example: orders with their customer's wilayat

SELECT o.id, c.name, coalesce(c.wilayat, '(none)') AS wilayat, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;
-- 101 | Test Customer A | Muscat | 16.500
-- 102 | Test Customer A | Muscat | 23.000
-- 103 | Test Customer B | (none) |  6.500
-- 104 | Test Customer B | (none) | 12.000

Common mistakes

  • Using INNER JOIN when looking for missing data, which hides exactly the rows you need.
  • Joining on the wrong column and multiplying rows.
  • Running UPDATE or DELETE "just to set up a test" in a shared environment.

Try

Practise what you just read. Answers are checked here; nothing is graded publicly or counts toward Bug Arena.

Exercise

Write a query that finds orders whose stored total doesn't equal the sum of quantity × unit price of their items. (Ignore delivery and discounts for this exercise.)

Write your answer first, then compare it with the sample solution. It stays on this page only and isn't sent anywhere.

Review

Check yourself — you should now be able to:

  • Use INNER JOIN and LEFT JOIN and explain the difference.
  • Write consistency checks with GROUP BY and HAVING.
  • Follow safe habits when querying shared databases.

References

Continue

When you’re ready, mark the lesson complete in the Try step and move on.

Marked as read when you reach the end of the lesson.