SQL and MySQL course Β· Module 3: Filtering (WHERE)

Ranges: BETWEEN

1 min read Β·
In this lesson3

You already know lists of values. Now you will learn a second elegant shortcut: the BETWEEN operator, which selects values from a range from-to.

BETWEEN - a from-to range

The BETWEEN operator selects values from a range, together with both ends:

SELECT * FROM ksiazki
WHERE rok_wydania BETWEEN 1900 AND 2000;

This means: "Books published from 1900 to 2000 inclusive". It is a convenient replacement for two comparison conditions:

SELECT * FROM ksiazki
WHERE rok_wydania >= 1900 AND rok_wydania <= 2000;

Both ends are included

Remember the most important thing: BETWEEN always includes both ends of the range. The condition BETWEEN 1900 AND 2000 covers both the year 1900 and the year 2000.

Great for numbers and prices

BETWEEN works perfectly for years, prices or any numbers:

SELECT * FROM ksiazki
WHERE cena BETWEEN 20 AND 50;

This means: "Books priced from 20 to 50 coins inclusive". Thanks to IN and BETWEEN your queries will be shorter and more readable than the most beautifully written scroll.

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. Which rows will the condition return: WHERE rok_wydania BETWEEN 1900 AND 2000 ?

  2. 2. Which condition gives the same result as: WHERE cena BETWEEN 20 AND 80 ?

Hands-on tasks in the game

  • Code editor

    Write a query that retrieves all columns from the ksiazki table for books published in the years from 1900 to 2000 inclusive. Use the BETWEEN operator.

  • Code editor

    Write a query that retrieves all columns from the ksiazki table for books whose cena falls within the range from 20 to 50 inclusive. Use the BETWEEN operator.

  • Horizontal ordering

    Arrange in one line the year range condition with the BETWEEN operator:

  1. QuestionThe BETWEEN operator
  2. QuestionBETWEEN vs comparison operators
  3. Coding taskBooks from the 20th century
  4. Coding taskMid price shelf
  5. Put in orderA year range with BETWEEN

Useful articles