databasedev.co.uk - database solutions and downloads for microsoft access

Using the WHERE clause with the SQL SELECT Statement:

You can create more powerful SQL SELECT queries by using the WHERE clause to choose to display only specific rows contained in the database table. The WHERE clause is used to filter the data that the SELECT statement would return in the result set. We use the WHERE clause to specify a value to identify one or more rows in the database table.

We will use the following syntax to execute an SQL SELECT query with a WHERE clause:

SELECT column_list
FROM table_name
WHERE column_name conditional_operator value

We can also use the following Conditional Operators with our SQL WHERE clause:

Operator	Descriptions
=	Equal To
>	Greater Than
<	Less Than
>=	Greater Than or Equal To
<=	Less Than or Equal To
<>	Not Equal To
LIKE	String Comparison Test
BETWEEN	Between an inclusive range

If the column using the WHERE clause is one of character data types, we must also enclose the value in quotes (single or double dependant on the database that you are using). In contrast, if the column using the WHERE clause is of numeric data type, you can't enclose the value in quotes.

Now, lets see a couple of examples of using the WHERE clause in the SQL SELECT statement. We will use the following Employee data table:

EmpName	EmpAddress	EmpTown	EmpPostcode	EmpAge
Elaine Jones	1 Old Road	Manchester	M27 1SN	28
David Thomas	245 Lane End	Lincoln	LN34 2TH	41
Simon Lee	9 Swallow Lane	Wigan	WN3 0NR	58
Leslie Ward	3 North End Road	Nottingham	NG8 2LJ	30
Harry Webb	1002 Trinity Road Sth	Swindon	SN2 1JH	22
Ronny Scott	1 The Lane	Nottingham	NG25 9LK	55
Janet Fuller	14 Garrett Hill	London	SW1	40
Sally Timmings	Edgeham Hollow	London	WE3	19

If we want to view the details of all of the Employees that live in London, we should use the following:

SELECT *
FROM tblEmployee
WHERE EmpTown = 'London'

Notice that we have enclosed the EmpTown value in quotes, as the data type for this column is text. The results from this query would give us:

EmpName	EmpAddress	EmpTown	EmpPostcode	EmpAge
Janet Fuller	14 Garrett Hill	London	SW1	55
Sally Timmings	Edgeham Hollow	London	WE3	40

If we want to view details of the Employees we have that are older than 30, then we would need to use the following query - notice this time that our where clause does not include quotes as the data type of the EmpAge column is numeric:

SELECT *
FROM tblEmployee
WHERE EmpAge > 30

This time we will be returned the following results to our query:

EmpName	EmpAddress	EmpTown	EmpPostcode	EmpAge
Elaine Jones	1 Old Road	Manchester	M27 1SN	41
Ronny Scott	1 The Lane	Nottingham	NG25 9LK	58
Janet Fuller	14 Garrett Hill	London	SW1	55
Sally Timmings	Edgeham Hollow	London	WE3	40

What we should try to remember is to always include a WHERE clause in your SQL SELECT statement to limit the number of results that the query will return. If you don't use the WHERE clause, then SQL Server will perform a table scan of your table and return all of the rows.

In some case you may want to return all of the database rows, and not using an SQL WHERE clause is appropriate in this case. But if you don't need all the rows returned, use a WHERE clause to limit the number of rows returned.

Why not check out these related SQL Articles:

• SQL Multiple Conditions; using the AND and OR conditions