9.3.19 Range matching: between() and in()This is NOT the latest copy of this book; click here for the latest version.
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 see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
