The optimizer converts a subquery that follows an IN keyword only if:
The main query does not contain a GROUP BY clause, and is not an aggregate query, or the subquery returns exactly one value.
The subquery does not contain a GROUP BY clause.
The subquery does not contain the keyword DISTINCT.
The subquery is not a UNION query.
The subquery is not an aggregate query.
The conjunct 'expression IN ( subquery-expression )'
must not be negated.
So, the request "Find the names of the employees who are also department heads", expressed by the following query, would be converted to a joined query, as it satisfies the conditions.
SELECT GivenName, Surname FROM Employees WHERE EmployeeID IN ( SELECT DepartmentHeadID FROM Departments WHERE ( DepartmentName ='Finance' OR DepartmentName = 'Shipping' ) ); |
However, the request, "Find the names of the employees who are either department heads or customers" would not be converted to a join if it were expressed by the UNION query.
SELECT GivenName, Surname FROM Employees WHERE EmployeeID IN ( SELECT DepartmentHeadID FROM Departments WHERE ( DepartmentName='Finance' OR DepartmentName = 'Shipping' ) UNION SELECT CustomerID FROM SalesOrders); |
Similarly, the request "Find the names of employees who are not department heads" is formulated as the negated subquery shown below, and would not be converted
SELECT GivenName, Surname FROM Employees WHERE NOT EmployeeID IN ( SELECT DepartmentHeadID FROM Departments WHERE ( DepartmentName='Finance' OR DepartmentName = 'Shipping' ) ); |
The conditions necessary for an IN or ANY subquery to be converted to a join are identical. This is because the two expressions are logically equivalent.
In some cases, SQL Anywhere converts a query with the IN operator to one with an ANY operator, and decides whether to convert the subquery to a join. For example, the following two expressions are equivalent:
WHERE column-name IN( subquery-expression )
WHERE column-name = ANY( subquery-expression )
Likewise, the following two queries are equivalent:
SELECT GivenName, Surname FROM Employees WHERE EmployeeID IN ( SELECT DepartmentHeadID FROM Departments WHERE ( DepartmentName='Finance' OR DepartmentName = 'Shipping' ) ); |
SELECT GivenName, Surname FROM Employees WHERE EmployeeID = ANY ( SELECT DepartmentHeadID FROM Departments WHERE ( DepartmentName='Finance' OR DepartmentName = 'Shipping' ) ); |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |