Tutorials
Search…
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. 1.
    AND for when you want both conditions to be true
  2. 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)
Copy link