SQL BETWEEN

BETWEEN ... AND

The BETWEEN ... AND operator selects a range of data between two values. These values can be numbers, text, or dates.

SELECT columnName FROM tableName WHERE columnName BETWEEN value1 AND value2;

Some Examples

The "Books" table:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005
XML Language	M. Salim	Knowledge Press	2000

Example

To display the books published between 2000 and 2004 (including 2000 and 2004), use the following SQL:

SELECT * FROM Books WHERE Year BETWEEN 2000 AND 2004;

The result is:

Title	Author	Publisher	Year
Java 2	L. Johnston	Fast Press	2002
Linux and Unix	J. Sam	Fast Press	2004
XML Language	M. Salim	Knowledge Press	2000

Important: The BETWEEN...AND operator is treated differently in different databases. With some databases a book published in 2000 or 2004 will not be listed (BETWEEN...AND only selects fields that are between and excluding the test values). With some databases a book published in 2000 or 2004 will be listed (BETWEEN...AND selects fields that are between and including the test values). With other databases a book published in 2000 will be listed, but that published in 2004 will not be listed (BETWEEN...AND selects fields between the test values, including the first test value and excluding the last test value). Therefore: Check how your database treats the BETWEEN...AND operator.

Example

To display the books published outside the range of dates used in the previous example(i.e. outside the range 2000-2004), we use the NOT operator:

SELECT * FROM Books WHERE Year NOT BETWEEN 2000 AND 2004;

The result is:

Title	Author	Publisher	Year
الدورة الدموية	إبن النفيس	دار العلم	1650
Operating Systems	M. Stone	Coriolis	2005
Web Programming	K. Yariv	East Edition	2005

Test your SQL Skills

To test your SQL skills click here.