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.

 

Next chapter: Working with NULL >>

Previous chapter: Advanced text searching using full-text indexes

Jump to:

 

Home: Table of Contents

Follow us on Identi.ca or Twitter

Username:   Password:
Create Account | About TuxRadar