Operators in The WHERE Clause
<>
Not equal. Note: In some versions of SQL this operator may be written as !=
[!charlist] Wildcard
SELECT * FROM Customers WHERE City LIKE '[!bsp]%'; SELECT * FROM Customers WHERE City NOT LIKE '[bsp]%';
_ Wildcard
SELECT * FROM Customers WHERE City LIKE '_erlin'; SELECT * FROM Customers WHERE City LIKE 'L_n_on';
IN Operator Examples
SELECT * FROM Customers WHERE Country IN ('Germany', 'France', 'UK'); SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'France', 'UK'); SELECT * FROM Customers WHERE Country IN (SELECT Country FROM Suppliers);
>
Greater than
>=
Greater than or equal
SQL Wildcard Characters
A wildcard character is used to substitute any other character(s) in a string. Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character Note: MS Access uses a question mark (?) instead of the underscore (_).
=
Equal
LIKE '%a'
Finds any values that ends with "a" SELECT * FROM Customers WHERE CustomerName LIKE '%a';
LIKE '%or%'
Finds any values that have "or" in any position SELECT * FROM Customers WHERE CustomerName LIKE '%or%';
LIKE '_r%'
Finds any values that have "r" in the second position SELECT * FROM Customers WHERE CustomerName LIKE '_r%';
LIKE 'a%'
Finds any values that starts with "a" SELECT * FROM Customers WHERE CustomerName LIKE 'a%';
LIKE 'a_%_%'
Finds any values that starts with "a" and are at least 3 characters in length SELECT * FROM Customers WHERE CustomerName LIKE 'a_%_%';
LIKE 'a%o'
Finds any values that starts with "a" and ends with "o" SELECT * FROM Customers WHERE ContactName LIKE 'a%o';
<
Less than
<=
Less than or equal
Combining AND, OR and NOT
SELECT * FROM Customers WHERE Country='Germany' AND (City='Berlin' OR City='München'); SELECT * FROM Customers WHERE NOT Country='Germany' AND NOT Country='USA';
BETWEEN Dates Example
SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
BETWEEN with IN Example
SELECT * FROM Products WHERE (Price BETWEEN 10 AND 20) AND NOT CategoryID IN (1,2,3);
BETWEEN 10 and 20
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
NOT BETWEEN Example
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN Text Values Example
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
NOT BETWEEN Text Values Example
SELECT * FROM Products WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' ORDER BY ProductName;
NOT Syntax
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
AND Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
BETWEEN
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
IN Operator
The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
LIKE
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character Note: MS Access uses a question mark (?) instead of the underscore (_). Tip: You can also combine any number of conditions using AND or OR operators.
The SQL AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators. The AND and OR operators are used to filter records based on more than one condition:
[charlist] Wildcard
The following SQL statement selects all customers with a City starting with "b", "s", or "p": SELECT * FROM Customers WHERE City LIKE '[bsp]%'; The following SQL statement selects all customers with a City starting with "a", "b", or "c": SELECT * FROM Customers WHERE City LIKE '[a-c]%';
IN
To specify multiple possible values for a column