MySQL Logical Operators

Logical operators in MySQL are used in the WHERE clause to combine multiple conditions and filter rows based on complex logic. These operators include AND, OR, NOT, and others that help in defining relationships between conditions.

List of Logical Operators in MySQL

Operator	Description
AND	Returns `TRUE` if all conditions are true
OR	Returns `TRUE` if any condition is true
NOT	Returns `TRUE` if the condition is false
XOR	Returns `TRUE` if one (and only one) condition is true
IS TRUE	Checks if a condition evaluates to true
IS FALSE	Checks if a condition evaluates to false
IS NULL	Checks if a column has a NULL value

1. AND Operator

The AND operator combines multiple conditions and returns rows only if all conditions are true.

Syntax

SELECT column1, column2 
FROM table_name
WHERE condition1 AND condition2;

Example

Find employees who are older than 30 and work in the Sales department.

SELECT * FROM Employees WHERE Age > 30 AND Department = 'Sales';

2. OR Operator

The OR operator combines multiple conditions and returns rows if any condition is true.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 OR condition2;

Example

Find employees who are older than 40 or earn more than 60,000.

SELECT * FROM Employees WHERE Age > 40 OR Salary > 60000;

3. NOT Operator

The NOT operator negates a condition, returning rows where the condition is false.

Syntax

SELECT column1, column2
FROM table_name
WHERE NOT condition;

Example

Find employees who are not in the IT department.

SELECT * FROM Employees WHERE NOT Department = 'IT';

4. XOR Operator

The XOR operator returns TRUE if one (and only one) condition is true. If both conditions are true or both are false, it returns FALSE.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition1 XOR condition2;

Example

Find employees who are either older than 30 or work in HR, but not both.

SELECT * FROM Employees WHERE Age > 30 XOR Department = 'HR';

5. IS TRUE and IS FALSE

These operators explicitly check whether a condition evaluates to TRUE or FALSE.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition IS TRUE;

Example

Find employees where the salary comparison condition is true.

SELECT * FROM Employees WHERE (Salary > 60000) IS TRUE;

6. Combining Logical Operators

Logical operators can be combined to create more complex conditions. Use parentheses () to group conditions and control precedence.

Example

Find employees who are in the IT or Sales department and earn more than 50,000.

SELECT * FROM Employees 
WHERE (Department = 'IT' OR Department = 'Sales') AND Salary > 50000;

Precedence:

NOT has the highest precedence.
AND has a higher precedence than OR.

Example Table: `Employees`

EmployeeID	FirstName	LastName	Age	Department	Salary	Country
1	John	Smith	30	Sales	50000	USA
2	Jane	Doe	45	HR	65000	UK
3	Sarah	Johnson	28	IT	55000	USA
4	Michael	Brown	50	Sales	70000	Canada

Examples with Output

AND Example

SELECT * FROM Employees WHERE Age > 30 AND Department = 'Sales';

Output:

EmployeeID	FirstName	LastName	Age	Department	Salary	Country
4	Michael	Brown	50	Sales	70000	Canada

OR Example

SELECT * FROM Employees WHERE Age > 40 OR Salary > 60000;

Output:

EmployeeID	FirstName	LastName	Age	Department	Salary	Country
2	Jane	Doe	45	HR	65000	UK
4	Michael	Brown	50	Sales	70000	Canada

NOT Example

SELECT * FROM Employees WHERE NOT Department = 'IT';

Output:

EmployeeID	FirstName	LastName	Age	Department	Salary	Country
1	John	Smith	30	Sales	50000	USA
2	Jane	Doe	45	HR	65000	UK
4	Michael	Brown	50	Sales	70000	Canada

Combined Example

SELECT * FROM Employees 
WHERE (Department = 'IT' OR Department = 'Sales') AND Salary > 50000;

Output:

EmployeeID	FirstName	LastName	Age	Department	Salary	Country
3	Sarah	Johnson	28	IT	55000	USA
4	Michael	Brown	50	Sales	70000	Canada

Conclusion

Logical operators allow you to build advanced filtering criteria in MySQL queries. By understanding the precedence of operators and how to combine them effectively, you can perform complex data retrieval operations.