Sql (Not) Between Operator
The SQL BETWEEN operator is used to fetch or extract values within a given range.
The SQL BETWEEN keyword is a conditional based operator, that meet a condition falling between a specified range of given values.
It can work with any type of data like numbers, text, or dates.
Related Links
SQL BETWEEN Syntax
The below syntax is used to define between operator in SQL WHERE clause:
SELECT column_name1, column_name2 FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Note: value1 and value2 must be the same datatype and It cannot be different.
Sample Database Table - Books
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | Sql Server Concurrency | 136.33 | 2006 | Security | Padmavathi |
2 | Troubleshooting SQL Server | 70 | 2009 | Optimization | Keshavan |
3 | The SQL Programming Language | 84.22 | 2008 | Optimization | Dharan |
4 | Sql Server Interview Questions | 90 | 2015 | Administration | Varshini Kutty |
SQL BETWEEN Operator Example With Numbers
The following SQL SELECT statement selects the all books with a BookPrice Between "100" AND "200", in the "Books" table:
SELECT * FROM Books WHERE
BookPrice BETWEEN 100 AND 200;
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
1 | Sql Server Concurrency | 136.33 | 2006 | Security | Padmavathi |
It checks for numeric value:
- Value1:- Greater than or equal
- Value2:- Less than or equal
SQL NOT BETWEEN Operator Example With Numbers
The SQL BETWEEN condition can use with the SQL NOT operator.
To display the books outside the range of the previous example, use NOT BETWEEN:
SELECT * FROM Books WHERE
BookPrice NOT BETWEEN 100 AND 200;
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | Troubleshooting SQL Server | 70 | 2009 | Optimization | Keshavan |
3 | The SQL Programming Language | 84.22 | 2008 | Optimization | Dharan |
4 | Sql Server Interview Questions | 90 | 2015 | Administration | Varshini Kutty |
SQL BETWEEN Operator Example With Text Values
The following SQL statement selects all books with a AuthorName beginning with any of the letter BETWEEN 'E' and 'P', in the "Books" table:
SELECT * FROM Books WHERE
AuthorName BETWEEN 'E' AND 'P';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
2 | Troubleshooting SQL Server | 70 | 2009 | Optimization | Keshavan |
It checks for text value:
- Value1:- Greater than or equal
- Value2:- Less than only
SQL NOT BETWEEN Operator Example With Text Values
To display the books outside the range of the previous example, use NOT BETWEEN:
SELECT * FROM Books WHERE
AuthorName NOT BETWEEN 'E' AND 'V';
The result of above query is:
BookId | BookName | BookPrice | RelYear | DomainName | AuthorName |
---|---|---|---|---|---|
3 | The SQL Programming Language | 84.22 | 2008 | Optimization | Dharan |
4 | Sql Server Interview Questions | 90 | 2015 | Administration | Varshini Kutty |
Related Links
SQL Between and NOT Between
SQL BETWEEN
In SQL, the BETWEEN...AND operator is used to pick in-between values from a given range of data. In SELECT, UPDATE, and DELETE statements/queries, it's utilised in the WHERE clause.
The BETWEEN operator accepts both beginning and ending values.
The BETWEEN operator can be used with numeric, text, and date values.
Syntax for SQL BETWEEN…AND operator
SELECT column_name1, column_name2, etc
FROM table_name
WHERE column_name1 BETWEEN value1 AND value2;
Let's put these statements into real use.
We've a table named Students in our database that contains the following records:
ROLL_NO | NAME | SUBJECT |
---|---|---|
1 | Will | Java |
2 | Sam | Phython |
3 | Sara | HTML |
4 | Rim | Java |
5 | Micheal | SQL |
6 | Lara | Java |
Now lets select values in a certain range from Students table.
Example 1: BETWEEN
SELECT * FROM Students
WHERE ROLL_NO BETWEEN 2 AND 5;
The above SQL statement selects all students that their ROLL_NO is BETWEEN 2 and 5.
Output:
ROLL_NO | NAME | SUBJECT |
---|---|---|
2 | Sam | Phython |
3 | Sara | HTML |
4 | Rim | Java |
5 | Micheal | SQL |
Now lets select values in a outside the range from Students table.
Example 2: Using the above-mentioned syntax, we can define values as part of BETWEEN operator. Also, the syntax mentioned above remains the same for usage with a numeric value, text value, and date value.
Get the percentage of students whose age is between 11 and 13.
SELECT StudentPercent FROM Student WHERE StudentAge BETWEEN 11 AND 13;
Output:
StudentPercent |
---|
88 |
84 |
78 |
SQL NOT BETWEEN
The SQL NOT BETWEEN operator is used to extract values from a result set that are outside of the BETWEEN operator's defined range.
Syntax for SQL NOT BETWEEN…AND operator
SELECT column_name1, column_name2, etc
FROM table_name
WHERE column_name1 NOT BETWEEN value1 AND value2;
Example 1: HOW TO USE NOT BETWEEN…AND IN WHERE CLAUSE IN SELECT QUERIES
SELECT Student_ID, Student_name, City, Age from student
WHERE Age NOT BETWEEN 19 AND 25;
Example 2: NOT BETWEEN
SELECT * FROM Students
WHERE ROLL_NO NOT BETWEEN 2 AND 5;
The above SQL statement selects all students that their ROLL_NO is NOT BETWEEN 2 and 5.
Output:
ROLL_NO | NAME | SUBJECT |
---|---|---|
1 | Will | Java |
6 | Lara | Java |
Example 3: Scenario: Get the percentage of students whose age is not between 11 and 13:
SELECT StudentPercent FROM Student WHERE StudentAge NOT BETWEEN 11 AND 13;
Output:
StudentPercent |
---|
85 |
SQL Between Cast Dates
Use the CAST() function to explicitly convert date or time values to the suitable data type when employing the BETWEEN operator for the best results.
Example 1:
SELECT *
FROM orders
WHERE order_date BETWEEN CAST('2016/04/19' AS DATE) AND CAST('2016/05/01' AS DATE);
There will be 3 records selected. These are the results that you should see:
Order_id | Customer_id | Order_date |
---|---|---|
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | NULL | 2016/05/01 |
This example would return all records from the orders table where the order_date is between Apr 19, 2016 and May 1, 2016 (inclusive).
Example 2: For example, if you use a string such as '2016-12-31' in a comparison to a DATE, cast the string to a DATE, as follow:
The following SQL statement selects all the employees who hired between 1st January 2006 (i.e. '2006-01-01') and 31st December 2016 (i.e. '2016-12-31'):
SELECT * FROM employees WHERE hire_date
BETWEEN CAST('2006-01-01' AS DATE) AND CAST('2016-12-31' AS DATE);
Result:
Emp_id | Emp_name | Hire_date | Salary | Dept_id |
---|---|---|---|---|
4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
5 | Martin Blank | 2008-06-24 | 5600 | NULL |
SQL Between Dates
In SQL, dates can be complicated, and how you utilise the BETWEEN condition with dates is dependent on the database you're working with.
Example 1: In such situations, when you have both upper and lower limits for your range, you can choose to use a single condition that uses the BETWEEN operator instead of using two different conditions, as in:
SELECT *
FROM Employees
WHERE HireDate BETWEEN '2012-01-01' AND '2014-12-31';
Output:
ID | Name | Age | Job | Salary | HireDate |
---|---|---|---|---|---|
2 | Kim | 26 | Manager | 55000 | 2014-04-25 |
3 | Eve | 22 | Developer | 32000 | 2013-03-11 |
6 | Sam | 30 | Janitor | 10000 | 2012-02-10 |
Example 2: In this example, we have a table called orders with the following data:
Order_id | Customer_id | Order_date |
---|---|---|
1 | 7000 | 2016/04/18 |
2 | 5000 | 2016/04/18 |
3 | 8000 | 2016/04/19 |
4 | 4000 | 2016/04/20 |
5 | NULL | 2016/05/01 |
The Query will be look like this,
SELECT *
FROM orders
WHERE order_date BETWEEN '2016/04/19' AND '2016/05/01';
Example 3: show the date between two dates using an example.
SELECT name,start_date FROM employee
WHERE start_date BETWEEN '2000-01-01' AND '2006-01-01';
In the above output, we have shown the number of employees who started job between 2000-01-01 and 2006-01-01, as a result, we found that there are 9 employees.
Example 4: Collect records of Feb to Aug for the years 2004 and 2005. To get the records between two months with the year we have to change this query. Let us say we want records from March 2015 to Feb 2016.
SELECT FROM table_name WHERE dte_field
BETWEEN '2015-03-01' AND LAST_DAY('2016-02-01')
Example 5: Two dates between single quotes like:
select Date, TotalAllowance from Calculation where EmployeeId = 1
and Date between '2011/02/25' and '2011/02/27'
SQL Between Dates with Time
Use the BETWEEN keyword to separate the two dates and times specified in the query. The WHERE keyword comes before this to satisfy the requirement created by the BETWEEN clause. As previously indicated, the date and time format in our table will be yyyy:mm: dd hh:mm: ss, as specified by DATETIME2. The time is formatted as a 24-hour clock.
Example 1: Retrieve the details of the transactions done between 10:00 am, 1st February 2001 and 10:00 pm, 1st May 2007.
SELECT * FROM ATM WHERE
TRANSACTION_TIME BETWEEN
'2001-02-01 10:00:00' AND
'2007-03-01 22:00:00';
Example 2: Retrieve the details of the transactions done between 09:00 pm, 28th February 2005 and 12:00 am, 25th December 2008.
SELECT * FROM ATM WHERE TRANSACTION_TIME
BETWEEN '2005-02-28 21:00:00'
AND '2008-12-25 00:00:00';
Note:– The 5th tuple is not displayed as its transaction time is 00:01:00 whereas our query has limited the time to 00:00:00.
Example 3: “sql query between two dates and times”:
SELECT * FROM `objects`
WHERE (date_field BETWEEN '2010-01-30 14:15:55' AND '2010-09-29 10:15:55')
WHERE
requireddate BETWEEN
CAST('2003-01-01' AS DATE) AND
CAST('2003-01-31' AS DATE);
Example 4: You can use the BETWEEN operator to select employees who were born between 01-Jan-1948 and 01-Jan-1960 as follows:
SELECT lastname,
firstname, birthdate
FROM employees
WHERE
birthdate BETWEEN '1948-01-01' AND '1960-01-01';
In case the column that you want to compare is a DATETIME column, the following expression:
dt BE\ETWEEN '1980-01-01' AND '1980-01-02';
is translated as:
dt BETWEEN '1980-01-01 00:00:00.000000 AND '1980-01-02 00:00:00.000000';
Because the time part is not specified in the date literals, the database engine uses 12:00:00 AM as the default time. It means any row that contains a time part after 12:00 A.M. on 1980-01-01 is not returned because it is outside the range.
SQL Between Float
use BETWEEN in the entry_key column it includes the all entry_keys. When I search BETWEEN 2 and 2.21 it will find the entry with 2.2.
And, when I try in the mysql shell the following:
SELECT 2.2 BETWEEN 2 and 2.2;
Output:
2.2 BETWEEN 2 and 2.2 |
---|
1 |
You should use a decimal data type rather than a float. Equality, and hence between, for floating point values is imprecise
SQL Between for Text
Example 1: 'Between...and' operator for text value:
BEGIN
IF 'C' BETWEEN 'A' AND 'D' THEN
DBMS_OUTPUT.PUT_LINE('TRUE');
ELSE
DBMS_OUTPUT.PUT_LINE('FALSE');
END IF;
END;
PL/SQL procedure successfully completed.
Example 2: example for the expression:
where food_name BETWEEN 'G' AND 'O'
will match 'O', but not any other string beginning with 'O'.
Once simple kludge is to use "~". This has the largest 7-bit ASCII value, so for English-language applications, it usually works well:
where food_name between 'G' and 'O~'
You can also do various other things. Here are two ideas:
where left(food_name, 1) between 'G' and 'O'
where food_name >= 'G' and food_name < 'P'
The important point, though, is that between works the same way regardless of data type.
SQL Between in Case Statement
The WHEN clause, not the THEN clause, should include the Boolean statement (which returns the value).
Example 1: sample SQL statement using a CASE expression on the title table in the sample pubs database:
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END,
FROM titles
This statement would return results similar to these:
Title | Price | Budget |
---|---|---|
Cooking with Computers | 11.95 | Moderate |
Straight Talk About Co | 19.99 | Moderate |
The Busy Executive's D | 19.99 | Moderate |
You Can Combat Compute | 2.99 | Inexpensive |
Silicon Valley Gastron | 19.99 | Moderate |
The Gourmet Microwave | 2.99 | Inexpensive |
But Is It User Friendl | 22.95 | Expensive |
Secrets of Silicon Val | 20.00 | Moderate |
Net Etiquette | (null) | Unknown |
Example 2: While grouping in a rang it is better to use MySQL BETWEEN Query
SELECT `id` , `name` , `class` , `mark` , `gender` ,
CASE
WHEN mark BETWEEN 90 AND 100 THEN 'A'
WHEN mark BETWEEN 80 AND 89 THEN 'B'
WHEN mark BETWEEN 70 AND 79 THEN 'C'
ELSE 'FAIL'
END AS grade
FROM `student`
Example 3: Take out the MONTHS from your case, and remove the brackets... like this:
CASE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
You can think of this as being equivalent to:
CASE TRUE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
SQL Between Inclusive and Exclusive
The BETWEEN operator is used to limit the results to a specific range. Numbers, messages, and dates can all be used as values.
The BETWEEN Operator in SQL The BETWEEN operator accepts both beginning and ending values.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
SQL Between Min and Max
The BETWEEN operator can be used to run a query based on a condition like this. NOT BETWEEN min AND max is the same as NOT BETWEEN min AND max (expr BETWEEN min AND max).
BETWEEN gives 1 if expr is larger than or equal to min and less than or equal to max.
If not, it returns 0.
This is the same as the equation (min <= expr AND expr <= max).
Syntax:
SELECT column1_name, column2_name, columnN_name
FROM table_name
WHERE column_name BETWEEN min_value AND max_value;
Example 1:
SELECT 1 BETWEEN 2 AND 3;
Output:
1 BETWEEN 2 AND 3 |
---|
0 |
Example 2: The BETWEEN operator tests if a value is within a specified range. It uses the syntax value BETWEEN min AND max:
SELECT 3 BETWEEN 2 AND 6;
The statement shown above is equivalent to the following statement:
SELECT 3 >= 2 AND 3 <= 6;
Example 3: The following SQL statement will return only those employees from the employees table, whose salary falls within the range of 7000 and 9000.
SELECT * FROM employees
WHERE salary BETWEEN 7000 AND 9000;
SQL Between Multiple Range
Example 1: BETWEEN is a nice method to help define ranges.
SELECT boroid
FROM districts
WHERE boroid BETWEEN 101 AND 108
OR boroid BETWEEN 301 AND 303
OR boroid = 402;
Example 2: multiple ranges within the between:
select id, price
from tablename
where price between 500 and 1500
or price between 2500 and 4500
Example 3: I have a table like the one below…
ID | Price |
---|---|
1 | 1000 |
2 | 2000 |
3 | 4000 |
4 | 5000 |
Now I need a query to select values between 500 and 1500 And between 2500 and 4500. i.e. like using two between in a single select query :
select id, price
from tablename
where price between 500 and 1500
or price between 2500 and 4500
Output:
ID | Price |
---|---|
1 | 1000 |
3 | 4000 |
SQL Between Now and 7 days
Example: MySql GETDATE() for now and 7 days:
select * from tab
where DateCol between adddate(now(),-7) and now()
SQL Between String
You can create conditions that search for ranges of strings by defining a character range, dates, and numerals.
Example 1: The following SQL statement selects all the employees whose name beginning with any of the letter between 'O' and 'Z':
SELECT * FROM employees
WHERE emp_name BETWEEN 'O' AND 'Z';
Output:
Emp_id | Emp_name | Hire_date | Salary | Dept_id |
---|---|---|---|---|
2 | Tony Montana | 2002-07-15 | 6500 | 1 |
3 | Sarah Connor | 2005-10-18 | 8000 | 5 |
4 | Rick Deckard | 2007-01-03 | 7200 | 3 |
Example 2: Query will find all the existing Customers in the Customers table whose Last Name is in the middle of Carlson and Ruiz
SELECT [FirstName]
,[LastName]
,[YearlyIncome]
,[Education]
,[Occupation]
FROM [Customer]
WHERE [LastName] BETWEEN 'Carlson' AND 'Ruiz'
ORDER BY [LastName]
Example 3: When you are searching for employees whose names are between the letters ‘A’ and ‘F’. Here’s what the query would look like:
SELECT *
FROM Employees
WHERE Name BETWEEN 'A' AND 'F';
Output:
ID | Name | Age | Job | Salary | HireDate |
---|---|---|---|---|---|
1 | Bob | 28 | Manager | 60000 | 2011-03-07 |
3 | Eve | 22 | Developer | 32000 | 2013-03-11 |
Example 4: Suppose you want to search for last names that begin with the letter F. The following clause won’t work because it will retrieve someone whose last name is the letter G (is the letter G, not starts with the letter G):
WHERE last_name BETWEEN 'F' AND 'G'
This next clause shows the correct way to specify the ending point (in most cases):
WHERE last_name BETWEEN 'F' AND 'Fz'
SQL Between Time Range
Using the BETWEEN clause, retrieve data between two date time periods, such as details between specified timestamps.
Example 1: When we don’t specify the time along with date range then it defaults to 12:00 A.M:
SELECT name, type_desc,create_date, modify_date
FROM sys.objects
WHERE modify_date BETWEEN ‘2021-03-19 4:38:00’ AND ‘2021-03-19 4:38:30’
Example 2: In this section, we will use the table named journey, and a description of the table is given below.
Let’s see the arrival and departure of the person using the below code.
SELECT * FROM journey
WHERE arrival
BETWEEN '2021-05-06 07:30:00' AND '2021-05-07 20:40:30';
In the above code, checking the arrival or departure of a person between the date timestamp range of ‘2017-05-06 07:30:00’ and ‘2019-07-14 20:40:30’.
SQL Between Values
The SQL BETWEEN condition makes it simple to see if an expression falls inside a specified range of values (inclusive).
The BETWEEN operator is used in the WHERE conditions to filter records that fall within a certain range. Strings, integers, and dates are examples of possible values. With the AND operator, the range of values must be provided.
Syntax:
SELECT column1, column2,..
FROM table
WHERE column BETWEEN begin_value AND end_value
If the expression is higher than or equal to the low value (>=) and less than or equal to the high value (=), the BETWEEN operator returns true.
Example 1: The following query selects product whose unit price is from $18 to $19:
SELECT productName, unitPrice
FROM
products
WHERE
unitPrice BETWEEN 18 AND 19;
Example 2: The following statement uses the BETWEEN operator to find all employees whose salaries are between 2,500 and 2,900:
SELECT employee_id,
first_name,
last_name,
salary
FROM
employees
WHERE
salary BETWEEN 2500 AND 2900
ORDER BY
salary DESC;
Example 3: use the following Employee tables in all examples.
EmpId | FirstName | LastName | Salary | HireDate | |
---|---|---|---|---|---|
1 | John | King | john.king@abc.com | 33000 | 2018-07-25 |
2 | James | Bond | 2018-07-29 | ||
3 | Neena | Kochhar | neena@test.com | 17000 | 2018-07-22 |
4 | Lex | De Haan | lex@test.com | 15000 | 2018-09-08 |
5 | Amit | Patel | 18000 | 2019-01-25 | |
6 | Abdul | Kalam | abdul@test.com | 25000 | 2020-07-14 |
Consider the following query.
SELECT EmpId, FirstName, LastName, Salary
FROM Employee
WHERE Salary BETWEEN 10000 AND 20000;
Above, the Salary column is used with the BETWEEN operator to filter records. The Salary BETWEEN 10000 AND 20000; specifies that the values in the Salary column should be between 10000 and 20000 (inclusive of both values).
Output:
EmpId | FirstName | LastName | Salary |
---|---|---|---|
3 | Neena | Kochhar | 17000 |
4 | Lex | De Haan | 15000 |
5 | Amit | Patel | 18000 |
The AND operator must be used with the BETWEEN operator; otherwise, an error will occur.
SQL Between vs Greater Than
They're the same: BETWEEN is a shortcut for the question's longer phrasing, which contains both values (EventDate >= '10/15/2009' and EventDate = '10/19/2009').
The syntax of the BETWEEN operator is as follows:
expression Where condition >= AND condition <=;
The BETWEEN operator returns true if the expression is greater than or equal to ( >=) the low value and less than or equal to ( <=) the high value.
Between Query 1:
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate BETWEEN '10/15/2009' AND '10/18/2009'
Greater than Query 2:
SELECT EventId, EventName
FROM EventMaster
WHERE EventDate >='10/15/2009'
AND EventDate <='10/18/2009'
SQL Between Yesterday and Today
Example 1: To combine the variable date and specific time values.
With all the numerous local formats, date and time constants can be capricious. We can prevent this with TimeSerial:
SELECT *
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + TimeSerial(18,0,0) And Date() + TimeSerial(18,0,0)
(First suggestion, works with German date format)
SELECT *
FROM [Table]
WHERE [Table].[Date Time] Between Date()-1 + #18:00:00# And Date() + #18:00:00#
Between Date()-1 + #12/30/1899 18:00:00# And Date() + #12/30/1899 18:00:00# 1899-12-30 is "Date zero" in Access.
Example 2: You can always find today (with no time):
SELECT CONVERT(date, GETDATE());
So to find all of the data for yesterday, you say:
DECLARE @today date = GETDATE();
SELECT ...
WHERE createDate >= DATEADD(DAY, -1, @today)
AND createDate < @today;
For today, it's a simple change:
WHERE createDate >= @today
AND createDate < DATEADD(DAY, 1, @today);
Example 3: Between Yesterday at 23:00:00 and today 06:59:59:
We are using with a datetime stamp. The below works but it is also including records at 7:00:00 which i understand why.
How would i exclude 7:00:00 and go records below that time e.g. <= 06:59:59.
CREATE_DATE Between DATEADD(HOUR,23,DATEADD(day,DATEDIFF(day,0,GETDATE()-1),-0))
And DATEADD(HOUR,7,DATEADD(day,DATEDIFF(day,0,GETDATE()),-0)))
SQL Between Multiple
SQL provides a number of predicates, each of which uses a different operator to analyse rows, allowing users to retrieve granular result sets. There are two sorts of predicates in this manual: range predicates that use the BETWEEN operator and set membership predicates that use the IN operator.
Use the BETWEEN operator in SQL to see if the values in a column are within a certain range.
Syntax:
SQL Multiple Between Syntax
SELECT Column(s) FROM table_name WHERE column_name
BETWEEN value1 AND value2 AND column_name
BETWEEN value3 and value4 ... AND column_name
BETWEEN valueN and valueM
Example 1: Results Multiple between A and B, or it is between C and D etc.
SELECT Description, SUM(Balance) AS Total_Balance
FROM Chart_Of_Accounts
WHERE (Account BETWEEN '400401' AND '400407')
OR (Account BETWEEN '440094' AND '440100')
OR (Account BETWEEN '450094' AND '450100')
GROUP BY Description
Example 2: SQL Multiple Between Examples
Use SQL multiple Between operator to fetch student records who have given exam of semester 1 to 2 and achieved percentage more than 70 and less than 90.
SELECT tblstudent.studentname, tblresult.examdate AS 'Exam Date',
tblresult.examname, tblresult.obtainmark, tblresult.pecentage, tblresult.subjectid
FROM tblresult LEFT OUTER JOIN tblstudent ON tblstudent.student_id = tblresult.student_id
WHERE (tblresult.pecentage BETWEEN 70 AND 90) AND (tblresult.examname BETWEEN 'sem1' AND 'sem2');
In above query two SQL Between operator is used to compare two range of values with two different columns percentage and examname.
The resulting records of this query contains student details who has got percentage between 70 to 90, and with examname of sem1 to sem2.