SELECT and WHERE

The MOT is the yearly safety test that most cars in Great Britain must pass once they are three years old, and the government publishes every result, hundreds of millions of tests in tables such as fact_mot_test. You query a sample of them here, priced as if it were the whole thing, so a narrow filter and a short column list keep the answer readable and the query cheap.

How it works

fact_mot_test has one row per MOT test, with the make, the result and the date. WHERE drops the tests that do not match your condition. SELECT then picks the columns you want from the tests that are left.

Common mistake

A condition such as make = 'FORD' OR test_result = 'FAILED' looks like the question and answers a different one: it returns every Ford test plus every failed test of any other make. Read each OR against the wording of the question before you run it.

Worked example

SELECT vehicle_id, completed_date, test_result
FROM `learn.mot.fact_mot_test`
WHERE make = 'HONDA'
  AND test_result = 'PASSED'
  AND completed_date BETWEEN '2024-03-01' AND '2024-03-31'

All three conditions must be true, so the result holds the Honda tests that passed in March 2024.

The date range is the date filter that lets BigQuery read March only, because the table is partitioned by month. The other two conditions then filter the rows inside it.

BigQuery charges for the bytes a query reads, so reading March only costs less than reading every month.

All BigQuery lessons