560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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.
|
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.
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.
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.
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).
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.
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.