Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/sql/sql-logical-operators/ below:

SQL - Logical Operators - GeeksforGeeks

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.

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:

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.

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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.

• 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4