SQL WHERE Clauses

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

Syntax

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

The condition can be any valid SQL expression that evaluates to true or false. It typically involves comparison operators.

Comparison Operators

The following comparison operators can be used in the WHERE clause:

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

Logical Operators

The WHERE clause can also use logical operators to combine conditions:

AND: Displays a record if ALL of the conditions separated by AND are true.
OR: Displays a record if ANY of the conditions separated by OR is true.
NOT: Negates a condition, displaying records that do NOT meet the condition.

Example: Selecting Customers from a Specific City

Let's say we have a Customers table:

+------------+-----------+----------+----------------------+
| CustomerID | Name      | City     | Country              |
+------------+-----------+----------+----------------------+
| 1          | Alfreds   | Berlin   | Germany              |
| 2          | Maria     | London   | UK                   |
| 3          | Ana       | Mexico D | Mexico               |
| 4          | Thomas    | London   | UK                   |
| 5          | Christina | Paris    | France               |
+------------+-----------+----------+----------------------+

To select all customers from London:

SELECT CustomerID, Name, City, Country
FROM Customers
WHERE City = 'London';

Result:

+------------+--------+--------+---------+
| CustomerID | Name   | City   | Country |
+------------+--------+--------+---------+
| 2          | Maria  | London | UK      |
| 4          | Thomas | London | UK      |
+------------+--------+--------+---------+

Example: Using AND and OR

To select customers from Germany AND from Berlin:

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

To select customers from Berlin OR from Paris:

SELECT Name, Country, City
FROM Customers
WHERE City = 'Berlin' OR City = 'Paris';

Other Operators

BETWEEN: Selects values within a range.
IN: Selects values that match any value in a list.
LIKE: Selects values based on a pattern matching.
IS NULL: Selects records where the specified column is NULL.
IS NOT NULL: Selects records where the specified column is NOT NULL.

Example: Using LIKE

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

Wildcard characters:

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

To select customers whose name starts with 'A':

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

To select customers whose name ends with 'a':

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

To select customers whose name contains 'ar':

SELECT Name
FROM Customers
WHERE Name LIKE '%ar%';

Example: Using IN

To select customers who are from 'Berlin', 'Paris', or 'London':

SELECT Name, City
FROM Customers
WHERE City IN ('Berlin', 'Paris', 'London');

Example: Using BETWEEN

If we had a numerical column, e.g., CustomerID:

SELECT Name, CustomerID
FROM Customers
WHERE CustomerID BETWEEN 2 AND 4;

This is equivalent to:

SELECT Name, CustomerID
FROM Customers
WHERE CustomerID >= 2 AND CustomerID <= 4;

Example: Using IS NULL

To select customers where the Region column is NULL (assuming such a column exists):

SELECT Name, Region
FROM Customers
WHERE Region IS NULL;