Comprehensive Guide to SQL Operators
Tags: sql, sqloperators, database, queries
Published: 2024-05-31 21:58:39

#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.

```sql
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.

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

### MIN
The `MIN` function returns the smallest value in a column.

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

### MAX
The `MAX` function returns the largest value in a column.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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](https://dev.to/kellyblaire/how-to-use-the-like-operator-in-sql-4kem)

### `IS NULL` / `IS NOT NULL`
Checks if a column is null or not null.

```sql
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.

```sql
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](https://buymeacoffee.com/yafskkw).