Explicit join syntax

Question

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 'explicit join syntax' query for the below:

Select
       DTL.DetailId
     , HDR.PersonId
from
       CMPN.Header             HDR
     , CMPN.Detail             DTL
     , CMPN.DetailStatus       DST
     , CMPN.AdjustmentsDetails CAD
where
       HDR.HeaderId                      = DTL.HeaderId
       and DTL.DetailId                  = DST.DetailId
       and DTL.DetailId                  = CAD.DetailId
       and DST.DetailStatusCode          = 'Approved'
       and DST.ExpirationTimestamp IS NULL
       and HDR.Group                     = 'Group A'
;

What are the table's unique keys? It seems the tables `Detail` and `DetailStatus` have either a 1:1 relationship (which is a rather rare thing) or there is one row per detail and status code in the status table. Or what else is the table's key? Can the query produce duplicate result rows? — Thorsten Kettner, Apr 17 '20 at 20:48
No further condition on the `CAD` table? I suppose that there can be multiple rows in that table for one `DetailId`? Then the query produces duplicates. Is this intended? Again: Please tell us what the tables' keys are. — Thorsten Kettner, Apr 17 '20 at 20:56
marc_s's query is the direct translation of your original query and produces the same result (including possible duplicates), which mine is not. So, marc_s's answer is the direct answer to your question. You may want to accept it (i.e. click the check mark next to his answer) :-) — Thorsten Kettner, Apr 17 '20 at 21:00
Does this answer your question? [Inner join vs Where](https://stackoverflow.com/questions/121631/inner-join-vs-where) — philipxy, Apr 18 '20 at 02:11

marc_s · Accepted Answer · 2020-04-17T20:58:22.847

4

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';

edited Apr 17 '20 at 20:58

answered Apr 17 '20 at 20:18

marc_s

732,580
175
1,330
1,459

CAD is another table that has the following relationship with Detail table AdjustmentDetail CAD DTL.DetailId = CAD.DetailId – Andulos Apr 17 '20 at 20:40
I have a follow-up question. Here is another thread with the details https://stackoverflow.com/questions/61277235/how-to-combine-three-queries-into-one-is-it-possible-to-combine-them-and-write – Andulos Apr 17 '20 at 21:29

score 1 · Answer 2 · answered Apr 17 '20 at 20:40

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.

Explicit join syntax

2 Answers2

Linked