sql between - sql between dates - sql between numbers - sql between range - sql between text

SQL Between — Limiting Rows Returned

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;
kindlength
red70000
orange62000
yellow58000
green53000

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;
kindlength
radio10000000000000000
microwave2500000
infrared100000
ultraviolet35000
x-ray1000
gamma1

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';
namebirth
Salvador Dali1904-05-11
Willem de Kooning1904-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.

Start Learning

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' );