Comprehensive Guide to SQL Operators

Introduction

SQL (Structured Query Language) is essential for managing and manipulating relational databases. Among its core functionalities are the various operators that perform arithmetic calculations, comparisons, and logical operations. This article elaborates on these operators, providing examples with expected outputs.

1. Arithmetic Operators

Arithmetic operators perform mathematical operations on numeric data.

SUM

The SUM function returns the total sum of a numeric column.

SELECT SUM(salary) AS total_salary FROM employees;
-- Expected output: total_salary
--                  -------------
--                  5000000

AVG

The AVG function returns the average value of a numeric column.

SELECT AVG(salary) AS average_salary FROM employees;
-- Expected output: average_salary
--                  ---------------
--                  75000

MIN

The MIN function returns the smallest value in a column.

SELECT MIN(salary) AS min_salary FROM employees;
-- Expected output: min_salary
--                  -----------
--                  30000

MAX

The MAX function returns the largest value in a column.

SELECT MAX(salary) AS max_salary FROM employees;
-- Expected output: max_salary
--                  -----------
--                  120000

COUNT

The COUNT function returns the number of rows that match a specified criterion.

SELECT COUNT(*) AS sales_count FROM employees WHERE department = 'Sales';
-- Expected output: sales_count
--                  ------------
--                  15

Basic Arithmetic Operators

• + (Addition): Adds two numbers.
• - (Subtraction): Subtracts the second number from the first.
• * (Multiplication): Multiplies two numbers.
• / (Division): Divides the first number by the second.
• % (Modulus): Returns the remainder of a division operation.

SELECT salary + 500 AS increased_salary FROM employees;
-- Expected output: increased_salary
--                  -----------------
--                  30500
--                  50500
--                  ...

SELECT salary - 500 AS decreased_salary FROM employees;
-- Expected output: decreased_salary
--                  -----------------
--                  29500
--                  49500
--                  ...

SELECT salary * 1.1 AS new_salary FROM employees;
-- Expected output: new_salary
--                  -----------
--                  33000
--                  55000
--                  ...

SELECT salary / 2 AS half_salary FROM employees;
-- Expected output: half_salary
--                  ------------
--                  15000
--                  25000
--                  ...

SELECT salary % 100 AS remainder FROM employees;
-- Expected output: remainder
--                  ---------
--                  0
--                  0
--                  ...

2. Comparison Operators

Comparison operators compare two values, returning true or false.

=

Equals. Checks if the values of two operands are equal.

SELECT * FROM employees WHERE salary = 50000;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  2  | Alice | HR         | 50000

>

Greater than. Checks if the value of the left operand is greater than the value of the right operand.

SELECT * FROM employees WHERE salary > 50000;
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  3  | Bob    | IT         | 60000
--                  4  | Carol  | Marketing  | 70000
--                  ...

<

Less than. Checks if the value of the left operand is less than the value of the right operand.

SELECT * FROM employees WHERE salary < 50000;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  1  | John  | Sales      | 30000

>=

Greater than or equal to. Checks if the value of the left operand is greater than or equal to the value of the right operand.

SELECT * FROM employees WHERE salary >= 50000;
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  2  | Alice  | HR         | 50000
--                  3  | Bob    | IT         | 60000
--                  ...

<=

Less than or equal to. Checks if the value of the left operand is less than or equal to the value of the right operand.

SELECT * FROM employees WHERE salary <= 50000;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  1  | John  | Sales      | 30000
--                  2  | Alice | HR         | 50000

<> or !=

Not equal to. Checks if the values of two operands are not equal.

SELECT * FROM employees WHERE salary <> 50000;
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  1  | John   | Sales      | 30000
--                  3  | Bob    | IT         | 60000
--                  ...

SELECT * FROM employees WHERE salary != 50000;
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  1  | John   | Sales      | 30000
--                  3  | Bob    | IT         | 60000
--                  ...

3. Logical Operators

Logical operators combine two or more conditions in a SQL statement.

AND

Combines two conditions and returns true if both conditions are true.

SELECT * FROM employees WHERE department = 'Sales' AND salary > 50000;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  5  | Dave  | Sales      | 60000

OR

Combines two conditions and returns true if either condition is true.

SELECT * FROM employees WHERE department = 'Sales' OR department = 'Marketing';
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  1  | John   | Sales      | 30000
--                  4  | Carol  | Marketing  | 70000

NOT

Negates a condition, returning true if the condition is false.

SELECT * FROM employees WHERE NOT department = 'Sales';
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  2  | Alice  | HR         | 50000
--                  3  | Bob    | IT         | 60000
--                  4  | Carol  | Marketing  | 70000

BETWEEN ... AND

Selects values within a given range. The range includes the end values.

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  1  | John  | Sales      | 30000
--                  2  | Alice | HR         | 50000

IN

Checks if a value is within a set of values.

SELECT * FROM employees WHERE country IN ('Nigeria', 'Ghana', 'Togo', 'Cameroun');
-- Expected output: id | name   | department | country
--                  -----------------------------------
--                  1  | John   | Sales      | Nigeria
--                  6  | Emma   | HR         | Ghana

LIKE

Searches for a specified pattern in a column. Commonly used with wildcards:

• %: Represents zero or more characters.
• _: Represents a single character.

SELECT * FROM employees WHERE name LIKE '%jo%';
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  1  | John   | Sales      | 30000
--                  7  | Johnson| IT         | 80000

SELECT * FROM employees WHERE name LIKE 'A%';
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  2  | Alice  | HR         | 50000
--                  8  | Andrew | Marketing  | 55000

SELECT * FROM employees WHERE name LIKE '%Keyword';
-- Expected output: id | name        | department | salary
--                  -------------------------------------
--                  9  | JaneKeyword| Marketing  | 60000

Check out this special post I wrote on the LIKE operator

IS NULL / IS NOT NULL

Checks if a column is null or not null.

SELECT * FROM employees WHERE manager_id IS NULL;
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  1  | John  | Sales      | 30000
--                  5  | Dave  | Sales      | 60000

SELECT * FROM employees WHERE manager_id IS NOT NULL;
-- Expected output: id | name   | department | salary
--                  ---------------------------------
--                  2  | Alice  | HR         | 50000
--                  3  | Bob    | IT         | 60000

4. WHERE Clause

The WHERE clause is used to filter records based on specified conditions.

SELECT * FROM employees WHERE department = 'Sales';
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  1

  | John  | Sales      | 30000
--                  5  | Dave  | Sales      | 60000

SELECT * FROM employees WHERE salary > 50000 AND department = 'Sales';
-- Expected output: id | name  | department | salary
--                  --------------------------------
--                  5  | Dave  | Sales      | 60000

Conclusion

SQL operators enable powerful manipulation and retrieval of data. Mastering these operators—arithmetic, comparison, and logical—allows for sophisticated database queries and efficient data management. Each example provided includes expected output to illustrate the practical application of these operators in SQL.

PS:
I love coffee, and writing these articles takes a lot of it! If you enjoy my content and would like to support my work, you can buy me a cup of coffee. Your support helps me to keep writing great content and stay energized. Thank you for your kindness!
Buy Me A Coffee.