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