From this article:
================================================================
Enhanced Sub-Query Support

Sub-queries have always been powerful in the SQL language. They can be used as filters by placing them on the right side of a comparison in the WHERE clause. In Visual FoxPro 9, sub-queries can also be used as part of the SELECT list (called a projection) and in the FROM clause (often called a derived table).

"
--------------------------------------------------------------------------------
Many of these changes improve FoxPro's client/server capabilities by providing stronger interoperability with SQL Server.
--------------------------------------------------------------------------------
"


When used as a projection, if the sub-query doesn't return any records, a NULL value is returned. Correlation is supported with projection (more on this in a minute).

A SQL statement that uses projection looks like this:

SELECT ;
   C.CustomerID, ;
   C.CompanyName, ;
   (SELECT YTD_Sales FROM Sales_02 WHERE ;
     C.CustomerID = Sales_02.CustomerID) AS Y02,;
   (SELECT YTD_Sales FROM Sales_03 WHERE ;
     C.CustomerID = Sales_03.CustomerID) AS Y03,;
   (SELECT YTD_Sales FROM Sales_04 WHERE ;
     C.CustomerID = Sales_04.CustomerID) AS Y04 ;
  FROM Customers C 

This SELECT statement returns the customer ID and company name along with year-to-date sales from warehoused tables for the last three fiscal years.

A restriction on a projection is that the sub-query should return only one column and no more than one record for each record in the containing SELECT.

Another valuable use of a projection is when it's used as part of an expression.

SELECT ;
   C.customerID, ;
   C.companyname, ;
   SUM(D.quantity*D.unitprice) AS CustTotal ,;   
   (SUM(D.quantity*D.unitprice) / ;
     (SELECT SUM((quantity*unitprice)-discount) ;
        FROM OrderDetails D2) ;
    )*100 AS PctTotal ;
  FROM Customers C ;
 INNER JOIN Orders O ;
    ON C.customerID = O.customerID ;
 INNER JOIN OrderDetails D ;
    ON O.orderid = D.orderid ;
 GROUP BY C.customerID, C.companyname, O.orderID ;
 ORDER BY pctTotal DESC

This SELECT statement returns customer ID, company name, total sales, and a percent of total sales against all customer sales.

Note that the sub-query in the SELECT list is part of a complex expression that includes an aggregate function. Now that's flexibility!

A derived table as sub-query allows you to treat the results of a sub-query as though it were its own table.

Consider the following example:

SELECT ;
   C.customerid, ;
   P.product_count AS p_count;
  FROM Customers C, ;
     (SELECT c2.customerid, ; 
       COUNT(DISTINCT D.productID) AS p_count ;
        FROM Customers C2 ;
       INNER JOIN Orders O ;
          ON  C2.customerid = O.customerid ;
       INNER JOIN OrderDetails D ;
          ON O.orderid = D.orderid ;
       GROUP BY c2.customerid) AS P ;
 WHERE C.customerID = p.customerID ;
   AND P.p_count >= ;
      (SELECT (COUNT(*)*.50) FROM Products) ;
 ORDER BY p.product_count DESC

This SELECT statement returns customer ID and product count for all customers who have purchased at least 50% of the product line.

Notice that the derived table has an alias of "P" that is designated the same way you would alias a column, using the AS clause (required). It's also important to note that the sub-query can be complex (in this case, joining to two other tables) and that the results from the derived table can be used as a condition of the WHERE clause and in the ORDER BY of the top-most SELECT.

Unlike a projection, the derived sub-query can return more than one column and more than one record. It cannot be correlated. All sub-selects are executed before the top-most SELECT is evaluated.

Sub-queries are also supported in the SET list of a SQL UPDATE statement. Only one sub-query is allowed in a SET clause and if there is a sub-query in the SET clause, a sub-query in the WHERE clause is not allowed.