🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

【SQL BETWEEN Clause】Mastering Range Searches! A Smarter Way to Specify Ranges for Numbers and Dates

In web and application development, you very often need to extract data that falls within a certain range, such as "products with a price between $10 and $50," or "users who registered between June 1st and June 30th."

When writing such a condition in a WHERE clause, how would you do it?

WHERE price >= 1000 AND price <= 5000

Of course, you can specify a range using comparison operators (>=, <=) and AND like this. However, SQL provides a dedicated command for making this "range search" more intuitive and cleaner. That's our main topic for today: the BETWEEN clause.

This article will thoroughly explain everything from the basic usage of the BETWEEN clause, to its application with date ranges, and its negative form, NOT BETWEEN, all with copy-and-paste code. Let's graduate from clunky comparison operator chains and acquire the skill to write smart, easy-to-read SQL for everyone!

Preparation: Let's Get Our Order Data Ready

To try out range searches, we first need some data to work with. This time, we'll create an orders table that mimics an e-commerce site's order history. By including data with various price points and order dates, it will be easier to see the effects of the BETWEEN clause.

-- Drop the orders table if it exists (for repeatable testing)
DROP TABLE IF EXISTS orders;

-- Create a new orders table
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  product_name TEXT NOT NULL,
  price INTEGER NOT NULL,
  order_date DATE NOT NULL,
  quantity INTEGER NOT NULL
);

-- Insert initial data
INSERT INTO orders (id, product_name, price, order_date, quantity) VALUES
(1, 'Amazing Ballpoint Pen', 200, '2025-06-05', 3),
(2, 'Magic Notebook', 500, '2025-06-10', 5),
(3, 'Ultimate Rice Cooker', 30000, '2025-06-15', 1),
(4, 'Miracle Apple', 980, '2025-06-20', 10),
(5, 'USB-C Hub', 4500, '2025-06-25', 2),
(6, 'Traditional Brush', 12000, '2025-06-30', 1),
(7, 'Premium Chocolate', 3500, '2025-07-01', 4);

Now we're all set to extract data using various ranges.

【The Basics】Specifying a Numerical Range with the BETWEEN Clause

The basic syntax of the BETWEEN clause is very straightforward.

WHERE column_name BETWEEN minimum_value AND maximum_value;

The most important point here is that the range for the `BETWEEN` clause is inclusive, meaning it includes both the minimum and maximum values specified. In other words, WHERE price BETWEEN 1000 AND 5000 means exactly the same thing as WHERE price >= 1000 AND price <= 5000. Be sure to remember this "inclusive of both ends" point.

Scenario: "Find all products with a price between 1,000 and 5,000."

SELECT * FROM orders
WHERE price BETWEEN 1000 AND 5000;

Result:

id | product_name      | price | order_date | quantity
---|-------------------|-------|------------|----------
5  | USB-C Hub         | 4500  | 2025-06-25 | 2
7  | Premium Chocolate | 3500  | 2025-07-01 | 4

The code is cleaner than using two comparison operators, and the intent of "a range from 1,000 to 5,000" is conveyed more directly, isn't it?

【Application】Specifying a Date Range with the BETWEEN Clause

A convenient feature of the BETWEEN clause is that it can be used not only for numbers but also for specifying ranges of dates (DATE type) and datetimes (DATETIME type). Since requirements to extract data for a specific period are very common in web applications, this is a frequently used technique.

Scenario: "Find all orders placed during June 2025."

In this case, we specify the start of the range as "2025-06-01" and the end as "2025-06-30".

SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-01' AND '2025-06-30';

Result:

id | product_name          | price | order_date | quantity
---|-----------------------|-------|------------|----------
1  | Amazing Ballpoint Pen | 200   | 2025-06-05 | 3
2  | Magic Notebook        | 500   | 2025-06-10 | 5
3  | Ultimate Rice Cooker  | 30000 | 2025-06-15 | 1
4  | Miracle Apple         | 980   | 2025-06-20 | 10
5  | USB-C Hub             | 4500  | 2025-06-25 | 2
6  | Traditional Brush     | 12000 | 2025-06-30 | 1

As you can see, orders from June 1st and June 30th are properly included in the result. This is also thanks to the "inclusive of both ends" nature of the BETWEEN clause.

【The Opposite Condition】Specifying What's Outside a Range with NOT BETWEEN

If you want to find things that are **outside** a specified range, you use the NOT BETWEEN clause.

There's one point to be careful about here. While price BETWEEN 1000 AND 5000 meant "greater than or equal to 1000 AND less than or equal to 5000," price NOT BETWEEN 1000 AND 5000 means "less than 1000 **OR** greater than 5000" (price < 1000 OR price > 5000). In other words, the boundary values themselves are not included in the result.

Scenario: "Find all products that are either very cheap (under 1,000) or very expensive (over 5,000)."

SELECT * FROM orders
WHERE price NOT BETWEEN 1000 AND 5000;

Result:

id | product_name          | price | order_date | quantity
---|-----------------------|-------|------------|----------
1  | Amazing Ballpoint Pen | 200   | 2025-06-05 | 3
2  | Magic Notebook        | 500   | 2025-06-10 | 5
3  | Ultimate Rice Cooker  | 30000 | 2025-06-15 | 1
4  | Miracle Apple         | 980   | 2025-06-20 | 10
6  | Traditional Brush     | 12000 | 2025-06-30 | 1

Although our sample data doesn't have products priced at exactly 1,000 or 5,000, remember that even if they existed, they would not be included in this result.

【Interactive Playground】Master Range Searches by Running SQL in Your Browser!

Now, it's time to turn knowledge into a skill! Copy the entire HTML code below, save it as a file like sql_between_test.html, and open it in your browser. Your own personal SQL environment will launch, pre-loaded with the orders table we've been using in this article.

