SQL reference

Each keyword has a short explanation, a working example on the practice shop data, and the mistakes people make most.

• SELECTChoose which columns to read from a table, and in what order.
• WHEREKeep only the rows that match a condition. It runs before grouping.
• ORDER BYSort the result rows. Without it, the order is not guaranteed.
• DISTINCTRemove duplicate rows from the result, so each value or combination appears once.
• LIKEMatch text against a pattern. % stands for any run of characters, _ for exactly one.
• INTest whether a value is one of a list. Shorter than several OR conditions.
• NULLA missing value. It is not zero and not an empty string, and it never equals anything, not even NULL.
• COUNT, SUM and AVGAggregate functions: they turn many rows into one value, or one value per group.
• GROUP BYMake one result row for each distinct value (or combination of values), then aggregate each group.
• HAVINGFilter groups after they are aggregated. WHERE filters rows before.
• JOIN (INNER and LEFT)Combine rows from two tables where a key matches. An inner join keeps only the matches; a left join keeps every row of the left table.
• INSERT, UPDATE and DELETEChange the data: add rows, change values in rows, and remove rows.