Relational databases store information in tables — in columns that describe aspects of an item and rows which tie together the columns. The SQL Between clause modifies a SQL Select statement by filtering values between a top and bottom boundary, which may be numbers, text, dates. The SQL Between operator is inclusive, that is to say, begin and end values are included.
The syntax of the SQL Between clause is:
SELECT column1, column2, … FROM table WHERE columnX BETWEEN value1 and value2 ;
To demonstrate the SQL Between clause we’ll process values from the electromagnetic radiation spectrum, which includes the colors we see.
Photons, mass-less particles of energy, vibrate in a wave motion from some event which has moved them, like waves in the surface of a pond powered by the dropping of a rock. The more energetic the event, the faster the wave motion (which we can describe by energy, wavelength, or frequency).
Our eyes see colors because our retinas react to the photons. The entire palette of colors, when ordered by length, looks just like a rainbow:
Depending on our cultural convention, what we visualize when we hear “red” may vary a bit, but they’re all related by the length of the photon’s vibration.
SQL Between — By Number
At the bottom of this blog entry is the SQL source code needed to create a table called em_spectra, which contains the parts of the electromagnetic spectrum and the lengths of the wavelengths measured in femtometers (a quadrillionth of a meter; 1,000,000,000,000,000 to the meter).
To retrieve the wavelengths of electromagnetic radiation that fall between specified numeric values, use:
SELECT * FROM em_spectra WHERE length BETWEEN 50000 AND 70000;
Some database implementations provide for a shorthand, so the clause
WHERE length >= 50000 AND <= 70000 may also work.
To get all the wavelengths except for the colors we can see, use the
NOT BETWEEN syntax:
SELECT * FROM em_spectra WHERE length NOT BETWEEN 42000 AND 70000;
SQL Between — By Text
Information can also be retrieved by specifying text values for the SQL Between clause. To retrieve wavelengths by name (and have them conveniently sorted alphabetically):
SELECT kind FROM em_spectra WHERE kind BETWEEN 'a' AND 'c' ORDER BY kind;
Note please that this is similar to another SQL construct which matches a letter range:
SELECT * FROM examples WHERE words LIKE '[a-c]%' ;
SQL Between — By Date
To retrieve 1904 birthdays from the sample data table, artists (source code below), use:
SELECT * FROM artists WHERE birth BETWEEN '1904-01-01' AND '1904-12-31';
|Willem de Kooning||1904-04-24|
Note please that providing SQL Between with a nonsensical date range, like
BETWEEN '1904-12-31' AND '1904-01-01' (the latter date occurs before the former date) will simply return no rows rather than raise an error message. Some sanity-checking in your program code is warranted.
Covered has been the ways numbers, text, and dates can be filtered by the SQL Between clause, as well as the
NOT BETWEEN option, as well as some related tips.
SQL Sample Table Code
CREATE TABLE em_spectra ( kind VARCHAR(15), length BIGINT -- in femtometers ); INSERT INTO em_spectra VALUES ( "radio", 1E+16 ), ( "microwave", 2500000 ), ( "infrared", 100000 ), ( "red", 70000 ), ( "orange", 62000 ), ( "yellow", 58000 ), ( "green", 53000 ), ( "blue", 47000 ), ( "violet", 42000 ), ( "ultraviolet", 35000 ), ( "x-ray", 1000 ), ( "gamma", 1 ); CREATE TABLE artists ( name VARCHAR(55), birth DATE ); INSERT INTO artists VALUES ( 'Andrew Wyeth', '1917-07-12' ), ( 'Barbara Hepworth', '1903-01-10' ), ( 'Caravaggio', '1573-09-28' ), ( 'Claude Monet', '1840-11-14' ), ( 'Clementine Hunter', '1887-01-19' ), ( 'Diego Rivera', '1886-12-08' ), ( 'Edouard Manet', '1832-01-23' ), ( 'Edvard Munch', '1863-12-12' ), ( 'Elaine de Kooning', '1920-03-12' ), ( 'Frida Kahlo', '1907-07-06' ), ( 'Georges Seurat', '1859-12-02' ), ( 'Georgia O’Keeffe', '1887-11-15' ), ( 'Grandma Moses', '1860-09-07' ), ( 'Grant Wood', '1892-02-13' ), ( 'Helen Frankenthaler', '1928-12-12' ), ( 'Henri Matisse', '1869-12-31' ), ( 'Jackson Pollock', '1912-01-28' ), ( 'James Abbott McNeil Whistler', '1834-07-11' ), ( 'Jan Vermeer', '1632-10-31' ), ( 'Joan Miro', '1893-04-20' ), ( 'Keith Haring', '1958-05-04' ), ( 'Leonardo da Vinci', '1452-04-15' ), ( 'M.C. Escher', '1889-06-17' ), ( 'Marc Chagall', '1887-07-07' ), ( 'Mark Rothko', '1903-09-25' ), ( 'Max Ernst', '1891-04-02' ), ( 'Michelangelo Buonarroti', '1475-03-06' ), ( 'Pablo Picasso', '1881-10-25' ), ( 'Paul Cezanne', '1839-01-19' ), ( 'Paul Gauguin', '1848-06-07' ), ( 'Peter Paul Rubens', '1577-06-28' ), ( 'Pierre Auguste Renoir', '1841-02-25' ), ( 'Rembrandt van Rijn', '1606-07-15' ), ( 'Roy Lichtenstein', '1923-10-27' ), ( 'Salvador Dali', '1904-05-11' ), ( 'Vincent van Gogh', '1853-03-30' ), ( 'Willem de Kooning', '1904-04-24' ), ( 'Winslow Homer', '1836-02-24' );