Explicit Join Syntax

I am enhancing an old application that is using a query similar to the below. I would like to convert the syntax to start using the explicit JOIN syntax. What would be the 'explici

Solution 1:

Try this:

SELECT
    DTL.DetailId, HDR.PersonId
FROM
    CMPN.Header HDR
INNER JOIN
    CMPN.Detail DTL ON HDR.HeaderId = DTL.HeaderId
INNER JOIN
    CMPN.DetailStatus DST ON DTL.DetailId = DST.DetailId
INNER JOIN
    CMPN.AdjustmentsDetails CAD ON DTL.DetailId = CAD.DetailId
WHERE
    DST.DetailStatusCode = 'Approved'AND DST.ExpirationTimestamp IS NULL
    AND HDR.Group = 'Group A';

Solution 2:

You are showing a query with 1980s-style joins and want to change them to explicit joins, which is a good idea. But joins are not the only means to represent relations in a query.

In your case you are selecting data from two tables, but a third table is involved. You only want to select data from the header and detail, when a certain entry exists in detailstatus. When we want to check whether an entry exists, we usually use an EXISTS or an IN clause. This also puts the third table where it belongs: in the WHERE clause, because it represents nothing more than a condition.

Here is how I would write the query:

SELECT dtl.detailid, hdr.personid
FROM cmpn.header hdr
JOIN cmpn.detail dtl ON dtl.headerid = hdr.headerid
WHERE hdr.group = 'Group A'AND dtl.detailid IN
(
  SELECT detailid
  FROM cmpn.detailstatus
  WHERE detailstatuscode = 'Approved'AND expirationtimestamp IS NULL
);

The reader of this query will see at a glance that it won't produce duplicates, which is not the case with the original query, where the reader must know whether a query detail can have multiple statuses or not. So, while my query looks a tad long-winded, it is still clearer and thus better maintainable than an only-joins query.

BTW: I've removed

and DTL.DetailId = CAD.DetailId

because there is no table CAD in your query.