Tutorial Gateway

  • C Language
  • Java
  • R
  • SQL
  • MySQL
  • Python
  • BI Tools
    • Informatica
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • QlikView
  • Js

MySQL BETWEEN Operator

by suresh

The MySQL Between Operator returns the records (or rows) whose values are between the given two values or range.

For example, to find the Sales between Jan 2018 to Dec 2018, you can use this MySQL Between Dates.

MySQL Between Operator Syntax

The basic syntax of the MySQL Between operator can be written as:

SELECT Column_Names 
FROM Table_Name
WHERE Column_Value BETWEEN Value1 AND Value2

This operator displays the records available between the Value1 and Value2 Including them. It mean, Column_Value >= Value1 and Column_Value <= Value2.

In this article we will show you, How to use the MySQL Between Operator with an examples.

MySQL Between example

This simple example shows the working functionality of MySQL Between on Numerical values.

In the first statement, 2 is between 1 and 3, and 7 is between 1 and 9. So, it returns 1. In the next statement, 4 is not between 1 and 3 and 22 is not between 1 and 9 so, they return 0.

SELECT 2 BETWEEN 1 AND 3, 7 BETWEEN 1 AND 9;

SELECT 4 BETWEEN 1 AND 3, 22 BETWEEN 1 AND 9;

SELECT 2 BETWEEN 3 AND 1, 7 BETWEEN 9 AND 1;

OUTPUT

MySQL BETWEEN Operator 1

In this example, we will show you, How to use this Between operator on String data.

The first statement returns 1, 1 because b is between a and c , and t is between s and v. Within the second testament, it will convert the string 10 to int and check whether 8 is between 4 and 10.

SELECT 'b' BETWEEN 'a' AND 'c', 't' BETWEEN 's' AND 'v';

SELECT 8 BETWEEN 4 AND '10';

SELECT 8 BETWEEN 4 AND 'Hi';

OUTPUT

MySQL BETWEEN Operator 2

MySQL Between Operator On Numeric and String Data Example

For this MySQL Between operator demonstration, We are going to use the Customer table. The following screenshot shows you the data present inside this table

MySQL BETWEEN Operator 3

The following query returns the Customers whose Income is between 50000 and 80000. Or, whose Income is greater than or equal to 50000 and less than or equal to 80000.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE Income BETWEEN 50000 AND 80000;

OUTPUT

MySQL BETWEEN Operator 4

This MySQL Between operator example finds all the Customers whose Last Name is between Erickson and Suresh

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE `Last Name` BETWEEN 'Erickson' AND 'Suresh';

TIP: We can also use single character instead of writing the complete name. Because, this function considers the first character as a reference.

OUTPUT

MySQL BETWEEN Operator 5

MySQL Between Dates Example

In this MySQL Dates Between example, we will find all the Customers whose Hire Date is between 2009-01-01 and 2013-01-15

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE HireDate BETWEEN '2009-01-01 ' AND '2013-01-15';

OUTPUT

MySQL DATE BETWEEN 6

This MySQL Between Dates example find and returns all the Customers whose Hire Date is between 2009-01-01 and 2013-01-15 and Income between 60000 and 90000.

SELECT EmpID, 
`First Name`,
      `Last Name`,
      Qualification,
      Occupation,
      Income,
      Sales,
      HireDate
FROM `MySQL Tutorial`.customer
WHERE HireDate BETWEEN '2009-01-01 ' AND '2013-01-15' AND
Income BETWEEN 60000 AND 90000;

OUTPUT

MySQL DATE BETWEEN 7

Thank You for Visiting Our Blog

Placed Under: MySQL

Trending Posts

Java If Statement

Python LOG10

Tableau Dashboard Objects

SQL Restore Database

Difference between CTE, Temp Tables, Derived tables

Transfer SQL Server User Defined Functions in SSIS

SQL Upper and Lower Fucntions

Java Program to Check Prime Number

R ggplot2 Dot Plot

Power BI Slicer

  • C Programs
  • Java Programs
  • SQL FAQ’s
  • Python Programs
  • SSIS
  • Tableau
  • JavaScript

Copyright © 2019 | Tutorial Gateway· All Rights Reserved by Suresh

Home | About Us | Contact Us | Privacy Policy