SQL retrieval from tables

I have a table something like

EMPLOYEE_ID DTL_ID COLUMN_A COLU开发者_C百科MN_B
---------------------------
JOHN        0       1           1
JOHN        1       3           1
LINN        0       1           12
SMITH       0       9           1
SMITH       1       11          12

It means for each person there will be one or more records with different DTL_ID's value (0, 1, 2 .. etc).

Now I'd like to create a T-SQL statement to retrieve the records with EMPLOYEE_ID and DTL_ID.

If the specified DTL_ID is NOT found, the record with DTL_ID=0 will be returned.

I know that I can achieve this in various ways such as checking if a row exists via EXISTS or COUNT(*) first and then retrieve the row.

However, I'd like to know other possible ways because this retrieval statement is very common in my application and my table have hundred thousand of rows.

In the above approach, I've had to retrieve twice even if the record with the DTL_ID specified exists, and I want to avoid this.

Like this:

  SELECT *
    FROM table
   WHERE EMPLOYEE_ID = ?? AND DTL_ID = ??
UNION
  SELECT *
    FROM table
   WHERE EMPLOYEE_ID = ?? AND DTL_ID = 0
     AND NOT EXISTS (SELECT *
                       FROM table
                      WHERE EMPLOYEE_ID = ?? AND DTL_ID = ??)

You will of course have to fill in the ?? with the proper number.

If DTL_ID is always 0 or positive:

SELECT TOP 1 * FROM table
where EmployeeID = @EmployeeID and DTL_ID in (@DTL_ID,0)
order by DTL_ID desc

If you're working across multiple employees in a single query, etc, then you might want to use ROW_NUMBER() if your version of SQL supports it.

Use ISNULL(DTL_ID, 0) in your final SELECT query

SELECT E1.EMPLOYEE_ID, ISNULL(E2.DTL_ID, 0), E1.COLUMN_A, E1.COLUMN_B EMPLIYEES AS E1
LEFT JOIN EMPLIYEES AS E2
ON E1.EMPLOYEE_ID = E2.EMPLOYEE_ID AND E2.DTL_ID = 42

You can use top and union, e.g.:

declare @t table(id int, value int, c char)
insert @t values (1,0,'a'), (1,1,'b'), (1,2,'c')

declare @id int = 1;
declare @value int = 2;

select top(1) *
from
(
    select *
    from @t t
    where t.value =  @value and t.id = @id

    union all

    select *
    from @t t
    where t.value = 0

)a
order by a.value desc

If @value = 2 than query returns 1 2 c. If @value = 3 than query returns 1 0 a.

SELECT MAX(DTL_ID) ...
WHERE DTL_ID IN (@DTL_ID, 0)