Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Subqueries » How subqueries work

Quantified comparison test Next Page

Set membership test


A query containing a subquery that follows the keyword IN is converted into a join only if:

Example

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.

A UNION query following the IN operator cannot be converted
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.

Query with IN operator converted to a query with an ANY operator

In some cases, SQL Anywhere converts a query with the IN operator to one with an ANY operator, and decides accordingly 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' ) );