The where clause is used with a relational statement to isolate the object element or row
Lets you restrict the rows returned
If you restrict rows based on the result of a group function you must have a GROUP BY as well as a HAVING clause
WHERE (Country = 'Finland' OR Country = 'Brazil')
WHERE CompanyName = 'Island Trading'
--When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).
WHERE CompanyName > 'g'
Note: Text strings should always be enclosed in quotes. if one of your values is a text string containing blanks or special characters you must enclose the swhole string in quotes.
Note: If you are comparing date values you must enclose the date constants in pound sign (#)
--This works in SQL Server despite Extension being a text column - doesn't work in Access
WHERE EMPLOYEES.Extension > 3000
--This works in both Access and SQL server
WHERE Extension = '428'
--This works in SQL Server (despite Extension being a text column) but not in Access
WHERE Extension = 428
When multiple operators are used in a WHERE clause the operator precedence determines the order in which the operations are performed.
If two operators in an expression have the same operator precedence level then they are evaluated from left to right.
Since parentheses have the highest precedence level these can be used to override the default operator precedence.
The following tables are shown in the order of precedence.
|>=||Greater Than or Equal To|
|<=||Less Than or Equal To|
Whenever you use both the AND and OR operators together always use parentheses to ensure that you get the correct results.
|AND||Requires both expressions on either side of the AND operator to be true for data to be returned|
|OR||Requires at least one expression on either side of the OR operator to be true in order for data to be returned.|
|NOT||Used to match any condition opposite of the one defined.|
|BETWEEN||Used to determine whether a value of an expression falls within a specified range of values|
|IN||Used to match conditions in a list of expressions|
|LIKE||Used to match patterns in data|
|IS NULL||Used to determine if a field contains data|
|IS NOT NULL||Used to determine if a field does not contain data.|
These are used to perform mathematical calculations.
These are listed in order of precedence.
|^||Exponentiation (not available in SQL Server ?)|
|!=, ^=||Test for inequality|
|=||Test for equality|
When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).
WHERE CompanyName > 'g'
© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited