SQL: How to compare two rows from same table where second row is given by first's values-CodePudding

Below is my SQL Server query which works great, but as you can see I am fetching the same row multiple times. Is there a way to optimise this by doing a SELECT only once? Thanks.

SELECT TOP 1 1
FROM some_table
WHERE col1 = @col1
  AND (col2 IN ('N', 'C', 'U') OR 
       col3 != (SELECT t.col3 FROM some_table t 
                WHERE t.id = id AND t.revision_id = revision_id - 1) OR
       col4 != (SELECT t.col4 FROM some_table t 
                WHERE t.id = id AND t.revision_id = revision_id - 1) OR
       col5 != (SELECT t.col5 FROM some_table t 
                WHERE t.id = id AND t.revision_id = revision_id - 1) OR
       col6 != (SELECT t.col6 FROM some_table t 
                WHERE t.id = id AND t.revision_id = revision_id - 1)
      )

Note: I only need to see only if one such column exists (see SELECT 1 1), which is more than likely to give be 1 most times. So I do NOT want to do a join of the table which has millions of rows.

CodePudding user response：

First, you have an error with the column names in the subqueries. See Qualifying column names in subqueries

column names in a statement are implicitly qualified by the table referenced in the FROM clause at the same level.

So, all of these columns belong to t: t.id=id AND t.revision_id=revision_id-1

And the query:

SELECT TOP 1 1
FROM some_table s
WHERE   col1=@col1
    AND 
        (
            col2 IN ('N','C','U') OR 
            EXISTS(SELECT * FROM some_table where id = s.id
               AND revision_id = s.revision_id - 1 AND
               (col3 != s.col3 OR col4 != s.col4 OR col5 != s.col5 OR col6 != s.col6)
            )
        )

CodePudding user response：

You can use apply here to select multiple columns form a related row.

This is just an approximation of what you need as you haven't supplied any example data and expected results. You may need to use a top (1) with an appropriate order by clause or aggregate if the apply can return more than one row:

select * 
from some_table t
outer apply (
  select col3, col4, col5, col6
  from some_table t2
  where t2.id = t.id AND t2.revision_id = t.revision_id-1
)t2
where col1 = @col1
and (
  col2 IN ('N','C','U') or
    t.col3 != t2.col3 or
    t.col4 != t2.col4 or
    t.col5 != t2.col5 or
    t.col6 != t2.col6
);