IN, EXISTS, and ANY Subqueries
In SQL database management systems, subqueries are used to manually filter database table data based on the results of nested table queries, applying multiple different operators such as IN, EXISTS, and ANY. Here, the IN, EXISTS, and ANY operators are mostly used in conjunction with database table subqueries to analyze or apply conditions or table expressions like IN, EXISTS, and ANY to the subquery’s result output.
Let’s explore the IN, EXISTS, and ANY operators in SQL database tables in more detail.
IN Subquery in SQL Database Management.
The IN operator in SQL database tables is used to test or analyze whether a data or value in the database table exists in the list of values returned by the subquery. The IN operator is mostly applied in SQL tables when the database user needs to properly match a table column value with several table values or rows returned by the subquery.
Syntax of IN Subquery in SQL.
SELECT column_name
FROM table_name
WHERE column_name IN (subquery);
In SQL database tables, the subquery displays and returns a list of table values, mostly in the format or order of a table’s column and multiple rows.
Here, the outer query tests whether the column_name in the current database table exists in the list of table values returned by the subquery.
Example of IN Subquery in SQL.
Let’s assume the database user has two tables named Employee and Department.
This is the employee database table, which has table column fields such as employee_id, emp_name, and department_id.
This is the department database table, which has table column fields such as department_id and department_name. Here, the database user wants to retrieve the employee names from the existing table where the employee department_id corresponds to ‘Development’ and ‘Design’ departments. The user can achieve this by applying the IN operator in a subquery as follows:
SELECT emp_name
FROM employee
WHERE department_id IN (
SELECT department_id
FROM department
WHERE department_name IN (‘Development’, ‘Design’)
);
Explanation of IN Subquery in SQL.
The inner subquery returns the department_id values for the ‘Development’ and ‘Design’ employee departments.
The outer query then retrieves the employee names whose department_id matches the values returned by the inner subquery.
Advantages of IN Subquery in SQL.
The IN operator simplifies your query results when the database user needs to check if a value exists in the output of a table column.
Instead of manually specifying each database value in the table, the database user can use the IN operator to compare against the table column values returned by the subquery.
EXISTS Subquery in SQL Database Management.
The EXISTS operator in SQL database tables is used to test or analyze whether a subquery based on the existing database table returns any value or row. The specific data returned by the subquery does not matter; the result simply indicates whether the subquery returns at least one result in the system-generated database table. It verifies the existence of rows in the existing table. The EXISTS operator in SQL database tables is mostly applied when database users want to analyze whether a user-generated database table subquery has any directly related rows, and it can ultimately be added with a correlated subquery.
Syntax of EXISTS Subquery in SQL.
SELECT column_name
FROM table_name
WHERE EXISTS (subquery);
Here, the subquery generated in the database table is run for every table row processed by the outer query.
If the table-based subquery returns at least one row value, the outer table query will return the row; otherwise, it will not return any row value as output.
Example of an EXISTS subquery in SQL.
Here, the database user wants to find the employee records in the Employee and Department tables who work in departments that have at least one employee.
SELECT emp_name
FROM employee e
WHERE EXISTS (
SELECT 1
FROM employee
WHERE department_id = e.department_id
);
Explanation of the EXISTS subquery in SQL.
Here, the inner subquery checks or analyzes whether any employee exists in that department e.department_id.
Similarly, the outer database table query finds and returns the names of those employees for whom the EXISTS subquery finds at least one matching employee in the same department.
Advantages of the EXISTS subquery in SQL.
It is an efficient process for testing or verifying the existence of table data or information without returning the actual table data in user-defined database tables.
It performs better for correlated subqueries in a database table, where a subquery refers to the outer query.
ANY Subquery in SQL Database Management.
The ANY operator in SQL database tables is used to compare a value with any of the values returned by a subquery in the database table. Here, the table data returned by the subquery in the database table is compared with the table value using comparison operators such as =, >, <, >=, <=, or <>. The comparison is performed on any value in the result output of the subquery.
Syntax of ANY Subquery in SQL.
SELECT column_name
FROM table_name
WHERE column_name operator ANY (subquery);
Here, the inner database table subquery returns multiple values, a column, and several row values.
Similarly, the outer query compares the column value with any of the table values returned by the subquery by applying an indicated operator.
Example of ANY Subquery in SQL.
Let’s find the employees in the employee table whose salary is greater than the salary of any employee in department 322.
SELECT emp_name
FROM employee
WHERE salary > ANY (
SELECT salary
FROM employee
WHERE department_id = 322
);
Explanation of the ANY Subquery in SQL.
Here, the inner query returns the salary values of employees in department 322.
Similarly, the outer query extracts the names of employees whose salary is greater than any salary in department 322.
Key Features of the ANY Subquery in SQL.
Comparison operators =, >, <, >=, etc., help the user determine how the comparison will be made between the column value of the outer query and the table data values returned by the subquery. Here, the ANY outer table query allows the result to be returned as long as the condition is met for any value in the result output of the database table subquery.
Advantages of the ANY Subquery in SQL.
The ANY operator provides flexibility in comparing table values in a database table, especially when a table value needs to be compared with multiple results.
The ANY operator can be used in several conditions where the outer table query needs to match a range of table values returned through the subquery.
Let’s Analyze or compare the in, exists, and any operator in sql
| Operator type | Operator description | Subquery Behaviors | Where to usage |
| In operator | In operator used to checks or find if a data value is equal to any value in the exist table result output. | It’s a non-correlated usually behaviour in table. | We can use it when we want to match a table column value to a set of possible existing table data values. |
| Exists operator | Exist operator used to checks if the active used table subquery returns any table rows data value or not. | It’s a correlated usually behaviour in table. | We can use it when we want to check the existence of ay rows matching to a given user define table column condition. |
| Any operator | Any operator used to compare a table value with any given value returned by the table subquery use a multiple individual comparison operator. | It’s a correlated or non-correlated behaviour in database table. | We can use it when we need to compare a table value against multiple table values returned by the subquery data output. |
Conclusion of IN, EXISTS, and ANY Subqueries.
IN operator – The IN operator in SQL database tables is used when the database user wants to match a table column value with a list of table values returned through a subquery.
EXISTS operator – The EXISTS operator is used in SQL database tables when the database user needs to determine whether a found table data value record exists or not, rather than checking if the actual value exists.
ANY operator – The ANY operator is useful when the database user needs to compare a table column value with any table value in the result output returned through the database table subquery by applying a comparison operator.
