SQL Operators

SQL operators can be categorized as Arithmetic, Bitwise, Logical, Comparison and Compound operators. They are represented by symbols which are used to execute actions on data within SQL queries.

The goal of this article is to show you how to use SQL operators and some real-world use cases.

Arithmetic Operators

Used to perform basic mathematical calculations such as addition and subtraction.

Addition (+)

Returns the sum of two specified variables or columns in a table

-- SELECT x + y; SUM OF x and Y
SELECT 25 + 3; -- Returns 28

The following example increases the salary of an employee by adding a bonus

SELECT
 employee_id, salary, bonus,
 salary + bonus AS total_salary FROM employee;
-- AS is an alias that creates a tempory name for a column
  

Subtraction (-)

Use this operator to subtract two columns. For example, we can calculate how much profit a sale made

SELECT
  id, product_name, revenue, cost,
  revenue - cost AS profit
FROM sales;
   

Multiplication (*)

Used to multiply any two numeric expressions.

We can retrieve new product listing price from a products table after a discount. Suppose we have a 20% discount

SELECT product_id, name, original_price, 
  original_price * 0.8 AS discount_price
FROM products;

Division (/)

Divides one number by another. You can find the hourly rate of an employee by dividing their salary by number of working hours

SELECT eployee_id, full_name, salary, total_hours, 
  salary / total_hours AS hourly_rate
FROM employees;

Modulus (%)

Returns the remainder of a division.

SELECT dividend % divisor;
SELECT 10 % 3; -- returns 1

Comparison Operator

Used to test the similarity between two expressions. The result is a Boolean data type which can either be true, false or unknown.

Equals (=)

Filters results that are equal to the specified value.

SELECT user_id, username 
   FROM users
   WHERE age = 25;

Greater Than (>) and Less Than (<)

Also compares two variables and returns true or false if the value is greater than or less than

SELECT age > 25;
--OR
SELECT age < 18;

Greater Than or Equal to (≥) and Less Than or Equal to (≤)

Get Gracevalerie’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Compares two variables if they are equal, greater or less than.

SELECT * FROM users WHERE age >= 25; -- if age is 25 or more, returns true
--OR
SELECT * FROM users WHERE age <= 18; -- if age is 18 or less, returns true

Not Equal To (<>) and (!=)

Filters results whose operands are not equal to each other.

SELECT user_id, name
FROM users
WHERE user_id != 7 AND user_id <> 10;
-- Filters out users with user_id 7 and 10 and returns the rest

Logical Operators

Returns a Boolean data type of either true, false or unknown for some comparison condition.

AND

Returns true if both conditions of the expression are true. The AND operator is always evaluated first when other logical operators are present.

SELECT product_id, product_name
FROM products
WHERE category = "outdoor gear" AND price > 80
-- retrieves products whose category is outdoor gear and their price is more than 80

OR

Returns true if at least one condition of the expression is true.

-- condition TRUE OR FALSE returns TRUE
SELECT product_id, product_name
FROM products
WHERE category = "outdoor gear" OR price > 80
-- retrieves products whose category is outdoor gear OR their price is more than 80. Both conditons do not have to be true.

NOT

Returns the reverse value of any Boolean expression by negating it.

SELECT id, name, department, active
FROM employees
WHERE NOT active;
-- since the WHERE clause retrieves conditions that are true, NOT will negate the condition and exclude rows where active = TRUE

IN

Determines whether the specified value matches any value in the list

SELECT product_name, cost
FROM products
WHERE category IN ('furniture', 'electronics', 'gear');

Using IN is equivalent to OR as it looks for any one condition that is true.

LIKE

Use this operator to look for a specific character string that matches a specified pattern. It is more flexible when finding wildcard characters as opposed to using comparison operators. Accepted wildcard characters include:

• (%) — represents any string of zero or more characters.

-- match any string beginning with the letter "G"
SELECT id, name FROM users WHERE name LIKE 'G%' 
-- Find users whose names end with "e"
SELECT id, name FROM users WHERE name LIKE '%e';

-- match string with multiple characters inside
SELECT id, name FROM users WHERE name LIKE '%ce%

• (_) underscore — finds exactly one character. You can use multiple underscores where each underscore represents one character. For example ____ matches values with exactly four characters.
• ([]) square brackets — finds a single character within the specified range e.g. [a-f]
• ([^]) finds a single character not within the specified range. e.g ([^ghij])

SET OPERATORS

They combine the results of two or more queries into a single query.

UNION — Merges the results from both queries.

INTERSECT —returns distinct rows from both queries.

EXCEPT — returns distinct rows from the left query that are not returned by the right query.

REFERENCES

LIKE (Transact-SQL) — SQL Server | Microsoft Learn