CODEWITHSUNDEEP
sqlpostgresql
CXJ
CXJ

Reputation: 4449

SQL joined subquery problem / performance

Is there a way to write a query to obtain the same result set as the following "imaginary" query?

CREATE OR REPLACE VIEW v_report AS
    SELECT
        meta.refnum         AS  refnum,
        codes.svc_codes     AS  svc_codes
    FROM
        t_bill AS meta

    JOIN (SELECT
        string_agg(p.service_code, ':') AS svc_codes
      FROM
        t_bill_service_services AS p
      WHERE
        p.refnum = meta.refnum
    ) AS codes
    ON meta.refnum = codes.refnum

This query is imaginary because it won't run with an error message about the meta.refnum in the WHERE clause not being able to be referenced from this part of the query.

Note 1: many columns from a variety of other tables which are also JOINed are omitted in the interest of brevity. This may preclude some simpler solutions which eliminate the subquery.

Note 2: it is possible to make this work (for some definitions of "work") by adding the p.refnum column to the subquery and doing a GROUP BY p.refnum and removing the WHERE altogether, but this of course means the entire t_bill_service_services table gets scanned and sorted -- very very slow for my situation, as the table is reasonably large.

(The SQL flavor is Postgres, but should be irrelevant, as only the string_agg() call should be non-std SQL.)

Upvotes: 0

Views: 42

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

What you are describing is a lateral join -- and Postgres supports these. You can write the query as:

SELECT meta.refnum         AS  refnum,
       codes.svc_codes     AS  svc_codes
FROM t_bill meta CROSS JOIN LATERAL
     (SELECT string_agg(p.service_code, ':') AS svc_codes
      FROM t_bill_service_services p
      WHERE p.refnum = meta.refnum
     ) codes;

In this case, a lateral join is a lot like a correlated subquery (Nick's answer). However, a lateral join is much more powerful, because it allows the subquery to return multiple columns and multiple rows.

Upvotes: 1

Nick
Nick

Reputation: 147146

Rather than JOINing to a derived table, you can place the subquery in the SELECT part of the query. In this section, you can access the values from the parent table in subqueries and so only aggregate the relevant entries in the other table. For example:

select meta.refnum,
       (SELECT string_agg(p.service_code, ':')
        FROM t_bill_service_services AS p
        WHERE p.refnum = meta.refnum
       ) AS svc_codes
from t_bill meta

Demo on dbfiddle

Upvotes: 1

Related Questions