MySQL

In MySQL, logical operators are used to combine multiple conditions in a query, allowing you to create more complex filtering criteria. These operators return boolean values (TRUE or FALSE) based on the conditions provided. Logical operators like AND, OR, and NOT help you define conditions that must be met to retrieve specific rows from the database.

Here’s a detailed explanation of the most common logical operators in MySQL, along with examples:

1. AND Operator:

   • The AND operator is used to combine two or more conditions. All conditions must be true for the row to be included in the result set.
   • Syntax:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id = 2 
   AND salary > 50000;

   • This query retrieves employees who belong to department 2 and have a salary greater than 50,000.

2. OR Operator:

   • The OR operator is used to combine two or more conditions, where at least one of the conditions must be true for the row to be included in the result set.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id = 2 
   OR salary > 50000;

   • This query retrieves employees who either belong to department 2 or have a salary greater than 50,000 (or both).

3. NOT Operator:

   • The NOT operator is used to negate a condition. It returns true if the condition is false and false if the condition is true.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE NOT department_id = 2;

   • This query retrieves employees who do not belong to department 2.

4. Combining AND and OR:

   • You can combine AND and OR operators in the same query to create more complex conditions. Parentheses are used to group conditions and control the order of evaluation.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE (department_id = 2 OR department_id = 3) 
   AND salary > 50000;

   • This query retrieves employees who belong to either department 2 or 3 and have a salary greater than 50,000.

5. Using NOT with IN:

   • You can use NOT with the IN operator to exclude specific values from the result set.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id NOT IN (1, 2, 3);

   • This query retrieves employees who do not belong to departments 1, 2, or 3.

6. Using NOT with LIKE:

   • You can use NOT with the LIKE operator to exclude rows that match a specific pattern.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE first_name NOT LIKE 'J%';

   • This query retrieves employees whose first names do not start with 'J'.

7. AND with Multiple Conditions:

   • When you need to apply more than two conditions, you can chain AND operators together to ensure all conditions must be true.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id = 2 
   AND salary > 50000 
   AND hire_date > '2020-01-01';

   • This query retrieves employees in department 2 who have a salary greater than 50,000 and were hired after January 1, 2020.

8. OR with Multiple Conditions:

   • Similarly, you can chain OR operators to check for multiple alternative conditions.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE department_id = 2 
   OR department_id = 3 
   OR department_id = 4;

   • This query retrieves employees who belong to departments 2, 3, or 4.

9. Order of Evaluation with AND, OR, and NOT:

   • When combining logical operators, NOT is evaluated first, followed by AND, and finally OR. Parentheses can be used to explicitly control the order of evaluation.
   • Example:

   SELECT first_name, last_name 
   FROM employees 
   WHERE NOT (department_id = 2 OR salary < 30000);

   • This query retrieves employees who are not in department 2 and do not have a salary less than 30,000.

Logical operators in MySQL are essential for creating complex queries that filter data based on multiple conditions. Using these operators effectively allows you to fine-tune your queries and retrieve precisely the data you need.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT *
FROM org_client
WHERE gender = 'M'
AND state = 'NY';

SELECT *
FROM org_client
WHERE city = 'Miami'
OR state = 'PA';

SELECT * 
FROM org_client
WHERE NOT state = 'NY';

SELECT * FROM org_client
WHERE credit_limit > ALL (SELECT credit_limit FROM org_client WHERE married_flag = 0)
AND married_flag = 1;

SELECT * FROM org_client
WHERE birth_year = ANY (SELECT birth_year FROM org_client WHERE gender = 'M' AND married_flag = 0)
AND gender = 'F'
AND married_flag = 0;

SELECT * FROM org_client
WHERE EXISTS (SELECT 1 FROM act_order WHERE org_client.client_id = act_order.client_id);

SELECT *
FROM org_client
WHERE birth_year IN (1990, 1980);

AND operator

SELECT * FROM org_client WHERE gender = 'M' AND state = 'NY';

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. When using the AND operator, both column values must satisfy the specified criteria.

OR operator

SELECT * FROM org_client WHERE city = 'Miami' OR state = 'PA';

In the given image, the green hue indicates the data that has been chosen or meets the criteria specified in our query. In the case of the OR operator, at least one of the column values needs to meet the specified criteria.

SQL ALL Clause

SELECT * FROM org_client WHERE credit_limit > ALL (SELECT credit_limit FROM org_client WHERE married_flag = 0) AND married_flag = 1;

In this context, we are focused on identifying married couples whose credit limit is higher than the highest credit limit among bachelors. The bachelors and their respective credit limits are displayed on the right side, arranged in descending order with the top credit limit being 15,000. Therefore, for married clients to have a higher credit rating than all bachelors, they must exceed this top credit limit. Rows with a green background on the left represent the data of married couples that fulfill our criteria.

SQL ANY Clause

SELECT * FROM org_client WHERE birth_year = ANY (SELECT birth_year FROM org_client WHERE gender = 'M' AND married_flag = 0) AND gender = 'F' AND married_flag = 0;

In this case, our goal is to find single female clients who share the same birth year as any of the single male clients. The data for single females, along with their birth years, is displayed on the left, while the information for single males is listed on the right. Single female clients whose data is highlighted with a green background are those whose birth year matches with that of a single male from the right side.

SQL EXISTS Clause

SELECT * FROM org_client WHERE EXISTS (SELECT 1 FROM act_order WHERE org_client.client_id = act_order.client_id);

In this scenario, we aim to list clients who have made at least one order. Referring to the image, the clients from the 'clients' table, highlighted with a green background, meet our criteria, as they have placed one or more orders in the 'orders' table on the right. Conversely, clients marked with a red background are those who haven't placed any orders in the table on the right.

IN OPERATOR

SELECT * FROM org_employee WHERE designation IN ('Sales Manager', 'Financial Analyst', 'CFO');

In the provided image, the green color signifies the data that has been selected or satisfies the conditions specified in our query. Data points in red indicate information that does not meet the criteria set by the query.