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.