SQL Operators
🧠 SQL Operators in DQL (Data Query Language)
SQL operators are used inside the SELECT, WHERE, HAVING, and other clauses to filter, compare, or manipulate data.
🔹 1. Comparison Operators
Used to compare one value with another (numbers, text, or dates).
| Operator | Meaning | Example | Result |
= | Equal to | salary = 50000 | salary exactly 50000 |
!= or <> | Not equal to | salary <> 50000 | salary not 50000 |
> | Greater than | salary > 50000 | salary more than 50000 |
< | Less than | salary < 50000 | salary below 50000 |
>= | Greater than or equal to | salary >= 50000 | salary 50000 or more |
<= | Less than or equal to | salary <= 50000 | salary 50000 or less |
BETWEEN | Range check (inclusive) | salary BETWEEN 40000 AND 60000 | 40000 ≤ salary ≤ 60000 |
IN | Matches any from list | dept_id IN (1,3,5) | belongs to dept 1,3,5 |
NOT IN | Excludes values | dept_id NOT IN (2,4) | excludes depts 2,4 |
LIKE | Pattern match | name LIKE 'S%' | names starting with ‘S’ |
NOT LIKE | Pattern not match | name NOT LIKE '%a' | names not ending with ‘a’ |
IS NULL | Checks NULL | bonus IS NULL | column is empty |
IS NOT NULL | Checks not null | bonus IS NOT NULL | column has a value |
🔹 2. Logical Operators
Used to combine multiple conditions in WHERE or HAVING.
| Operator | Description | Example | Meaning |
AND | All conditions must be true | salary > 50000 AND dept_id = 3 | both must match |
OR | Any one condition true | salary > 70000 OR dept_id = 2 | either condition passes |
NOT | Negates condition | NOT salary < 50000 | opposite of condition |
ALL | Compare with all values in subquery | salary > ALL (SELECT salary FROM interns) | salary greater than all interns |
ANY | Compare with any one value | salary > ANY (SELECT salary FROM staff) | greater than at least one staff |
EXISTS | Checks if subquery returns rows | EXISTS (SELECT 1 FROM projects WHERE emp_id=e.id) | returns true if subquery has result |
🔹 3. Arithmetic Operators
Used to perform numeric calculations.
| Operator | Description | Example | Result |
+ | Addition | salary + bonus | sum |
- | Subtraction | salary - deduction | difference |
* | Multiplication | salary * 0.1 | 10% of salary |
/ | Division | salary / 12 | monthly salary |
% | Modulus (remainder) | id % 2 | even/odd check |
⚙️ Common in calculations inside
SELECTorHAVINGclauses.
🔹 4. String Operators
Used to manipulate or compare text data.
| Operator | Example | Description |
| ` | orCONCAT()` | |
LIKE | name LIKE 'S%' | Starts with ‘S’ |
LOWER(), UPPER() | LOWER(name) | Change case |
TRIM(), LTRIM(), RTRIM() | TRIM(name) | Remove spaces |
SUBSTRING() | SUBSTRING(name,1,3) | Extract substring |
LENGTH() or LEN() | LENGTH(name) | Count characters |
🔹 5. Set Operators
Used to combine results from multiple queries.
| Operator | Description | Example |
UNION | Combines results (removes duplicates) | SELECT name FROM emp UNION SELECT name FROM mgr |
UNION ALL | Combines results (keeps duplicates) | SELECT name FROM emp UNION ALL SELECT name FROM mgr |
INTERSECT | Common records in both | SELECT dept FROM emp INTERSECT SELECT dept FROM mgr |
EXCEPT or MINUS | Returns records in first query but not in second | SELECT dept FROM emp EXCEPT SELECT dept FROM mgr |
🔹 6. Aggregate / Grouping Functions
Used with DQL for analytics.
| Function | Description | Example |
COUNT() | Count of rows | COUNT(*) |
SUM() | Total | SUM(salary) |
AVG() | Average | AVG(salary) |
MAX() | Maximum value | MAX(salary) |
MIN() | Minimum value | MIN(salary) |
ROUND() | Round off number | ROUND(AVG(salary), 2) |
Used mostly in
SELECT,GROUP BY, andHAVING.
🔹 7. Special Operators (Advanced)
| Operator | Description | Example |
CASE | Conditional logic | CASE WHEN salary>50000 THEN 'HIGH' ELSE 'LOW' END AS Grade |
COALESCE() | Returns first non-null value | COALESCE(bonus,0) |
NVL() | Replaces NULL with value | NVL(bonus,0) |
CAST() / CONVERT() | Change data type | CAST(salary AS VARCHAR) |
NULLIF() | Returns NULL if two expressions are equal | NULLIF(a,b) |
🔹 8. Bitwise Operators (less common in DQL, but used in DB logic)
| Operator | Description | Example |
& | Bitwise AND | 5 & 3 = 1 |
| ` | ` | Bitwise OR |
^ | Bitwise XOR | 5 ^ 3 = 6 |
~ | Bitwise NOT | ~5 = -6 |
<< | Left shift | 5 << 1 = 10 |
>> | Right shift | 5 >> 1 = 2 |
🔹 9. Example — Using Multiple Operators Together
SELECT emp_name,
salary,
CASE
WHEN salary BETWEEN 60000 AND 90000 THEN 'MID'
WHEN salary > 90000 THEN 'HIGH'
ELSE 'LOW'
END AS salary_level
FROM employees
WHERE dept_id IN (1,2,3)
AND (emp_name LIKE 'S%' OR emp_name LIKE 'A%')
AND salary > 50000
ORDER BY salary DESC;
Explanation:
Filters departments 1,2,3
Name starts with S or A
Salary > 50000
Categorizes salary levels dynamically
✅ Summary
| Type | Operators / Functions | Used In | |
| Comparison | \=, <>, >, <, >=, <=, BETWEEN, IN, LIKE, IS NULL | WHERE, HAVING | |
| Logical | AND, OR, NOT, ALL, ANY, EXISTS | WHERE, HAVING | |
| Arithmetic | +, -, *, /, % | SELECT, WHERE | |
| String | LIKE, CONCAT, LENGTH, TRIM, SUBSTRING | SELECT, WHERE | |
| Set | UNION, UNION ALL, INTERSECT, EXCEPT | Combine queries | |
| Aggregate | COUNT, SUM, AVG, MIN, MAX | SELECT, GROUP BY | |
| Special | CASE, COALESCE, CAST, NULLIF | SELECT, WHERE | |
| Bitwise | &, | , ^, ~, <<, >> | Numeric conditions |