It is assumed that you have gone through the previous tutorials and have a basic understanding of it. It is
also assumed that you have practised on the previous examples shown so that you have grasped the syntax notations
so that writing simple queries is possible by yourself.
Here the 'WHERE'
clause is executed on the 'Employees' table.
The WHERE clause
The 'WHERE" clause acts as a filter to a query. It filters out certain records based on the criteria you supply and fetches records
if any, only if the given condition is satisfied. The 'WHERE' clause is used in
'SELECT', 'UPDATE' and 'DELETE' statements.
SQL WHERE Syntax in Select, Update and Delete Statements
Select
column_name FROM table_name
where condition
Update table_name set (column_name1 =value1,Column_name2=value2...)
where condition
Delete FROM table_name
where condition
The SELECT Statement Example with where clause
The
"Select statement on the Employees" table displays all the columns and data from the
'EMPLOYEES'
table as shown
below.
| 1 |
Richard Hughes |
4/23/1945 12:00:00 AM |
1 |
1 |
343-123-2002 |
New Orleans |
| 2 |
Taryn Sinclair |
3/22/1980 12:00:00 AM |
2 |
2 |
232-344-8755 |
San Francisco |
| 3 |
Ted Horowitz |
3/31/1960 12:00:00 AM |
3 |
4 |
435-657-9068 |
New York |
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
| 5 |
Miranda Leigh |
7/19/1983 12:00:00 AM |
7 |
3 |
634-465-8046 |
New York |
| 6 |
Jana Rae |
9/23/1976 12:00:00 AM |
8 |
2 |
434-434-1254 |
Houston |
| 7 |
Lita Rosanna |
9/14/1982 12:00:00 AM |
4 |
6 |
565-782-1223 |
Long Island |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
| 9 |
Anthony Frank |
2/22/1988 12:00:00 AM |
9 |
3 |
323-243-1249 |
San Francisco |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
| 11 |
Jack Martin |
8/25/1985 12:00:00 AM |
NULL |
NULL |
443-324-6522 |
Austin |
Select EmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,PhoneNo from
Employees
The
below
'SELECT" statement with the 'WHERE' clause brings out only the records that satisfy the given condition. Let's say we want only those employees who are from SAlt Lake city,we will pass this value in the 'WHERE' clause.
Note that since city is of datatype nvarchar we enclose the value in single
quotes. Also a where clause can be operated on one or more tables.
Select EmployeeID,EmployeeName,DateOfBirth,DesignationID,DeptID,PhoneNo from
Employees where City='Salt Lake City'
| 4 |
Jonathan Douglas |
7/7/1971 12:00:00 AM |
3 |
4 |
323-454-7656 |
Salt Lake City |
| 8 |
Colin Flooks |
12/29/1988 12:00:00 AM |
4 |
2 |
954-652-2111 |
Salt Lake City |
| 10 |
Stephanie Lynn |
7/30/1979 12:00:00 AM |
8 |
2 |
543-765-4653 |
Salt Lake City |
The UPDATE Statement Example with where clause
The
"update statement on the Employees" table updates data in one or more columns based on the criteria
as shown
below. It updates the EmployeeName and DateOfBirth columns where the EmployeeID=4
Update Employees set EmployeeName ='Jon Lord',DateOfBirth ='09-16-1982' where EmployeeID=4
The DELETE Statement Example with where clause
The
"Delete statement on the Employees" table updates the table based on the criteria
as shown. It will delete the record where the EmployeeID=2
below.
Delete from Employees where EmployeeID=2