Try changing the numerical or date ranges to get a feel for the "inclusive of both ends" nature of the BETWEEN clause. Give NOT BETWEEN a try as well and see how the results change!

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>SQL BETWEEN Clause Online Playground</title>
  <style>
    body { font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", Arial, sans-serif; line-height: 1.7; color: #333; max-width: 800px; margin: 2rem auto; padding: 0 1rem; }
    h1 { color: #16a085; }
    textarea { width: 100%; height: 180px; font-family: "SF Mono", "Consolas", monospace; font-size: 16px; padding: 12px; border: 1px solid #ccc; border-radius: 6px; box-sizing: border-box; margin-bottom: 1rem; }
    button { background-color: #1abc9c; color: white; border: none; padding: 12px 22px; font-size: 16px; border-radius: 6px; cursor: pointer; transition: background-color 0.2s; }
    button:hover { background-color: #16a085; }
    button:disabled { background-color: #bdc3c7; cursor: not-allowed; }
    #result-container { margin-top: 2rem; border: 1px solid #ddd; padding: 1rem; border-radius: 6px; background: #fdfdfd; min-height: 50px; }
    #error-message { color: #e74c3c; font-weight: bold; }
    table { border-collapse: collapse; width: 100%; margin-top: 1rem; }
    th, td { border: 1px solid #ddd; padding: 10px; text-align: left; }
    th { background-color: #f2f2f2; }
    tr:nth-child(even) { background-color: #f9f9f9; }
  </style>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/sql-wasm.js"></script>
</head>
<body>

  <h1>Let's Try SQL!</h1>
  <p>Enter an SQL statement in the textarea below and click the "Execute" button. Try out different ranges!</p>

  <textarea id="sql-input">-- Find orders placed between June 10th and June 25th
SELECT * FROM orders
WHERE order_date BETWEEN '2025-06-10' AND '2025-06-25';
  </textarea>
  
  <button id="execute-btn">Execute</button>
  
  <div id="result-container">
    <p id="error-message"></p>
    <div id="result-output"></div>
  </div>

  <script>
    const sqlInput = document.getElementById('sql-input');
    const executeBtn = document.getElementById('execute-btn');
    const errorMsg = document.getElementById('error-message');
    const resultOutput = document.getElementById('result-output');

    let db;

    async function initDb() {
      executeBtn.disabled = true;
      executeBtn.textContent = 'DB Initializing...';
      try {
        const SQL = await initSqlJs({
          locateFile: file => `https://cdnjs.cloudflare.com/ajax/libs/sql.js/1.10.3/${file}`
        });
        db = new SQL.Database();
        
        const setupSql = `
          DROP TABLE IF EXISTS orders;
          CREATE TABLE orders (id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, price INTEGER NOT NULL, order_date DATE NOT NULL, quantity INTEGER NOT NULL);
          INSERT INTO orders (id, product_name, price, order_date, quantity) VALUES
          (1, 'Amazing Ballpoint Pen', 200, '2025-06-05', 3),
          (2, 'Magic Notebook', 500, '2025-06-10', 5),
          (3, 'Ultimate Rice Cooker', 30000, '2025-06-15', 1),
          (4, 'Miracle Apple', 980, '2025-06-20', 10),
          (5, 'USB-C Hub', 4500, '2025-06-25', 2),
          (6, 'Traditional Brush', 12000, '2025-06-30', 1),
          (7, 'Premium Chocolate', 3500, '2025-07-01', 4);
        `;
        db.run(setupSql);
        
        executeBtn.disabled = false;
        executeBtn.textContent = 'Execute';
        resultOutput.innerHTML = '<p>Ready! Feel free to try out your own SQL queries.</p>';

      } catch (err) {
        errorMsg.textContent = 'Failed to initialize database: ' + err.message;
        console.error(err);
      }
    }

    function executeSql() {
      if (!db) return;
      
      const sql = sqlInput.value;
      errorMsg.textContent = '';
      resultOutput.innerHTML = '';

      try {
        const results = db.exec(sql);
        if (results.length === 0) {
          resultOutput.innerHTML = '<p>Query executed successfully, but no result set was returned.</p>';
          return;
        }
        
        results.forEach(result => {
          const table = document.createElement('table');
          const thead = document.createElement('thead');
          const tbody = document.createElement('tbody');
          
          const headerRow = document.createElement('tr');
          result.columns.forEach(colName => {
            const th = document.createElement('th');
            th.textContent = colName;
            headerRow.appendChild(th);
          });
          thead.appendChild(headerRow);
          
          result.values.forEach(row => {
            const bodyRow = document.createElement('tr');
            row.forEach(cellValue => {
              const td = document.createElement('td');
              td.textContent = cellValue === null ? 'NULL' : cellValue;
              bodyRow.appendChild(td);
            });
            tbody.appendChild(bodyRow);
          });
          
          table.appendChild(thead);
          table.appendChild(tbody);
          resultOutput.appendChild(table);
        });

      } catch (err) {
        errorMsg.textContent = 'SQL Error: ' + err.message;
        console.error(err);
      }
    }

    executeBtn.addEventListener('click', executeSql);
    
    initDb();
  </script>
</body>
</html>

Points to Watch Out For: Handling Datetimes and Value Order

While the BETWEEN clause is simple and powerful, there are a few points to be aware of.

Conclusion

In this article, we learned about the BETWEEN clause, which allows for intuitive range searches for numbers and dates.

By mastering the BETWEEN clause, the SQL you write will become simpler and its intent clearer. Take a step beyond simple combinations of comparison operators and add a more refined data extraction technique to your toolbox!

Related Templates