Logical Operators in SQL

Documentation Index

Fetch the complete documentation index at: https://rajanand.org/llms.txt

Use this file to discover all available pages before exploring further.

​

Key Points About Logical Operators

1. Combining Conditions: Logical operators are used to combine multiple conditions in a query.
2. Common Operators: The most commonly used logical operators are AND, OR, and NOT.
3. Order of Evaluation: Conditions are evaluated in the following order: NOT, AND, OR. Use parentheses to change the order of evaluation.
4. AND: Returns TRUE if all conditions are true.
5. OR: Returns TRUE if at least one condition is true.
6. NOT: Negates a condition, returning TRUE if the condition is false.
7. EXISTS: Used to test for the existence of rows in a subquery.
8. IN: Used to compare a value against a list or subquery.
9. LIKE: Used for pattern matching with wildcards (% and _).
10. BETWEEN: Used to filter values within a range (inclusive).
11. Use Cases: Logical operators are used in WHERE, HAVING, and JOIN clauses to filter and combine data.

​

Common Logical Operators

​

AND Operator

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

SELECT Name, City, Salary
FROM Employees
WHERE State = 'Tamil Nadu' AND Salary > 50000;

​

OR Operator

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

SELECT Name, City, Salary
FROM Employees
WHERE City = 'Chennai' OR City = 'Madurai';

​

NOT Operator

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

SELECT Name, City, Salary
FROM Employees
WHERE NOT State = 'Tamil Nadu';

​

EXISTS Operator

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

SELECT Name, City
FROM Employees
WHERE EXISTS (
    SELECT 1
    FROM Departments
    WHERE Departments.DepartmentID = Employees.DepartmentID
    AND Departments.DepartmentName = 'HR'
);

​

IN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

SELECT Name, City
FROM Employees
WHERE City IN ('Chennai', 'Madurai');

​

LIKE Operator

• %: Matches zero or more characters.
• _: Matches exactly one character.

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

SELECT Name, City
FROM Employees
WHERE Name LIKE 'K%';

​

BETWEEN Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SELECT Name, Salary
FROM Employees
WHERE Salary BETWEEN 50000 AND 60000;

​

Combining Logical Operators

SELECT Name, City, Salary
FROM Employees
WHERE (State = 'Tamil Nadu' AND Salary > 50000) OR State = 'Puducherry';

SELECT Name, City, Percentage
FROM Students
WHERE (State = 'Tamil Nadu' AND Percentage > 90) OR State = 'Karnataka';

SELECT CustomerName, City
FROM Orders
WHERE EXISTS (
    SELECT 1
    FROM Customers
    WHERE Customers.CustomerID = Orders.CustomerID
    AND Customers.State = 'Tamil Nadu'
);

SELECT Name, Percentage
FROM Students
WHERE CourseID IN (101, 102) AND Percentage > 90;

SELECT CustomerName, City
FROM Customers
WHERE CustomerName LIKE '%an%';

SELECT OrderID, OrderDate, Amount
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

​

Key Takeaways

1. Logical operators (AND, OR, NOT) are used to combine or negate conditions in SQL queries.
2. Use parentheses to control the order of evaluation when combining multiple logical operators.
3. AND returns TRUE if all conditions are true, OR returns TRUE if at least one condition is true, and NOT negates a condition.
4. EXISTS: Checks if a subquery returns any rows.
5. IN: Compares a value against a list or subquery.
6. LIKE: Performs pattern matching using wildcards (% and _).
7. BETWEEN: Filters values within a specified range (inclusive).
8. These operators are essential for writing flexible and efficient queries in SQL.
9. Logical operators are essential for creating complex conditions in WHERE, HAVING, and JOIN clauses.