WHERE Clause

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


SELECT * 
FROM CUSTOMERS
WHERE (Country = 'Finland' OR Country = 'Brazil')



SELECT * 
FROM CUSTOMERS
WHERE CompanyName = 'Island Trading'


--When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).

SELECT CompanyName 
,ContactName
FROM CUSTOMERS
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

SELECT     * 
FROM EMPLOYEES
WHERE EMPLOYEES.Extension > 3000


--This works in both Access and SQL server

SELECT     * 
FROM EMPLOYEES
WHERE Extension = '428'


--This works in SQL Server (despite Extension being a text column) but not in Access

SELECT     * 
FROM EMPLOYEES
WHERE Extension = 428



OR Clause





Operator Precedence

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.


Comparison Operators

>Greater Than
>=Greater Than or Equal To
=Equal
<Less Than
<=Less Than or Equal To
<>Not Equal


Logical Operators

Whenever you use both the AND and OR operators together always use parentheses to ensure that you get the correct results.

ANDRequires both expressions on either side of the AND operator to be true for data to be returned
ORRequires at least one expression on either side of the OR operator to be true in order for data to be returned.
NOTUsed to match any condition opposite of the one defined.


??? Operators

BETWEENUsed to determine whether a value of an expression falls within a specified range of values
INUsed to match conditions in a list of expressions
LIKEUsed to match patterns in data
IS NULLUsed to determine if a field contains data
IS NOT NULLUsed to determine if a field does not contain data.


Arithmetic Operators

These are used to perform mathematical calculations.
These are listed in order of precedence.

-Negation
^Exponentiation (not available in SQL Server ?)
/Divide
*Multiple
%Modulus
=Plus
-Minus
  
  
!=, ^=Test for inequality
=Test for equality
||String concatenation
:=Assignment operator


String Operators


When using SQL on text data, "alfred" is greater than "a" (like in a dictionary).

SELECT     CompanyName 
      ,ContactName
FROM CUSTOMERS
WHERE CompanyName > 'g'





© 2017 Better Solutions Limited. All Rights Reserved. © 2017 Better Solutions Limited

PrevNext