SQL WHERE Clause

by Taiba Hamid · Published July 17, 2015 · Updated September 24, 2015

The WHERE clause is used to restrict the number of rows returned by SELECT statement, in this case SELECT statement return the rows only if the condition specified in the WHERE clause is satisfied.

Syntax:

SELECT col1, col2, colN

FROM TableName

WHERE [conditions]

Consider the Person table without using WHERE Clause:

SELECT [BusinessEntityID]
      ,[PersonType]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person]

This query will return 19972 rows

Now, we will filter the row in with First Name as ‘Ken’, we will proceed as follows:

SELECT [BusinessEntityID]
      ,[PersonType]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
FROM [Person].[Person] WHERE [FirstName]='Ken'

This query will return only 6 rows

In previous example we have used WHERE clause with one condition, now we will use WHERE clause with more than one condition.

Suppose we want to display people whose first name is equal to ‘Ken’ and last name is equal to ‘Kwok’, we will proceed as follows:

 SELECT [BusinessEntityID]
       ,[PersonType] 
       ,[Title] 
       ,[FirstName] 
       ,[MiddleName] 
       ,[LastName]
FROM [Person].[Person] WHERE FirstName='Ken' AND LastName='Kwok'

And we will get one record in output as shown below.

Note:

Do not use ALIAS in the WHERE clause because the WHERE clause is evaluated before the select, so if you try something like that you will get an error as shown below.

SELECT [BusinessEntityID]
      ,[PersonType]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[FirstName] +' '+ [LastName] AS [Full Name]
FROM [Person].[Person] WHERE [Full Name]='Ken Kwok'

The logical query processing is different to the conceptual interpretation order, it starts with the FROM clause, that is why it did not recognized ALIAS in the WHERE clause.

The logical query processing order:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY