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) = (..., ...)

Collaborative

Did you try to execute similar code with your real table names and column names? did it work? what message did you get? – Xerography

Btw: you mean "tuples" not "tables" in your subject – Ewing

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 = ...)

Fleecy

I know what INNER JOIN is. But I like it better when there is only one table per query, because that way I can more easily follow the logic that led me to writing that query. Besides, I'm not extracting data from HeaderTable. – Collaborative

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

Ewing

I am completely certain that the sub-select returns one row. It just returns more than one column... and I'm using SQL Server. :S – Collaborative

I don't use SQL Server, so you will need to try if that is supported – Ewing

Answer 3

It seems like you are trying to compare records not tables here. And in fact you are comparing results of queries.

It is totally possible with Oracle and MySQL. The following query is valid and do the job:

SELECT (SELECT foo, bar FROM wathever) = (SELECT fuu, baz FROM another);

It will compare fields one against one and return 1 if they all match (or 0 if they dont). If subqueries return more than one row, it will raise a SQL error. This expression can also be used elsewhere as in WHERE clauses.

Update for postgreSQL

As @tsionyx pointed out, in PostgreSQL a subquery cannot return multiple columns. It's feasible returning a row value type:

SELECT (SELECT ROW(foo, bar) FROM wathever) = (SELECT ROW(fuu, baz) FROM another);

Comptroller

Not valid for Postgres: SELECT (SELECT 1, 2) = (SELECT 1,2); ERROR: subquery must return only one column; SELECT (SELECT ROW(1, 2)) = (SELECT ROW(1,2)); is the valid one – Croy

Answer 4

Goran's answer looks best to me and I voted for it. Just to add another option, since you're using SQL Server, a flexible way to grab multiple columns from subqueries is outer apply. You can compare two values (a tuple) like:

select  *
from    SomeTable t1
outer apply
        (
        select  *
        from    SomeOtherTable t2
        where   t1.Stuff = t2.Unit
        ) sub1
outer apply
        (
        select  *
        from    YetAnotherTable t3
        where   t1.Stuff = t3.jetser
        ) sub2
where   sub1.col1 = sub2.col1
        and sub1.col2 = sub2.col2

Fastidious