SQL logical operators are used to filter data by combining multiple conditions in SQL queries. These operators help in refining queries to fetch specific results.
1. ALL Operator
The ALL operator returns TRUE if all values in the subquery satisfy the condition.
Example:
SELECT Name FROM EmployeeDetails
WHERE Salary > ALL (SELECT Salary FROM EmployeeDetails WHERE Department = 'HR');
Explanation: Returns employees whose salary is greater than all salaries in the HR department.
2. AND Operator
The AND operator returns TRUE if all conditions are TRUE.
Example:
SELECT * FROM EmployeeDetails
WHERE Department = 'IT' AND Branch = 'Delhi';
Explanation: Fetches employees who belong to the IT department and work in the Delhi branch.
3. ANY Operator
The ANY operator returns TRUE if at least one value in the subquery meets the condition.
Example:
SELECT Name FROM EmployeeDetails
WHERE Salary > ANY (SELECT Salary FROM EmployeeDetails WHERE Department = 'HR');
Explanation: Returns employees whose salary is greater than the lowest salary in the HR department.
4. BETWEEN Operator
The BETWEEN operator checks if a value is within a specified range (inclusive).
Example:
SELECT * FROM EmployeeDetails
WHERE Salary BETWEEN 30000 AND 60000;
Explanation: Fetches employees with salaries between 30,000 and 60,000.
5. EXISTS Operator
The EXISTS operator returns TRUE if the subquery returns at least one record.
Example:
SELECT Name FROM EmployeeDetails
WHERE EXISTS (SELECT 1 FROM Departments WHERE Departments.ID = EmployeeDetails.DepartmentID);
Explanation: Fetches employee names if their department exists in the Departments table.
6. IN Operator
The IN operator checks if a value exists in a list of values.
Example:
SELECT * FROM EmployeeDetails
WHERE Department IN ('HR', 'Finance');
Explanation: Fetches employees belonging to either HR or Finance departments.
7. LIKE Operator
The LIKE operator is used for pattern matching with wildcards (% for multiple characters, _ for a single character).
Example:
SELECT * FROM EmployeeDetails
WHERE Name LIKE 'A%';
Explanation: Fetches employees whose names start with 'A'.
8. NOT Operator
The NOT operator negates a condition.
Example:
SELECT * FROM EmployeeDetails
WHERE NOT Department = 'HR';
Explanation: Fetches all employees except those in the HR department.
9. OR Operator
The OR operator returns TRUE if at least one condition is TRUE.
Example:
SELECT * FROM EmployeeDetails
WHERE Department = 'IT' OR Branch = 'Mumbai';
Explanation: Fetches employees who belong to the IT department or work in the Mumbai branch.
10. SOME Operator
The SOME operator works like ANY, returning TRUE if any subquery values meet the condition.
Example:
SELECT Name FROM EmployeeDetails
WHERE Salary > SOME (SELECT Salary FROM EmployeeDetails WHERE Department = 'HR');
Explanation: Returns employees whose salary is greater than at least one salary in the HR department.
Same functionality: SOME and ANY work exactly the same in SQL.
Conclusion
SQL logical operators are essential for refining queries and retrieving precise results. Combining them efficiently improves query performance and data retrieval accuracy.