In the previous article, we saw how to use WHERE to filter rows. But real life is rarely as simple as using a single condition.
Usually, we don’t just want “customers from Madrid”. We want:
“customers from Madrid AND who have purchased this month”
“products that cost BETWEEN 10 and 50 euros”
To express these business rules, T-SQL offers us a set of Operators that allow us to compare values and combine multiple logical conditions.
Comparison Operators
These are the most basic ones. They allow us to compare two expressions (a column against a value, or two columns against each other).
The result of this comparison is always TRUE, FALSE, or UNKNOWN (in case of nulls).
| Operator | Description | Example |
|---|---|---|
= | Equal to | Precio = 10 |
> | Greater than | Edad > 18 |
< | Less than | Stock < 5 |
>= | Greater than or equal to | Fecha >= '2023-01-01' |
<= | Less than or equal to | Nota <= 5 |
<> | Not equal to (SQL Standard) | Pais <> 'España' |
!= | Not equal to (Non-standard) | Pais != 'España' |
Although != works in T-SQL, it is recommended to use <>.
It is the operator defined by the ANSI SQL standard and ensures your code is more portable to other databases.
Logical Operators (AND, OR, NOT)
These operators are the “glue” that allows us to join several comparison conditions within the same WHERE.
For a row to be selected, both conditions must be true. It is restrictive.
-- Customers from Madrid WHO ARE ALSO active
SELECT * FROM Clientes
WHERE Ciudad = 'Madrid' AND Activo = 1;
For a row to be selected, it is enough that one of the conditions is true. It is inclusive.
-- Customers who are from Madrid OR Barcelona
SELECT * FROM Clientes
WHERE Ciudad = 'Madrid' OR Ciudad = 'Barcelona';
Inverts the result of the condition. If it’s TRUE it becomes FALSE, and vice versa.
-- Customers who are NOT from Madrid
SELECT * FROM Clientes
WHERE NOT Ciudad = 'Madrid'; -- Equivalent to Ciudad <> 'Madrid'
Operator Precedence
SQL Server has an operator precedence order. The AND operator is evaluated before OR.
Imagine we want to search for:
Customers from Madrid OR Barcelona AND who are over 18 years old
-- WARNING! This query is INCORRECTLY formulated
SELECT * FROM Clientes
WHERE Ciudad = 'Madrid' OR Ciudad = 'Barcelona' AND Edad > 18;
What does SQL do here? Due to precedence, it understands this:
- Customers from Barcelona over 18 years old.
- OR any customer from Madrid (regardless of age).
To correct it, we must use parentheses to force the order of evaluation, just like in mathematics:
-- CORRECT query
SELECT * FROM Clientes
WHERE (Ciudad = 'Madrid' OR Ciudad = 'Barcelona') AND Edad > 18;
When in doubt, use parentheses. Better an extra parenthesis than an error.
It’s also sometimes useful to use line breaks to make it easier to read (they don’t affect the query, but improve readability).
Special Operators
T-SQL includes certain operators that, although we could simulate with AND and OR, make the code much more readable and elegant.
BETWEEN (Range)
Used to search for values within a range. Important: BETWEEN is inclusive. It includes both the start and end values.
-- Search for products between 10 and 50 euros (both included)
SELECT * FROM Productos
WHERE Precio BETWEEN 10 AND 50;
-- Equivalent to:
-- WHERE Precio >= 10 AND Precio <= 50;
IN (List of values)
Allows us to specify a list of possible values. It’s much cleaner than writing multiple OR statements.
-- Customers from these three cities
SELECT * FROM Clientes
WHERE Ciudad IN ('Madrid', 'Barcelona', 'Valencia');
-- Equivalent to:
-- WHERE Ciudad = 'Madrid' OR Ciudad = 'Barcelona' OR Ciudad = 'Valencia';
LIKE (Text patterns)
Until now we have searched for exact text (=). But what if we want to find all customers whose last name starts with “L”?
The LIKE operator allows us to search for patterns using wildcards:
%(Percent): Represents any string of zero or more characters._(Underscore): Represents exactly one character.
-- Last names that start with "Garc" (García, Garzón, Garcés...)
SELECT * FROM Empleados
WHERE Apellido LIKE 'Garc%';
-- Emails that end in ".com"
SELECT * FROM Clientes
WHERE Email LIKE '%.com';
-- Contains "Llamas" anywhere in the name
SELECT * FROM Clientes
WHERE Nombre LIKE '%Llamas%';
-- A 3-letter code where the first is A and the last is C (e.g., ABC, ADC, AXC)
SELECT * FROM Productos
WHERE Codigo LIKE 'A_C';
LIKE Performance:
- Using
LIKE 'Texto%'is fast because it can use indexes. - Using
LIKE '%Texto'(starting with a wildcard) is very slow, as it forces SQL Server to read the entire table row by row to search for the text within.
