SQL WHERE Clause

The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified condition.

Syntax

The basic syntax for the WHERE clause in a SELECT statement is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

For other SQL statements, the syntax is similar:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE FROM table_name
WHERE condition;

Operators Used in the WHERE Clause

The WHERE clause can contain various operators to define conditions:

Comparison Operators

Operator	Description
=	Equal
<> or !=	Not equal
>	Greater than
<	Less than
>=	Greater than or equal to
<=	Less than or equal to

Logical Operators

Logical operators are used to combine multiple conditions:

Operator	Description
AND	Displays a record if ALL the conditions separated by AND are TRUE
OR	Displays a record if ANY of the conditions separated by OR is TRUE
NOT	Reverses the meaning of the condition

Other Operators

• BETWEEN: Selects values within a given range.
• LIKE: Searches for a specified pattern in a column.
• IN: Selects any values in a list.

Examples

1. Selecting Records Based on a Specific Value

To select all customers from the "Customers" table who are located in "Germany":

SELECT CustomerName, ContactName
FROM Customers
WHERE Country = 'Germany';

2. Using the AND Operator

To select all customers from the "Customers" table who are in "Germany" AND have the city name "Berlin":

SELECT CustomerName, City
FROM Customers
WHERE Country = 'Germany' AND City = 'Berlin';

3. Using the OR Operator

To select all customers from the "Customers" table who are in "Germany" OR "Spain":

SELECT CustomerName, Country
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';

4. Using the BETWEEN Operator

To select all products from the "Products" table with a price between 10 and 20:

SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 20;

5. Using the LIKE Operator

The LIKE operator is used to search for a specified pattern in a column.

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Select customers starting with 'A':

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'A%';

Select customers ending with 'a':

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%a';

Select customers that have 'or' in any position:

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE '%or%';

Select customers that start with 'A' and are at least 3 characters long:

SELECT CustomerName
FROM Customers
WHERE CustomerName LIKE 'A__%';

6. Using the IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

Select customers who are in "Germany", "France", or "UK":

SELECT CustomerName, Country
FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');

7. Using the NOT Operator

The NOT operator displays records that do NOT meet the specified condition.

Select customers who are NOT in "Germany":

SELECT CustomerName, Country
FROM Customers
WHERE NOT Country = 'Germany';

Select customers who are NOT in "Germany", "France", or "UK":

SELECT CustomerName, Country
FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');