SQL: WHERE Clause

One of the most useful feature of the SQL query, since it allows you to selectively retrieve only those rows that interest you. The Where clause is used to specify that only certain rows of the table are displayed, based on the criteria described in that Where clause.

The syntax is as follows:

SELECT column_name 
FROM derived_table 
WHERE conditions 

It is most easily understood by looking at a couple of examples.

Example #1

SELECT EMPLOYEEIDNO 
FROM EMPLOYEESTATISTICSTABLE 
WHERE POSITION = 'Manager' 

This displays the ID Numbers of all Managers. Generally, with text columns, stick to equal to or not equal to, and make sure that any text that appears in the statement is surrounded by single quotes (‘).

Example #2

SELECT EMPLOYEEIDNO, SALARY 
FROM EMPLOYEESTATISTICSTABLE 
WHERE POSITION = 'Manager' AND SALARY > 1000 

Multiple criterion in a Where clause can be combined using any of the logical operators. Here the query returns a list of all managers whose salary is greater than $1000.

Example #3

SELECT EmployeeAddressTable.Address, EmployeeAddressTable.LastName 
FROM EmployeeStatisticsTable,EmployeeAddressTable 
WHERE EmployeeAddressTable.EMPLOYEEIDNO = EmployeeStatisticsTable.EMPLOYEEIDNO 
AND EmployeeStatisticsTable.POSITION = 'Staff' 

It is possible to select fields from more than one table. In this case it is best to precede a field name with the name of the table from which it comes, followed by the dot operator ( . ). This must be done for fields of the same name, but from different tables that are used in the Select statement. The preceding example uses the Where clause to join multiple tables together in a single SQL statement. This SQL statement would return all employee names and ids where there is a matching record in the EmployeeAddressTable and EmployeeStatisticsTable tables based on EMPLOYEEIDNO and where the employee position is Staff.