Operators in SQL

Operators are symbols that specify the type of calculation that the different expressions perform. According to their nature, we can divide them into:

• Arithmetic operators.
• Comparison operators.
• Logical operators.
• String Concatenation operators.

Although the operators are not exclusive to the use of the WHERE clause of a SELECT, in this section we will focus on the most used in it for filtering and applying the conditions of a query. However, the user has access to more information in the Microsoft documentation regarding operator types.

Comparison operators

Comparison operators test whether two expressions are the same. Among these comparators we can find:

Equality operator (=)

This condition will return the record(s) whose field value is equal to the condition we are looking for:

SELECT *
FROM Customers c
WHERE c.customerId = 3;

You can also filter by text string:

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders O
WHERE o.productDescription= 'Sony Game Console';

Greater than operator (>)

This operator will return the records whose table field value is greater than the value we are entering in the condition:

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders O
WHERE o.price > 549.95;

Greater than or equal operator (>=)

This operator will return the records whose table field value is greater than or equal to the value we are introducing in the condition:

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders O
WHERE o.price >= 549.95;

Less than operator (<)

This operator will return the records whose table field value is less than the value we are introducing in the condition:

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders o
WHERE o.price < 549.95;

Less than or equal operator (<=)

This operator will return the records whose table field value is less than or equal to the value we are introducing in the condition:

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders O
WHERE o.price <= 549.95;

Not equal to operator (<>)

This operator will return the records whose table field value is different to the value we are introducing in the condition:

SELECT *
FROM Customers c
WHERE C.name <> 'Hugh';

This operator can also be written using the symbol:!=

SELECT *
FROM Customers c
WHERE C.name != 'Hugh';

Equal to NULL operator: IS NULL

This operator will return records whose table field value is NULL:

SELECT *
FROM Customers
WHERE bankAccount IS NULL;

Logical operators

Test for the truth of some condition.

LIKE operator

This operator allows us to search for records taking into account all or only part of a string chain:

SELECT *
FROM Customers c
WHERE c.address  LIKE '%Lake%';

The LIKE operator also allows searching by the beginning of a string.

SELECT o.product,
       o.price,
       o.productQuantity
FROM Orders O
WHERE o.product LIKE 'X%';

We can also search using the final character(s) of a string:

SELECT o.product, o.price, o.productQuantity
FROM Orders O
WHERE o.product LIKE '%Switch';

Conditional operator OR

This operator is used to validate several conditions If any of them is met, the SELECT will return the given record:

SELECT *
FROM Orders O
WHERE o.totalPrice > 2500
OR o.orderDate > '2022-08-01'

In this case, we observe that, although not all the records returned have a totalPrice greater than 2500, thus failing to fulfill said condition, the rest fulfill the respective condition on the dateOrder.

Conditional operator AND

This operator is used to validate several conditions All must be met for the SELECT to return the information:

SELECT *
FROM Orders O
WHERE o.totalPrice> 2500
AND o.orderDate > '2022-08-01'
AND o.product LIKE '%DOT%';

IN operator

The IN operator allows you to filter a list of values to return the corresponding records:

SELECT * FROM Customers c
WHERE C.name IN ('Janice', 'Helen', 'Kimberly');

BETWEEN operator

This operator will return records within a range of values.

SELECT * FROM Orders O
WHERE o.orderDate
BETWEEN '2022-07-15'
AND '2022-09-01';

Negation operator: NOT

This operator will return records that do not meet the specified conditions:

SELECT bankAccount
FROM Customers
WHERE bankAccount IS NOT NULL;

This run will return all the bank accounts in the Customers table.