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;
kind | length |
red | 70000 |
orange | 62000 |
yellow | 58000 |
green | 53000 |
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;
kind | length |
radio | 10000000000000000 |
microwave | 2500000 |
infrared | 100000 |
ultraviolet | 35000 |
x-ray | 1000 |
gamma | 1 |
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;
kind |
blue |
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';
name | birth |
Salvador Dali | 1904-05-11 |
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.
Conclusion
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.
To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.
SQL Sample Table Code
The following will build and populate the sample data referred to in this blog post. Test SQL interactively at SQL Fiddle or ExtendsClass.
CREATE TABLE em_spectra (
kindVARCHAR(15),
lengthBIGINT -- 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 (
nameVARCHAR(55),
birthDATE
);
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' );