Lessons › SQL › Deep Dive: Data Validation

SQL lesson

Referential integrity checks across tables

Referential integrity checks verify that every ID pointing to another table actually exists there, so your data stays consistent and queries return trustworthy results.

Example

Checking Broken Retail Relationships Across Tables

SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

Result:

Step by step

1. After SELECT, list the column names you want, separated by commas.

   SELECT o.order_id, o.customer_id

2. FROM tells SQL which table to pull the data from.

   SELECT o.order_id, o.customer_id
   FROM orders o

3. LEFT JOIN keeps every row from the left table, even when no match exists.

   SELECT o.order_id, o.customer_id
   FROM orders o
   LEFT JOIN customers c
     ON o.customer_id = c.customer_id

Practice

Try each one in your head first, then open the answer.

SELECT * FROM orders WHERE order_id NOT IN (SELECT order_id FROM order_details)