Logical Operators in SQL

Logical operators in SQL are used to combine or negate conditions in WHERE, HAVING, and JOIN clauses. They allow you to create complex conditions by combining multiple expressions. The most commonly used logical operators are AND, OR, and NOT.

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

The AND operator returns TRUE if all the conditions separated by AND are true.

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

Retrieve employees from Tamil Nadu with a salary greater than ₹50,000.

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

Name	City	Salary
Bala	Coimbatore	60000
Raj	Madurai	70000

OR Operator

The OR operator returns TRUE if at least one of the conditions separated by OR is true.

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

Retrieve employees from Chennai or Madurai.

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

Name	City	Salary
Anand	Chennai	50000
Raj	Madurai	70000

NOT Operator

The NOT operator negates a condition, returning TRUE if the condition is false.

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

Retrieve employees who are not from Tamil Nadu.

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

Name	City	Salary
Kavitha	Karaikal	55000

EXISTS Operator

The EXISTS operator checks if a subquery returns any rows. It returns TRUE if the subquery returns at least one row, otherwise FALSE.

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

Check if there are any employees in the HR department.

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

Name	City
Anand	Chennai

IN Operator

The IN operator checks if a value matches any value in a list or subquery.

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

Retrieve employees from Chennai or Madurai.

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

Name	City
Anand	Chennai
Raj	Madurai

LIKE Operator

The LIKE operator is used for pattern matching with wildcards:

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

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

Retrieve employees whose names start with the letter “K”.

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

Name	City
Kavitha	Karaikal
Kumar	Trichy

BETWEEN Operator

The BETWEEN operator checks if a value lies within a specified range (inclusive).

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

Retrieve employees with a salary between ₹50,000 and ₹60,000.

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

Name	Salary
Anand	50000
Bala	60000
Kavitha	55000

Combining Logical Operators

You can combine multiple logical operators to create complex conditions. Use parentheses to control the order of evaluation.

Retrieve employees from Tamil Nadu with a salary greater than ₹50,000 or employees from Puducherry.

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

Name	City	Salary
Bala	Coimbatore	60000
Raj	Madurai	70000
Kavitha	Karaikal	55000

Suppose you have a table named Students that stores student details.

Table: Students

StudentID	Name	City	State	Percentage
1	Ram	Chennai	Tamil Nadu	92
2	Karthik	Coimbatore	Tamil Nadu	88
3	David	Bangalore	Karnataka	95
4	Kannan	Karaikal	Puducherry	91
5	Siva	Madurai	Tamil Nadu	89

Retrieve students from Tamil Nadu with a percentage greater than 90 or students from Karnataka.

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

Name	City	Percentage
Ram	Chennai	92
David	Bangalore	95

Check if there are any orders placed by customers from Tamil Nadu.

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

Retrieve students who scored above 90% in Mathematics or Science.

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

Retrieve customers whose names contain the substring “an”.

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

Retrieve orders placed between January 1, 2023, and December 31, 2023.

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.