How to code correlated subqueries

      SELECT categoryID, categoryName, (
        SELECT COUNT(*) 
        FROM products 
        WHERE products.categoryID = categories.categoryID
        ) AS productCount 
      FROM categories;
      

      WHERE [NOT] EXISTS (subquery)
      

      SELECT c.customerID, firstName, lastName 
      FROM customers c 
      WHERE NOT EXISTS (
        SELECT * FROM orders o WHERE c.customerID = o.customerID
      );
      

Description

• A correlated subquery is a subquery that is executed once for each row processed by the outer query.
• A correlated subquery refers to a value that’s provided by a column in the outer query. Because that value varies depending on the row that’s being processed, each execution of the subquery returns a different result.
• To refer to a value in the outer query, a correlated subquery uses a qualified column name that includes the table name from the outer query. If the subquery uses the same table as the outer query, you must assign a table alias to one of the tables to remove ambiguity.
• You can use the EXISTS operator to test that one or more rows are returned by the subquery. You can also use the NOT operator with the EXISTS operator to test that no rows are returned by the subquery.