SQL Operators: Logical Operators

Logical operators in SQL are used to combine two or more conditions in a query. They are used to create complex queries that can retrieve specific data based on multiple conditions. Below are three common logical operators used in SQL:

1. AND: The AND operator returns TRUE if all conditions listed in the WHERE clause are TRUE.

   • Syntax:

     SELECT column_name
     FROM table_name
     WHERE condition1 AND condition2 AND condition3 ...;
     

   • Example:

     SELECT *
     FROM employees
     WHERE department = 'Marketing' AND salary > 50000;
     

   • Output:

     employee_id	first_name	last_name	department	salary
     4	John	Doe	Marketing	60000
     5	Jane	Smith	Marketing	75000

   • Explanation: The query above retrieves all employees whose department is 'Marketing' and salary is greater than 50000.

   • Use: The AND operator is used to retrieve data that meets multiple conditions.

   • Important Points:

     • All conditions separated by AND must be TRUE for the data to be returned.
     • The AND operator has a higher precedence than OR.

2. OR: The OR operator returns TRUE if at least one of the conditions listed in the WHERE clause is TRUE.

   • Syntax:

     SELECT column_name
     FROM table_name
     WHERE condition1 OR condition2 OR condition3 ...;
     

   • Example:

     SELECT *
     FROM employees
     WHERE department = 'Marketing' OR salary > 50000;
     

   • Output:

     employee_id	first_name	last_name	department	salary
     1	John	Smith	HR	40000
     2	Jane	Doe	IT	55000
     3	Jim	Brown	Finance	60000
     4	John	Doe	Marketing	60000
     5	Jane	Smith	Marketing	75000
     6	Jim	White	Sales	45000

   • Explanation: The query above retrieves all employees whose department is 'Marketing' or salary is greater than 50000.

   • Use: The OR operator is used to retrieve data that meets at least one condition.

   • Important Points:

     • At least one condition separated by OR must be TRUE for the data to be returned.
     • The OR operator has a lower precedence than AND.

3. NOT: The NOT operator returns data that does not satisfy the condition listed in the WHERE clause.

   • Syntax:

     SELECT column_name
     FROM table_name
     WHERE NOT condition;
     

   • Example:

     SELECT *
     FROM employees
     WHERE NOT department = 'Marketing';
     

   • Output:

     employee_id	first_name	last_name	department	salary
     1	John	Smith	HR	40000
     2	Jane	Doe	IT	55000
     3	Jim	Brown	Finance	60000
     6	Jim	White	Sales	45000

   • Explanation: The query above retrieves all employees whose department is not 'Marketing'.

   • Use: The NOT operator is used to retrieve data that does not meet the condition.

   • Important Points:

     • The NOT operator applies to only one condition at a time.
     • The NOT operator reverses the logical state of the condition.

Summary

Logical operators in SQL are used to combine two or more conditions in a query. Three common logical operators in SQL are AND, OR, and NOT. The AND operator returns TRUE if all conditions listed in the WHERE clause are TRUE. The OR operator returns TRUE if at least one of the conditions listed in the WHERE clause is TRUE. The NOT operator returns data that does not satisfy the condition listed in the WHERE clause.