Lessons › SQL › Deep Dive: Data Validation

SQL lesson

Cross-field validation

Cross-field validation checks whether values in different columns actually agree with each other, not just whether each column looks clean on its own.

Example

Checking Retail Fields Against Each Other

SELECT order_id, order_date, ship_date, quantity, price, revenue,
  CASE
    WHEN ship_date < order_date THEN 'Ship date before order date'
    WHEN ROUND(revenue, 2) <> ROUND(quantity * price, 2) THEN 'Revenue mismatch'
    ELSE 'OK'
  END AS validation_flag
FROM orders
WHERE ship_date < order_date
   OR ROUND(revenue, 2) <> ROUND(quantity * price, 2)

Result:

Step by step

1. Run the query without ROUND so you can see what the unfiltered values look like.

   SELECT order_id, order_date, ship_date, quantity, price, revenue,
     CASE
       WHEN ship_date < order_date THEN 'Ship date before order date'
       WHEN revenue <> ROUND(quantity * price, 2) THEN 'Revenue mismatch'
       ELSE 'OK'
     END AS validation_flag
   FROM orders
   WHERE ship_date < order_date
      OR ROUND(revenue, 2) <> ROUND(quantity * price, 2);

2. ROUND rounds the value to the nearest integer (or to N decimal places with a second argument).

   SELECT order_id, order_date, ship_date, quantity, price, revenue,
     CASE
       WHEN ship_date < order_date THEN 'Ship date before order date'
       WHEN ROUND(revenue, 2) <> ROUND(quantity * price, 2) THEN 'Revenue mismatch'
       ELSE 'OK'
     END AS validation_flag
   FROM orders
   WHERE ship_date < order_date
      OR ROUND(revenue, 2) <> ROUND(quantity * price, 2);

3. AS gives the ROUND result a friendly column name so the output reads cleanly.

   SELECT order_id, order_date, ship_date, quantity, price, revenue,
     CASE
       WHEN ship_date < order_date THEN 'Ship date before order date'
       WHEN ROUND(revenue, 2) AS round_result <> ROUND(quantity * price, 2) THEN 'Revenue mismatch'
       ELSE 'OK'
     END AS validation_flag
   FROM orders
   WHERE ship_date < order_date
      OR ROUND(revenue, 2) <> ROUND(quantity * price, 2);

Practice

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

SELECT * FROM orders WHERE customer_id < order_id

SELECT * FROM store_sales WHERE store_id < sale_id

SELECT * FROM products WHERE brand < product_id

SELECT * FROM customers WHERE full_name < customer_id

SELECT * FROM returns WHERE order_id < return_id