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 three 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 WHERE Criteria with Text Fields: % and _ Wildcards Combining WHERE Criteria (this blog) 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 three 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
WHERE Criteria with Text Fields: % and _ Wildcards
Combining WHERE Criteria (this blog)
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

Combining WHERE Criteria

The previous parts of this blog have all looked at single criteria; it's time to lift this restriction!

Combining Criteria using AND

If you use the keyword AND all of the criteria specified must be true. For example, the following query would show all fruit purchased in 2011:

SELECT

Fruit,

DateBought

FROM

tblFruit

-- show all fruit bought in 2011

WHERE

DateBought >= '2011-01-01' and

DateBought <=>'2011-12-31'

Note that you could also accomplish this using the BETWEEN keyword:

WHERE

DateBought between '2011-01-01' and '2011-12-31'

although the best way to do this would be using an SQL function:

WHERE

year(DateBought) = 2011

Choosing Alternatives using OR

If you use the keyword OR, any of the criteria specified can be true. For example, the following query would show fruit purchases which are either of berries or which were made in 2010:

SELECT

Fruit,

DateBought

FROM

tblFruit

-- show purchases of berries or made in 2010

WHERE

year(DateBought) = 2010 or

Fruit like '%berries'

This would give the following result (I've included an extra column to show why the query would return the result in question):

The purchases of Blueberries and Strawberries qualify for inclusion in the results for both reasons, but still only appear once.

Combining AND and OR

You can combine criteria, although you will probably find it useful to enclose them in brackets to emphasise what you're trying to achieve. The following query shows all purchases either made in 2010 or where between 3 and 5 items were bought at a time:

SELECT

Fruit,

DateBought,

qty

FROM

tblFruit

-- show purchases between 3 and 5 items,

-- or which were made in 2010

WHERE

(qty >= 3 and qty <=5)>or

year(DateBought) = 2010

This would return the following 5 rows:

The query returns 5 rows: all of these satisfy at least one of the two crtieria given.

Note that on this occasion you could omit the brackets and get the same result, but the parentheses make it clearer what's going on.

The Useful IN Keyword for Lists

You can use IN to make your queries more readable, by specifying allowable items in a list. Consider the following messy query criteria:

WHERE

Fruit = 'Apples' or

Fruit = 'Pears' or

Fruit = 'Cherries'

This will gives all purchases of the 3 fruit listed. However, you could also write this as:

WHERE

Fruit in ('Apples', 'Pears', 'Cherries')

You can do this with numerical values too. The following query would show all purchases of fruit made in January, February, April or September:

WHERE

Month(DateBought) in (1,2,4,9)

Parts of this blog
The WHERE Clause in SQL to filter records using criteria WHERE Criteria with Text Fields: % and _ Wildcards Combining WHERE Criteria (this blog) 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

Combining WHERE Criteria

Combining Criteria using AND

Choosing Alternatives using OR

Combining AND and OR

The Useful IN Keyword for Lists

Head office

London

Manchester