SQL: Operators

1. What are SQL Operators?

In SQL, operators are special symbols or keywords used to perform operations on values in a query.
They help in tasks like comparison, arithmetic, pattern matching, and logical evaluation.


2. Types of SQL Operators


A. Arithmetic Operators

Used for mathematical calculations.

Operator Description Example
+ Addition SELECT 10 + 5; → 15
- Subtraction SELECT 10 - 5; → 5
* Multiplication SELECT 10 * 5; → 50
/ Division SELECT 10 / 5; → 2
% Modulus (Remainder) SELECT 10 % 3; → 1


B. Comparison Operators

Used to compare two values and return TRUE or FALSE.

Operator Description Example
= Equal to WHERE salary = 50000
<> or != Not equal to WHERE department <> 'HR'
> Greater than WHERE age > 30
< Less than WHERE age < 25
>= Greater than or equal to WHERE marks >= 50
<= Less than or equal to WHERE marks <= 80


C. Logical Operators

Combine multiple conditions in a query.

Operator Description Example
AND Returns TRUE if all conditions are TRUE WHERE age > 25 AND city = 'Delhi'
OR Returns TRUE if any condition is TRUE WHERE age < 25 OR city = 'Mumbai'
NOT Reverses the result of a condition WHERE NOT city = 'Chennai'


D. Bitwise Operators (Some DBMS only)

Work on binary (bit-level) values.

Operator Description Example
& Bitwise AND a & b
` ` Bitwise OR
^ Bitwise XOR a ^ b


E. Special Operators

Used for special conditions in SQL.

Operator Description Example
BETWEEN Checks if a value is within a range WHERE age BETWEEN 20 AND 30
IN Checks if a value matches any value in a list WHERE city IN ('Delhi','Mumbai')
LIKE Searches for a pattern (used with % or _) WHERE name LIKE 'A%'
IS NULL Checks if a value is NULL WHERE salary IS NULL
EXISTS Checks if a subquery returns rows WHERE EXISTS (SELECT 1 FROM employees)


F. Pattern Matching with LIKE

  • % → Zero or more characters

  • _ → Exactly one character

Example:

SELECT * FROM employees
WHERE name LIKE 'J_n%';

Matches names like: "John", "Jane", "Junaid".


G. Concatenation Operator (Depends on DBMS)

  • SQL Server & MySQL: CONCAT() function

  • Oracle: || operator

Example:

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


Quick Tip: Operators are often combined for complex filtering:

SELECT * FROM employees
WHERE (salary > 50000 AND department = 'IT')
   OR (salary > 70000 AND department = 'Finance');