Is it possible to compare *tuples* in the `WHERE` clause of a SQL query?

Question

Is it possible to compare tuples (thanks, a_horse_with_no_name) in the WHERE clause of a SQL query? That way, I could convert this:

/* This is actually a sub-sub-sub-query in the middle *
 * of an incredibly complex stored procedure.         */
SELECT ISNULL(SUM(DT.DetailField), 0)
FROM   DetailTable DT
WHERE  DT.ForeignKey = ...
AND    EXISTS (/* I know this sub-sub-sub-sub-query *
                * will return at most one row.      */
               SELECT 'X'
               FROM   HeaderTable HT
               WHERE  HT.HeaderKey    = DT.HeaderKey
               AND    HT.HeaderField1 = ...
               AND    HT.HeaderField2 = ...)

Into something similar to this:

SELECT ISNULL(SUM(DetailField), 0)
FROM   DetailTable DT
WHERE  DT.ForeignKey = ...
AND    (SELECT HT.HeaderField1, HT.HeaderField2
        FROM   HeaderTable HT
        WHERE  HT.HeaderKey = DT.HeaderKey) = (..., ...)

Answer 1

What you are looking for is inner join:

SELECT ISNULL(SUM(DetailField), 0)
FROM   DetailTable DT
INNER JOIN HeaderTable HT ON HT.HeaderKey = DT.HeaderKey
WHERE  DT.ForeignKey = ...
AND    HT.HeaderField1 = ...
AND    HT.HeaderField2 = ...)

Answer 2

Writing

AND    (SELECT HT.HeaderField1, HT.HeaderField2
        FROM   HeaderTable HT
        WHERE  HT.HeaderKey = DT.HeaderKey) = (..., ...)

is certainly possible. At least with Oracle and PostgreSQL

If you are uncertain if the sub-select returns more than one row, you can even change the = to IN