AP 04 - Avoid nesting scalar subqueries in the SELECT statement

Severity

Warning Warning

Type

Performance

Readability

Correctness

Problem

Scalar subqueries have an impact on readability and performance when used in a SELECT statement.

SELECT purchaseorderid
      ,orderdate
      ,deliverymethodid
      ,(SELECT deliverymethodname
      FROM application_deliverymethods dm
     WHERE dm.deliverymethodid=ppo.deliverymethodid)
  FROM purchasing_purchaseorders ppo;

Solution

Most of the time, you can change a scalar subquery in a SELECT statement into a join.

SELECT ppo.purchaseorderid
      ,ppo.orderdate
      ,ppo.deliverymethodid
      ,dm.deliverymethodname
  FROM purchasing_purchaseorders ppo
  LEFT OUTER
  JOIN application_deliverymethods dm
    ON ppo.deliverymethodid=dm.deliverymethodid

Legitimate use of the anti pattern

None