Sql Filter results using WHERE and HAVING

Sql Filter results using WHERE and HAVING
Nikhil
Published on 2021-08-14 13:59:01

Section 13.1: Use BETWEEN to Filter Results

The following examples use the Item Sales and Customers sample databases.

Note: The BETWEEN operator is inclusive.

Using the BETWEEN operator with Numbers:

SELECT * From ItemSales
WHERE Quantity BETWEEN 10 AND 17

This query will return all ItemSales records that have a quantity that is greater or equal to 10 and less than or equal to 17. The results will look like:

Id SaleDate ItemId Quantity Price
1 2013-07-01 100 10 34.5
4 2013-07-23 100 15 34.5
5 2013-07-24 145 10 34.5

Using the BETWEEN operator with Date Values:

SELECT * From ItemSales
WHERE SaleDate BETWEEN '2013-07-11' AND '2013-05-24'

This query will return all ItemSales records with a SaleDate that is greater than or equal to July 11, 2013 and less than or equal to May 24, 2013.

Id SaleDate ItemId Quantity Price
3 2013-07-11 100 20 34.5
4 2013-07-23 100 15 34.5
5 2013-07-24 145 10 34.5

When comparing datetime values instead of dates, you may need to convert the datetime values into a date values, or add or subtract 24 hours to get the correct results. Using the BETWEEN operator with Text Values:

SELECT Id, FName, LName FROM Customers
WHERE LName BETWEEN 'D' AND 'L';

This query will return all customers whose name alphabetically falls between the letters 'D' and 'L'. In this case, Customer #1 and #3 will be returned. Customer #2, whose name begins with a 'M' will not be included.

Id FName LName
1 William Jones
3 Richard Davis

Section 13.2: Use HAVING with Aggregate Functions

Unlike the WHERE clause, HAVING can be used with aggregate functions. An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement (Wikipedia). Common aggregate functions include COUNT(), SUM(), MIN(), and MAX().

This example uses the Car Table from the Example Databases.

SELECT CustomerId, COUNT(Id) AS [Number of Cars]
FROM Cars
GROUP BY CustomerId
HAVING COUNT(Id) > 1

This query will return the CustomerId and Number of Cars count of any customer who has more than one car. In this case, the only customer who has more than one car is Customer #1.

The results will look like:

CustomerId Number of Cars
1 2

WHERE clause with NULL/NOT NULL values

SELECT *
FROM Employees
WHERE ManagerId IS NULL

This statement will return all Employee records where the value of the ManagerId column is NULL.

The result will be:

Id FName LName PhoneNumber ManagerId DepartmentId
1 James Smith 1234567890 NULL 1

SELECT *
FROM Employees
WHERE ManagerId IS NOT NULL

This statement will return all Employee records where the value of the ManagerId is not NULL.

The result will be:

Id FName LName PhoneNumber ManagerId DepartmentId
2 John Johnson 2468101214 1 1
3 Michael Williams 1357911131 1 2
4 Johnathon Smith 1212121212 2 1

Note: The same query will not return results if you change the WHERE clause to WHERE ManagerId = NULL or WHERE ManagerId <> NULL.

Equality

SELECT * FROM Employees

This statement will return all the rows from the table Employees.

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date CreatedDate ModifiedDate
1 James Smith 1234567890 NULL 1 1000 01-01-2002 01-01-2002 01-01-2002
2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 01-01-2002
3 Michael Williams 1357911131 1 2 600 12-05-2009 12-05-2009 NULL
4 Johnathon Smith 1212121212 2 1 500 24-07-2016 24-07-2016 01-01-2002

Using a WHERE at the end of your SELECT statement allows you to limit the returned rows to a condition. In this case, where there is an exact match using the = sign:

SELECT * FROM Employees WHERE DepartmentId = 1

Will only return the rows where the DepartmentId is equal to 1:

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date CreatedDate ModifiedDate
1 James Smith 1234567890 NULL 1 1000 01-01-2002 01-01-2002 01-01-2002
2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 01-01-2002
4 Johnathon Smith 1212121212 2 1 500 24-07-2016 24-07-2016 01-01-2002

The WHERE clause only returns rows that match its criteria

Steam has a games under $10 section of their store page. Somewhere deep in the heart of their systems, there's probably a query that looks something like:

SELECT *
FROM Items
WHERE Price < 10

AND and OR

You can also combine several operators together to create more complex WHERE conditions. The following examples use the Employees table:

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date CreatedDate ModifiedDate
1 James Smith 1234567890 NULL 1 1000 01-01-2002 01-01-2002 01-01-2002
2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 01-01-2002
3 Michael Williams 1357911131 1 2 600 12-05-2009 12-05-2009 NULL
4 Johnathon Smith 1212121212 2 1 500 24-07-2016 24-07-2016 01-01-2002

AND

SELECT * FROM Employees WHERE DepartmentId = 1 AND ManagerId = 1

Will return:

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date CreatedDate ModifiedDate
2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 01-01-2002

OR

SELECT * FROM Employees WHERE DepartmentId = 2 OR ManagerId = 2

Will return:

Id FName LName PhoneNumber ManagerId DepartmentId Salary Hire_date CreatedDate ModifiedDate
3 Michael Williams 1357911131 1 2 600 12-05-2009 12-05-2009 NULL
4 Johnathon Smith 1212121212 2 1 500 24-07-2016 24-07-2016 01-01-2002

Use IN to return rows with a value contained in a list

This example uses the Car Table from the Example Databases.

SELECT *
FROM Cars
WHERE TotalCost IN (100, 200, 300)

This query will return Car #2 which costs 200 and Car #3 which costs 100. Note that this is equivalent to using multiple clauses with OR, e.g.:

SELECT *
FROM Cars
WHERE TotalCost = 100 OR TotalCost = 200 OR TotalCost = 300

Use LIKE to find matching strings and substrings

See full documentation on LIKE operator.This example uses the Employees Table from the Example Databases.

SELECT *
FROM Employees
WHERE FName LIKE 'John'

This query will only return Employee #1 whose first name matches 'John' exactly.

SELECT *
FROM Employees
WHERE FName like 'John%'

Adding % allows you to search for a substring:

  • John% - will return any Employee whose name begins with 'John', followed by any amount of characters
  • %John - will return any Employee whose name ends with 'John', proceeded by any amount of characters
  • %John% - will return any Employee whose name contains 'John' anywhere within the value

In this case, the query will return Employee #2 whose name is 'John' as well as Employee #4 whose name is 'Johnathon'.

Where EXISTS

Will select records in TableName that have records matching in TableName1.

SELECT * FROM TableName t WHERE EXISTS (
SELECT 1 FROM TableName1 t1 where t.Id = t1.Id)

Use HAVING to check for multiple conditions in a group

Orders Table

CustomerId ProductId Quantity Price
1 2 5 100
1 3 2 200
1 4 1 500
2 1 4 50
3 5 6 700

To check for customers who have ordered both - ProductID 2 and 3, HAVING can be used

select customerId from orders
where productID in (2,3)
group by customerId
having count(distinct productID) = 2

Return value:

customerId
1

The query selects only records with the productIDs in questions and with the HAVING clause checks for groups having 2 productIds and not just one.

Another possibility would be

select customerId
from orders
group by customerId
having sum(case when productID = 2 then 1 else 0 end) > 0
and sum(case when productID = 3 then 1 else 0 end) > 0

This query selects only groups having at least one record with productID 2 and at least one with productID 3.

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website