4.4 Logical Operators: AND, OR, NOT, LIKE, IN

Logical operators in SQL allow you to perform conditional filtering on your data. These operators are used in WHERE clauses to combine or exclude certain conditions, enabling more flexible and precise queries. Mastering logical operators is essential for querying databases effectively, as they help refine search criteria and make data retrieval more efficient.

4.4.1 AND - Combine Conditions

The AND operator is used to combine two or more conditions in a SQL query. All conditions connected with AND must be true for the entire condition to evaluate as true. This operator is helpful when you want to filter data that meets multiple criteria.

• Purpose: To return rows that satisfy all specified conditions.
• Syntax:

  SELECT column1, column2
  FROM table_name
  WHERE condition1 AND condition2;
  

• Example:
  Find employees who work in the "HR" department and have a salary greater than 50000:

  SELECT employee_name, department, salary
  FROM employees
  WHERE department = 'HR' AND salary > 50000;
  

  This query will only return rows where both conditions are true: the department is "HR" and the salary is greater than 50,000.

4.4.2 OR - Combine Multiple Conditions

The OR operator is used to combine multiple conditions in a SQL query, where only one of the conditions needs to be true for the row to be returned. This operator is useful when you want to search for records that match any of the multiple criteria.

• Purpose: To return rows that satisfy at least one of the conditions.
• Syntax:

  SELECT column1, column2
  FROM table_name
  WHERE condition1 OR condition2;
  

• Example:
  Find employees who work either in the "HR" or "IT" department:

  SELECT employee_name, department
  FROM employees
  WHERE department = 'HR' OR department = 'IT';
  

  This query will return rows where either the department is "HR" or "IT".

4.4.3 NOT - Exclude Conditions

The NOT operator is used to negate a condition. It returns rows where the specified condition is false. It is commonly used to exclude certain values from the result set.

• Purpose: To exclude rows that meet the specified condition.
• Syntax:

  SELECT column1, column2
  FROM table_name
  WHERE NOT condition;
  

• Example:
  Find employees who do not work in the "HR" department:

  SELECT employee_name, department
  FROM employees
  WHERE NOT department = 'HR';
  

  This query will return employees who do not belong to the "HR" department.

4.4.4 LIKE - Pattern Matching

The LIKE operator is used for pattern matching in string values. It allows you to search for values that match a specific pattern using wildcard characters. It's especially useful for searching text-based fields where the exact value might not be known.

• Purpose: To search for a pattern in a column.
• Syntax:

  SELECT column1, column2
  FROM table_name
  WHERE column_name LIKE pattern;
  

  • %: Represents any sequence of characters (including no characters).
  • _: Represents a single character.
• Example:
  Find employees whose names start with "J":

  SELECT employee_name
  FROM employees
  WHERE employee_name LIKE 'J%';
  

  This query will return all employee names that begin with the letter "J" (e.g., "John", "James", etc.).
• Example:
  Find products whose names contain the word "laptop":

  SELECT product_name
  FROM products
  WHERE product_name LIKE '%laptop%';
  

  This will return products that have "laptop" anywhere in their name.

4.4.5 IN - Match Against Multiple Values

The IN operator allows you to check if a value matches any value in a list of specified values. It's more efficient than using multiple OR conditions, especially when you have many possible values to check against.

• Purpose: To check if a column's value matches any value in a set of values.
• Syntax:

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

• Example:
  Find employees who work in the "HR", "IT", or "Finance" departments:

  SELECT employee_name, department
  FROM employees
  WHERE department IN ('HR', 'IT', 'Finance');
  

  This query will return employees who work in any of the specified departments.
• Example:
  Find products with specific IDs:

  SELECT product_name, product_id
  FROM products
  WHERE product_id IN (101, 102, 105);
  

  This will return products with IDs 101, 102, or 105.

4.4.6 Combining Logical Operators

You can combine AND, OR, NOT, LIKE, and IN operators in a single query to create more complex conditions. Parentheses are often used to group conditions and control the order of evaluation.

• Example:
  Find employees who work either in the "HR" department or have a salary greater than 50000, but exclude those with the name "John":

  SELECT employee_name, department, salary
  FROM employees
  WHERE (department = 'HR' OR salary > 50000)
    AND NOT employee_name = 'John';
  

4.4.7 Practical Examples of Logical Operators

1. Find orders placed in 2024 that are not yet shipped:

   SELECT order_id, order_date, status
   FROM orders
   WHERE YEAR(order_date) = 2024 AND status NOT IN ('Shipped', 'Delivered');
   

2. Search for products that are either in stock or have low stock:

   SELECT product_name, stock_quantity
   FROM products
   WHERE stock_quantity IN (0, 1, 2, 3) OR stock_quantity > 100;
   

3. Search for customers with specific last names:

   SELECT customer_id, first_name, last_name
   FROM customers
   WHERE last_name LIKE 'S%' OR last_name LIKE 'A%';
   

4.4.8 Conclusion

Logical operators such as AND, OR, NOT, LIKE, and IN provide powerful ways to filter and combine conditions in SQL queries. These operators allow you to perform more complex searches and refine your results to meet specific criteria. Mastering these logical operators is key to querying databases efficiently and getting the exact data you need.