Restricting returned data

The basic restrictions which can be carried out on the returned data are applied by using a WHERE clause applied to the data in one or more of the requested columns. The comparison operators we consider here are "=", "<>" ("not equal to"), ">", "<", ">=", "<=", BETWEEN and LIKE, noting that "equals" and "not equals" are not written as typical in other programming languages ("!=" may work in some RDBMSs but it is not standard).

For our simplest case, we ask for all of the instruments which have not been maintained since the 1st of January 2020.

And the data returned are:

Dates are typically record as YYYY-MM-DD and are enclosed in single quotes in the WHERE clause. Note that the row with a NULL value for last_maintenance_date—either because it has never been maintained or the data were not recorded—is not returned as NULL does not equate to anything; to check for NULL status we must use IS NULL or IS NOT NULL. This is important to remember, and is often given as one of the reasons that NULL values can create confusion!

We can combine restrictions in the WHERE clause with the simple boolean operators AND, OR and NOT, using parentheses to preserve intended execution order. If we were interested in our Magnotron_2016 maintenance in a five year period in time between 1st January 2016 and 1st January 2021 and also want to get the information on the Magnotron_2016s for which we have no data on last maintenance period at all, we can combine our restrictions:

Notice that we don't get the row for the Magnotron_2016x! Assuming that we want that, too, for string matching we can use LIKE with the wildcards '_' (for a single character) and '%' (for any number of characters). So, to get the same result set for any instrument model starting with "Magnotron_2016" (i.e., for this dataset, it would also include the second row) we could just run:

Note that if we were certain that all members of the Magnotron_2016 family of instruments would have a single letter, like 'x', added then we could have simply used "instrument_model LIKE 'Magnotron_2016_'" instead.

Ordering returned data

It's fairly common that we would like the returned data to be ordered by one or more fields. This is simply achieved with ORDER BY after the WHERE clause, so if we wanted the instrument_ids for the Magnotron_2016-like models sorted by last_maintenance_date ascending, we would use

Without the IS NOT NULL constraint on last_maintenance_date, the row with the NULL value in that column would actually be returned, but where it turns up in the order will depend on the RDBMS you use! In some it will be considered to sort like the highest possible value for that column, and in others it will sort as the lowest possible value. If this could be a problem it should be handled either by consulting the RDBMS documentation to work out where they'll appear—but this could be a problem if you moved to another RDBMS—or else by specifically excluding such data with IS NOT NULL as done here. Again, take care with NULLable columns!

Finally, to have the data sorted from largest to smallest, use DESC instead of ASC. If you don't use either, the RDBMS will apply a default ordering which may depend on the RDBMS itself; it is generally best to avoid default behaviors when there is an explicit command available.