Learn SQL With Bru

In this blog we shall look into the usage of WHERE clause in SQL Server. WHERE clause can be used in Select, Update, Delete statements to filter the rows being affected by the query. In fact it can be used inside an Insert statement too which has a Select statement. WHERE clause ensures the data is filtered when it is retrieved in select statements or it ensures only those records are affected when updating / deleting is performed.

A WHERE clause contains one or more conditions depending on which the records are filtered in the table(s). The conditions in WHERE clause can be based on one or more operators and the operators that can be used in a WHERE clause can be =, <>, != , >, <, BETWEEN, IN, LIKE, NOT and many more. When there are multiple conditions, those conditions can be combined with AND / OR.

Let us look at some examples to understand different types of WHERE clause.

Examples for WHERE Clause

Example 1: Using “=”

SELECT EmployeeID, Title, Gender, HireDate 

FROM AdventureWorks.HumanResources.Employee

WHERE Title = 'Production Technician - WC20'

In the above SQL statement, the condition used in WHERE ensure that those records that have the value “Production Technician – WC20″ in the Title column will be displayed. You might have noticed that the string value Production Technician – WC20 is enclosed in single quotes, the reason being that all string values and Date and time related columns need to compared with at value which is enclosed in single quotes.

Example 2 A: Using “LIKE” and %

SELECT EmployeeID, Title, Gender, HireDate 

FROM AdventureWorks.HumanResources.Employee 

WHERE Title LIKE '%Technician'

In the above SQL statement, the condition used in WHERE is more wider than the one in previous example. It uses LIKE keyword to filter those records that end with the value “Technician” in the column Title. Notice that when we use LIKE we do not use = symbol as we would like to filter the data according to a patterm matching and the criteria in this condition is any rows / records which end with the string Technician. You also notice that ‘%’ is used before the string, which indicates that any string value in that position is valid in the condition.

SELECT EmployeeID, Title, Gender, HireDate 

FROM AdventureWorks.HumanResources.Employee 

WHERE Title LIKE '%Technician%'

In this statement, the condition is little bit changed to that in previous example. It has an additional ‘%’ at the end of the expression, indicating that any record is a valid record which contains the string “Technician” in column Title, no matter if it is at the beginning or startig or the end of the string value.

Comments / feedback will be highly appreciated.

–Bru Medishetty