SQL - Logical Operators

SQL Logical Operators are used to test conditions in queries, returning results as TRUE, FALSE, or UNKNOWN. They help in combining, negating, and comparing conditions, enabling precise data retrieval and filtering.

• Combine multiple conditions in a query.
• Control query execution flow.
• Support complex filtering and comparison.
• Return boolean results for decision-making.
• Useful for highly specific data retrieval.

Employee Table for Examples

We will use the following employee table throughout the examples. This table represents employee details, including their unique ID, name, city, and country.

Below is the comprehensive list of SQL Logical Operators along with their meanings, detailed explanations, and practical examples:

1. AND Operator

The AND operator is used to combine two or more conditions in an SQL query. It returns records only when all conditions specified in the query are true. This operator is commonly used when filtering data that must satisfy multiple criteria simultaneously.

Example

Retrieve the records of employees from the employees table who are located in 'Allahabad' and belong to 'India', ensuring that both conditions are met.

Query:

SELECT * FROM employee WHERE emp_city = 'Allahabad' AND emp_country = 'India';

Output

Explanation:

In the output, both conditions (emp_city = 'Allahabad' and emp_country = 'India') are satisfied for the listed employees, so these records are returned by the query.

2. IN Operator

The IN operator streamlines checking whether a value matches any value from a specified list, making queries more concise and readable than using multiple OR conditions. It is especially useful for filtering results by several possible values in a column, helping reduce query complexity

Example

Retrieve the records of employees from the employee table who are located in either 'Allahabad' or 'Patna'.

Query:

SELECT * FROM employee WHERE emp_city IN ('Allahabad', 'Patna');

Output

Explanation:

In this query, the IN operator checks if the value of the emp_city column matches any value in the list ('Allahabad', 'Patna'). The query returns all employees who are located in either of these two cities.

3. NOT Operator

The NOT operator is used to reverse the result of a condition, returning TRUE when the condition is FALSE. It is typically used to exclude records that match a specific condition, making it useful for filtering out unwanted data.

Example

Retrieve the records of employees from the employee table whose city names do not start with the letter 'A'.

Query:

SELECT * FROM employee WHERE emp_city NOT LIKE 'A%';

Output

Explanation:

In this query, the NOT operator negates the LIKE condition. The LIKE operator is used to match patterns in string data, and the 'A%' pattern matches any city name that starts with the letter 'A'. By using the NOT operator, we exclude cities starting with 'A' from the result set.

4. OR Operator

The OR operator combines multiple conditions in a SQL query and returns TRUE if at least one of the conditions is satisfied. It is ideal for situations where you want to retrieve records that meet any of several possible conditions.

Example

Retrieve the records of employees from the employee table who are either from 'Varanasi' or have 'India' as their country.

Query

SELECT * FROM employee WHERE emp_city = 'Varanasi' OR emp_country = 'India';

Output

Explanation:

In this case, the output includes employees from 'Varanasi' as well as those who have 'India' as their country, even if they are from different cities. The query returns all records where at least one of the conditions is true.

5. LIKE Operator

The LIKE operator in SQL is used in the WHERE clause to search for a specified pattern in a column. It is particularly useful when we want to perform pattern matching on string data. The LIKE operator works with two main wildcards:

• %: Represents zero or more characters. It allows matching any sequence of characters in the string.
• _: Represents exactly one character. It is used when you want to match a specific number of characters at a given position.

Example

Retrieve the records of employees from the employee table whose city names start with the letter 'P'.

Query:

SELECT * FROM employee WHERE emp_city LIKE 'P%';

Output

Explanation:

In this case, the output includes only those employees whose emp_city starts with 'P'. The % wildcard ensures that the query matches any city name starting with the specified letter, regardless of how many additional characters follow it.

6. BETWEEN Operator

The BETWEEN operator in SQL allows us to test if a value or expression lies within a specified range.

• Inclusive Range – Includes both the lower and upper limits in the result set.
• Versatile Use – Works with numbers, dates, and text values.
• Efficient Filtering – Ideal for selecting data within a defined range.

Example

Retrieve the records of employees from the employee table whose emp_id values fall within the range of 101 to 104 (inclusive).

Query:

SELECT * FROM employee WHERE emp_id BETWEEN 101 AND 104;

Output

Explanation:

In this query, the BETWEEN operator is used to filter employees with emp_id values ranging from 101 to 104. Since the BETWEEN operator is inclusive, employees with emp_id values of 101, 102, 103, and 104 will be included in the result set.

7. ALL Operator

The ALL operator in SQL is used to compare a value to all values returned by a subquery.

• Returns TRUE only if the condition is TRUE for all values returned by the subquery.
• Commonly used with SELECT, WHERE, and HAVING clauses.
• Ensures a value meets the specified condition when compared to an entire result set.

Example

Retrieve the records of employees whose emp_id is equal to all emp_id values in the employees table where the emp_city is 'Varanasi'.

Query:

SELECT * FROM employee WHERE emp_id = ALL 
              (SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');

Output

Explanation:

The query checks whether emp_id in the outer query is equal to every emp_id from the subquery (which retrieves emp_id values from employees in 'Varanasi'). In this case, the output will include employees whose emp_id matches all the values in the subquery.

8. ANY Operator

The ANY operator in SQL is used to compare a value with the results of a subquery.

• Returns TRUE if the value meets the condition with any value from the subquery results.
• Useful for checking if a value matches at least one item in a set.
• Provides flexibility when comparing against multiple possible values.

Example

Retrieve the records of employees whose emp_id matches any of the emp_id values in the employees table where the emp_city is 'Varanasi'.

Query:

SELECT * FROM employee WHERE emp_id = ANY
                (SELECT emp_id FROM employee WHERE emp_city = 'Varanasi');

Output

Explanation:

The output shows employees whose emp_id matches at least one emp_id from employees in 'Varanasi' (e.g., 101, 102, 106).

9. EXISTS Operator

The EXISTS operator in SQL is used to check whether a subquery returns any rows.

• Returns TRUE if the subquery returns one or more rows.
• Commonly used with SELECT, UPDATE, INSERT, and DELETE statements.
• Helps check if any matching rows exist for a given condition.
• Often used in correlated subqueries referencing the outer query.

Example

Retrieve the names of employees from the employee table if there are any employees in the employee table who are located in 'Patna'.

Query

SELECT emp_name FROM employee WHERE EXISTS
                (SELECT emp_id FROM employee WHERE emp_city = 'Patna');

Output

Explanation:

The EXISTS operator returns TRUE if the subquery finds any employees from Patna, so all employee names are included. The query lists all employees as long as at least one is from Patna.

10. SOME Operator

• Used with comparison operators (<, >, =, <=, etc.) to compare a value against subquery results.
• Returns TRUE if the condition is satisfied for at least one value from the subquery.
• Useful for matching a value against multiple possible results instead of just one.

Example

Retrieve the records of employees from the employee table where the emp_id is less than any of the emp_id values from employees located in 'Patna'.

Query:

SELECT * FROM employee WHERE emp_id < SOME 
                (SELECT emp_id FROM employee WHERE emp_city = 'Patna');

Output

Explanation:

The query returns employees whose emp_id is less than at least one emp_id of employees from Patna.