WHERE is a SQL command that begins a clause clarifying a statement:
JOIN Table1 AND Table2 WHERE Field1=Condition DELETE FROM Table WHERE Condition
The WHERE clause can also take multiple conditions. The correct syntax for this is:
[WHERE condition1 [AND|OR condition2] …]
The conditional here can be either a join condition or a filter condition. The WHERE clause can have up to forty conditions joined with AND|OR.
Overall, WHERE can be compared to an IF statement in most object-oriented programming languages including Basic and C++.
Most functions depending on WHERE are either join or filter conditions, and are always determined by the rows that the initial query returns. For instance, if your first query returns records of current employees, your WHERE statement can perform further operations on these records.
In join conditions, two or more tables in a SELECT statement are joined together in the WHERE clause. The AND or the OR operator must be used to connect the conditions. There are several types of join conditions; the following example is an outer join.
The situation: you have a furniture store with items on order and items in the warehouse. You need to know what you are going to be able to sell in the next week in order to put together your sales program. To get a complete list, use the SQL statement:
SELECT * FROM InStock, OnOrder WHERE ItemNumber <> 0 GROUP BY Item
In plain English, you're telling the SQL program: Select every record from the two tables InStock and OnOrder where the field ItemNumber has something in it. Group it by the field Item.
Filter conditions act somewhat similarly to Join conditions. However, instead of bringing data from two or more different tables together into the Query return, Filter specifies which records you want returned for an operation on one or more tables. Filters often use wild card characters, especially in the ESCAPE and WHERE clauses.
You should consider filters to be exclusive, rather than inclusive, operations; what you generally want is to exclude a bunch of things that don't match your single clause. You can filter for two or more conditions if you want, sort of funneling your data into a very specific query.
An example of filtering would be if you want to look at all employees who have missed ten or more days of work; you would SELECT employees WHERE (SUM(10+ days absent) (assuming absences are figured with a yes/no column):
SELECT Name FROM Employees WHERE (SUM Employees.absent >=10) ORDER BY Name
Filtering clauses can include the following comparison operators:
You can also use subqueries in your filter operations with syntax like the following:
FieldName Comparison Expression | ALL (Subquery) | ANY (Subquery) | SOME (Subquery)
Another possibility is the use of the EXISTS subquery, where the results for the WHERE clause are returned only if there is some data in the dataset. For instance, if you were looking for employees that had more than 20 days of absences, you might specify:
EXISTS (SELECT*FROM Employees; WHERE SUM Employees.absent >=20)
A common thing in WHERE statements is the use of wildcard. In Access SQL, the acceptable wildcards are:
| Character(s) in pattern | Matches in expression |
|---|---|
| ? or _ (underscore) | Any single character |
| * or % | Zero or more characters |
| # | Any single digit (0— 9) |
| [charlist] | Any single character in charlist |
| [!charlist] | Any single character not in charlist |
For instance, if you were looking for all employees with a last name beginning with W, you might specify:
SELECT LastName FROM Employees WHERE LastName = W* ORDER BY LastName