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.
- Write
SELECTcolumnsFROMtableWHEREcondition. A table name with dots in it is written in backticks, as on theFROMline of the worked example below. - One column can hold a group of related values.
vehicle_snapshot.modelreads the model out of such a group onfact_mot_test, and the name before the dot is the column. The Arrays and structs lesson covers these columns in full. - Text is compared exactly and case matters. Makes are stored in capitals, so the condition is
make = 'FORD'. fact_mot_testis partitioned by month oncompleted_date, and BigQuery refuses to run a query on it without a filter on that column. A range such ascompleted_date BETWEEN '2024-06-01' AND '2024-06-30'satisfies the rule and lets BigQuery skip the other months.- Combine conditions with
ANDandOR.ANDkeeps a row when both sides are true.ORkeeps a row when either side is true.
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.