DevTut

# Filter results using WHERE and HAVING

# Use BETWEEN to Filter Results

The following examples use the Item Sales (opens new window) and Customers (opens new window) 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';

Live example: SQL fiddle (opens new window)

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

# 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 (opens new window)).

Common aggregate functions include COUNT(), SUM(), MIN(), and MAX().

This example uses the Car Table (opens new window) 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 (opens new window) 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 (opens new window) 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.

# 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

# Equality

SELECT * FROM Employees

This statement will return all the rows from the table Employees (opens new window).

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

# AND and OR

You can also combine several operators together to create more complex WHERE conditions. The following examples use the Employees (opens new window) 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 (opens new window) 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 (opens new window).

This example uses the Employees Table (opens new window) 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'.

# 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.

# 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)

# Syntax

  • SELECT column_name
    FROM table_name
    WHERE column_name operator value
  • SELECT column_name, aggregate_function(column_name)
    FROM table_name
    GROUP BY column_name
    HAVING aggregate_function(column_name) operator value

    • ← SKIP TAKE (Pagination) →