This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Setting SQL criteria using the WHERE clause Part one of a four-part series of blogs
If you don't want to see a full set of records from a table in SQL, you can use a WHERE statement to filter out unwanted rows, as shown in this blog. The WHERE Clause in SQL to filter records using criteria (this blog) WHERE Criteria with Text Fields: % and _ Wildcards Combining WHERE Criteria Using Sound in Criteria This blog is part of our online SQL tutorial blog. If you want to learn SQL in a classroom environment, have a look at our SQL training courses.

Setting SQL criteria using the WHERE clause

Part one of a four-part series of blogs

If you don't want to see a full set of records from a table in SQL, you can use a WHERE statement to filter out unwanted rows, as shown in this blog.

The WHERE Clause in SQL to filter records using criteria (this blog)
WHERE Criteria with Text Fields: % and _ Wildcards
Combining WHERE Criteria
Using Sound in Criteria

This blog is part of our online SQL tutorial blog. If you want to learn SQL in a classroom environment, have a look at our SQL training courses.

Posted by Andy Brown on 08 November 2012

The WHERE Clause in SQL to filter records using criteria

By default, a SQL SELECT statement will return all of the records in a table, but you can use WHERE clauses to filter the rows to show only the ones of interest.

Our Example for this Blog

From the start of 2010 to the end of 2012 I kept careful track of my purchases of fruit, logging them in a SQL Server table:

The 10 purchases of fruit I made in the 3-year period (note that twice I forgot to log the purchase date).

At the bottom of this page you can see (and copy/run) the script used to create my simple table of fruit purchases.

The Basic WHERE Clause

Here is a simple criterion:

-- show purchases of more than 5 items

SELECT

Fruit,

Qty

FROM

tblFruit

WHERE

Qty > 10

ORDER BY

Fruit

This would return the following 4 fruit:

The 4 purchases where I bought more than 10 items at a time.

Note that the WHERE clause must come before the ORDER BY clause, if there is one.

Criteria for Numbers

If you're comparing numerical values (as in the example above), you can use any of the following relational operators:

Operator	What it means	Example	What it would return
>	Greater than	Qty > 20	Grapes
>=	Greater than or equal to	Qty >= 40	Grapes
<	Less than	Qty < 2	Lemons
<=	Less than or equal to	Qty <= 2	Bananas, lemons
=	Equals to	Qty = 3	Apples, limes
!= or <>	Not equal to	Qty <> 0	All the rows

You can use either != or <> for not equal to (C and Java programmers will probably prefer the former; VB programmers the latter).

Stripping out Nulls

You can use Is Null or Is Not Null to include or exclude rows which have null column values. For example, this query:

SELECT

Fruit,

DateBought

FROM

tblFruit

WHERE

DateBought is null

would show only the two fruit for which a purchase date hasn't been entered:

The two fruit for which the purchase date is null.

Criteria for Dates

You can use the relational operators above with dates too (if you want to know more about date formats, see our separate blog on SQL dates). The following query shows purchases made on or before 31st December 2010:

-- show 2010 purchases only

SELECT

Fruit,

DateBought

FROM

tblFruit

WHERE

DateBought <=>'2010-12-31'

ORDER BY

DateBought

Here are the rows this query would return:

The 3 purchases made on or before the last day of December 2010. Note that purchases with null dates are automatically excluded from the results.

Creating the Table for this Blog

I promised at the start of this page that I'd include the SQL needed to create my table of 10 fruit. Here it is!

-- if there's a table already created, delete it

BEGIN TRY

DROP TABLE tblFruit

END TRY

BEGIN CATCH

END CATCH

-- create a table of fruit purchases!

CREATE TABLE tblFruit (

FruitId int IDENTITY(1,1) PRIMARY KEY,

Fruit varchar(20),

Qty int,

DateBought date

)

-- insert a few rows

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Apples',3,'2010-04-21')

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Pears',5,'2011-11-13')

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Bananas',2,'2012-12-12')

INSERT INTO tblFruit(Fruit,Qty) VALUES ('Cherries',10)

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Raspberries',20,'2012-01-04')

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Blueberries',15,'2010-05-05')

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Grapes',40,'2012-04-07')

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Lemons',1,'2012-05-14')

INSERT INTO tblFruit(Fruit,Qty) VALUES ('Limes',3)

INSERT INTO tblFruit(Fruit,Qty,DateBought) VALUES ('Strawberries',20,'2010-10-14')

-- show what's been created

SELECT * FROM tblFruit

When you run this, you should see the following:

The table created by the SQL above.

Now that we've looked at criteria for numbers and dates, let's look at using criteria on text fields - and in particular, using wildcards.

Parts of this blog
The WHERE Clause in SQL to filter records using criteria (this blog) WHERE Criteria with Text Fields: % and _ Wildcards Combining WHERE Criteria Using Sound in Criteria

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

The WHERE Clause in SQL to filter records using criteria

Our Example for this Blog

The Basic WHERE Clause

Criteria for Numbers

Stripping out Nulls

Criteria for Dates

Creating the Table for this Blog

Head office

London

Manchester