Below are the commonly used Oracle SQL Operators with their definitions and examples.
| Operator | Description | Example |
|---|
| ALL | Used to compare a value with all values returned by a subquery. The condition is true only if it satisfies every value. | SELECT * FROM employee WHERE salary > ALL (SELECT salary FROM employee WHERE dept_no = 20); |
| AND | Combines two or more conditions. All conditions must be TRUE for the result to be returned. | SELECT * FROM employee WHERE dept_no = 10 AND salary > 30000; |
| ANY (SOME) | Compares a value with any one of the values returned by a subquery. The condition is true if it matches at least one value. | SELECT * FROM employee WHERE salary > ANY (SELECT salary FROM employee WHERE dept_no = 20); |
| BETWEEN | Selects values within a specified range (inclusive). | SELECT * FROM employee WHERE salary BETWEEN 20000 AND 50000; |
| IN | Checks whether a value exists in a specified list of values. | SELECT * FROM employee WHERE dept_no IN (10, 20, 30); |
| NOT | Reverses the result of a condition. | SELECT * FROM employee WHERE NOT dept_no = 10; |
| OR | Combines multiple conditions. Returns rows if any one condition is TRUE. | SELECT * FROM employee WHERE dept_no = 10 OR salary > 50000; |
| EXISTS | Checks whether a subquery returns at least one row. | SELECT * FROM department d WHERE EXISTS (SELECT 1 FROM employee e WHERE e.dept_no = d.dept_no); |
| LIKE | Searches for values matching a specified pattern using wildcard characters (% and _). | SELECT * FROM employee WHERE emp_name LIKE 'A%'; |
Wildcard Characters Used with LIKE
| Wildcard | Meaning | Example |
|---|
% | Represents zero or more characters | LIKE 'A%' → Names starting with A |
_ | Represents exactly one character | LIKE '_a%' → Second character is a |
Example Table: EMPLOYEE
| EMP_ID | EMP_NAME | DEPT_NO | SALARY |
|---|
| 101 | Amit | 10 | 25000 |
| 102 | Neha | 20 | 40000 |
| 103 | Ravi | 30 | 55000 |
| 104 | Ankit | 10 | 35000 |
| 105 | Pooja | 20 | 60000 |
Example Queries
-- AND
SELECT * FROM employee
WHERE dept_no = 10 AND salary > 30000;
-- OR
SELECT * FROM employee
WHERE dept_no = 10 OR salary > 50000;
-- NOT
SELECT * FROM employee
WHERE NOT dept_no = 20;
-- BETWEEN
SELECT * FROM employee
WHERE salary BETWEEN 30000 AND 60000;
-- IN
SELECT * FROM employee
WHERE dept_no IN (10, 30);
-- LIKE
SELECT * FROM employee
WHERE emp_name LIKE 'A%';
-- EXISTS
SELECT *
FROM department d
WHERE EXISTS (
SELECT 1
FROM employee e
WHERE e.dept_no = d.dept_no
);
-- ANY
SELECT *
FROM employee
WHERE salary > ANY (
SELECT salary
FROM employee
WHERE dept_no = 20
);
-- ALL
SELECT *
FROM employee
WHERE salary > ALL (
SELECT salary
FROM employee
WHERE dept_no = 20
);
Summary
- ALL → Compare with every value from a subquery.
- ANY → Compare with at least one value from a subquery.
- AND → All conditions must be true.
- OR → At least one condition must be true.
- NOT → Negates a condition.
- BETWEEN → Checks a range of values.
- IN → Checks membership in a list.
- EXISTS → Checks whether a subquery returns rows.
- LIKE → Matches text patterns using
% and _.
