SQL Operators

---

In SQL, operators play a crucial role in performing operations on data, whether it's for filtering, sorting, or transforming the results of a query. SQL operators are the building blocks of complex queries, and mastering them is essential for efficient database manipulation.

---

1. What Are SQL Operators?

SQL operators are symbols or keywords used to perform operations on data. They can manipulate values, compare values, or perform logical operations within SQL queries. Operators allow users to filter and modify data to get precise results.

SQL operators fall into the following categories:

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• Other Operators

---

2. Arithmetic Operators

Arithmetic operators in SQL are used to perform basic mathematical operations on numeric values. These operators work just like they do in other programming languages.

2.1 Addition (+)

• Description: Adds two values.

Example:

SELECT 10 + 5 AS result;  -- Returns 15

2.2 Subtraction (-)

• Description: Subtracts one value from another.

Example:

SELECT 10 - 5 AS result;  -- Returns 5

2.3 Multiplication (*)

• Description: Multiplies two values.

Example:

SELECT 10 * 5 AS result;  -- Returns 50

2.4 Division (/)

• Description: Divides one value by another.

Example:

SELECT 10 / 5 AS result;  -- Returns 2

2.5 Modulus (%)

• Description: Returns the remainder when one number is divided by another.

Example:

SELECT 10 % 3 AS result;  -- Returns 1

---

3. Comparison Operators

Comparison operators are used to compare two values. They are often used in the WHERE clause to filter query results based on specific conditions.

3.1 Equal to (=)

• Description: Checks if two values are equal.

Example:

SELECT * FROM employees
WHERE department = 'Sales';

3.2 Not Equal to (<> or !=)

• Description: Checks if two values are not equal.

Example:

SELECT * FROM employees
WHERE department != 'Sales';

3.3 Greater Than (>)

• Description: Checks if one value is greater than another.

Example:

SELECT * FROM employees
WHERE salary > 50000;

3.4 Less Than (<)

• Description: Checks if one value is less than another.

Example:

SELECT * FROM employees
WHERE salary < 50000;

3.5 Greater Than or Equal To (>=)

• Description: Checks if one value is greater than or equal to another.

Example:

SELECT * FROM employees
WHERE salary >= 50000;

3.6 Less Than or Equal To (<=)

• Description: Checks if one value is less than or equal to another.

Example:

SELECT * FROM employees
WHERE salary <= 50000;

3.7 BETWEEN

• Description: Checks if a value is within a specified range (inclusive).

Example:

SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;

3.8 IN

• Description: Checks if a value matches any value in a list of values.

Example:

SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing');

3.9 LIKE

• Description: Checks if a value matches a pattern. Used for string matching with wildcard characters.

Example:

SELECT * FROM employees
WHERE name LIKE 'J%';  -- Matches names starting with "J"

3.10 IS NULL

• Description: Checks if a value is NULL (no value).

Example:

SELECT * FROM employees
WHERE department IS NULL;

3.11 IS NOT NULL

• Description: Checks if a value is not NULL.

Example:

SELECT * FROM employees
WHERE department IS NOT NULL;

---

4. Logical Operators

Logical operators are used to combine multiple conditions in a WHERE clause. They help to refine queries with complex filtering requirements.

4.1 AND

• Description: Returns true if all conditions are true.

Example:

SELECT * FROM employees
WHERE department = 'Sales' AND salary > 50000;

4.2 OR

• Description: Returns true if any of the conditions are true.

Example:

SELECT * FROM employees
WHERE department = 'Sales' OR department = 'Marketing';

4.3 NOT

• Description: Negates a condition (i.e., returns true if the condition is false).

Example:

SELECT * FROM employees
WHERE NOT department = 'Sales';

---

5. Other SQL Operators

There are other operators in SQL that perform specialized operations:

5.1 EXISTS

• Description: Returns true if a subquery returns one or more records.

Example:

SELECT * FROM employees e
WHERE EXISTS (SELECT * FROM departments d WHERE e.department_id = d.department_id);

5.2 ANY and ALL

• Description: Used with comparison operators to compare a value to a set of values returned by a subquery. ANY returns true if any comparison is true, while ALL returns true if all comparisons are true.

Example:

SELECT * FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'Sales');

5.3 CONCAT

• Description: Used to concatenate two or more strings.

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

 

---

---