Lesson 7. Complex Data Filtering

Data can be filtered in a lot of different ways. You can be very specific in order to get exactly what you want.

Filters are built with logic operators. The logic operators in SQL are very much like the logic operators you will find in any language.

Filtering Individual Columns

The following are the logic operators for filtering individual columns.

Operator	Operator Description
=	Equals
<>	Does not equal
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
!=	Does not equal
!<	Is not less than
!>	Is not greater than
BETWEEN, AND	Is between two values inclusive of those values
NOT BETWEEN, AND	Is not between two values inclusive of those values
IS NULL	Where the value is null
IS NOT NULL	Where the value is not null

Combining Individual Filters

Combining filters requires the use of two additional operators.

1. AND for when you want both conditions to be true

2. OR for when you want either condition to be true

Combining filters can be quite complex and confusing. For readability and your own sanity, I recommend that you keep everything straight by using parentheses to clearly group your logic.

Examples

A Full Set Of Filtering Examples

USE AdventureWorks2016

SELECT *
FROM Production.Product
WHERE 1 = 1

SELECT *
FROM Production.Product
WHERE 1 = 1
AND SafetyStockLevel = 1000

SELECT *
FROM Production.Product
WHERE 1 = 1
AND SafetyStockLevel != 1000

SELECT *
FROM Production.Product
WHERE 1 = 1
AND ReorderPoint >= 600

SELECT *
FROM Production.Product
WHERE 1 = 1
AND SafetyStockLevel != 1000

SELECT *
FROM Production.Product
WHERE 1 = 1
AND ListPrice BETWEEN 50 AND 100

SELECT *
FROM Production.Product
WHERE 1 = 1
AND Size IS NOT NULL
AND MakeFlag = 1

SELECT *
FROM Production.Product
WHERE 1 = 1
AND Size IS NOT NULL
OR Color = 'Black'

SELECT *
FROM Production.Product
WHERE 1 = 1
AND (ReorderPoint > 350 AND ListPrice <> 0)
OR (Color = 'Black' AND ListPrice = 0)