Between Operator MySQL Essential Training

Between Operator MySQL Essential Training

MYSQL

Between Operator MySQL Essential Training




In this lesson we are gonna learn some thing about what is the Between Operator. The Lesson Between Operator MySQL Essential Training will gives you an clear idea about the Between Operator. Lets take a look in brief by using the upcoming live example. Here we use the new table called products.
The BETWEEN operator allows you to specify a range to test. In some cases we often use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements. The following is the syntax for the Between Operator.

The expr is the expression to test in the range that is defined by begin_expr and end_expr. All three expressions: expr, begin_expr, and end_expr must have the same data type.
The BETWEEN operator uses the boolean variables to test the condition. If value of the expr is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of the end_expr otherwise it returns zero.
The NOT BETWEEN operator does the operation reverse what the BETWEEN is performs. In Between Operator MySQL Essential Training lesson NOT BETWEEN returns true if the value of expr is less than (<) the value of the begin_expr or greater than the value of the value of end_expr otherwise it returns 0.
If any expression is NULL, the BETWEEN operator returns a NULL value. In case you want to specify an exclusive range, you use the greater than (>) and less than (<) operators.
Between Operator MySQL Essential Training
Let’s practice with some examples of using the BETWEEN operator.
MySQL BETWEEN with number examples
See the following products table in the sample database:
Between Operator MySQL Essential Training
Suppose if we want to find products whose prices are within the ranges of 10 and 20, you can use the BETWEEN operator as the following query:

Between Operator MySQL Essential Training




The above is gives you an result for products between the price range from 40 to 100.
In this Between Operator MySQL Essential Training lesson can achieve the same result by using the greater than or equal (>=) and less than or equal ( <= ) operators as the following query:

Between Operator MySQL Essential Training
To find the product whose buy price is not between 20 and 100, you combine the BETWEEN operator with the NOT operator as follows:

Between Operator MySQL Essential Training
The above query returns the values of even the lesser and greater prices except the given range in the query. In this Between Operator MySQL Essential Training we give this as a special tips to get exact values from the large databases.
You can rewrite the query above using the less than (>), greater than (>), and logical operators (AND) as the following query.

Between Operator MySQL Essential Training
Between Operator MySQL Essential Training
When you use the BETWEEN operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.
For example, to get the orders whose required dates are from 07/01/2016 to 07/31/2016, you use the following query:

Because the data type of the required date column is DATE so we used the cast operator to convert the literal strings ‘2016-06-01 ‘ and ‘2016-06-31 ‘ to the DATE data type.
we hope in this tutorial, you have learned how to use the BETWEEN operator to test if a value falls within a range of values.




Leave a Reply