Use different WHERE clause based on condition?

Question

I am trying to implement in a WHERE clause to filter on different condition based on certain scenario.

For example this is my table2:

MainID     TempID
-----------------
NULL        123

What I am trying to do is prioritize on filtering using MainID from table2. If MainID from table2 IS NOT NULL, then filter using

SELECT TOP 1 * 
FROM table1 
WHERE MainID = ...

But if MainID from table2 IS NULL, then filter using

SELECT TOP 1 * 
FROM table1 
WHERE TempID = ...

I thought of trying to use an OR, but I don't know if OR would always check based on the order left to right within the statement. I want to prioritize filtering based on the value from MainID from table2, but if it's not there then I need to filter on TempID from table2 based on a different column name within table1, for example:

SELECT TOP 1 * 
FROM table1 
WHERE MainID = value OR TempID = value

Answer 1

You can just use ISNULL to get either the first ID, or if it's null then the second.

SELECT *
FROM table2 t2
OUTER APPLY (
    SELECT TOP 1 * 
    FROM table1 t1
    WHERE t1.Id = ISNULL(t2.MainID, t2.TempID)
    ORDER BY t1.SomeColumn
) t1;

If you didn't need TOP 1 then you can use a normal join on ISNULL

SELECT *
FROM table2 t2
JOIN table1 t1 ON t1.Id = ISNULL(t2.MainID, t2.TempID);