Range matching

Although there is a lot you can do with standard operators like <, >, <=, etc, they are clunky to work with when you want to specify an exact range of items you want to check for. For example, the query to return all people with an age between 16 and 21 or 25 and 29 looks like this using the standard operators:

SELECT * FROM people WHERE (Age >= 16 AND Age <= 21) OR (Age >= 25 AND Age <= 29);

Using the between() function you can specify the low- and high-point a little more easily, giving the following query:

SELECT * FROM people WHERE Age BETWEEN(16,21) OR Age BETWEEN(25,29);

The two queries are functionally the same (between(16,21) matches 16, 17, 18, 19, 20, and 21; it is inclusive), and there is not really any big size difference between the two. However, the second query is much easier to read, as I think you will agree, because of the lack of symbols.

The other important function available here is in(), which allows you to specify the exact range of possibilities that will be matched against. Using in() to specify a range of constants is very, very fast, particularly if you specify a range of integers. Here are a couple of examples:

SELECT * FROM people WHERE Age IN (19, 20, 21);
SELECT * FROM people WHERE FirstName IN ('Sam', 'Bill', 'Patricia');

Using in() is a great way to avoid having to use multiple ORs in your WHERE clause.

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Working with NULL >>

Previous chapter: Advanced text searching using full-text indexes

Jump to:

Home: Table of Contents