SQL WHERE Clause

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]

01_where

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'

02_where

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.

03_where

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'

04_where

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

You may also like...

Leave a Reply

Your email address will not be published.