5 joins? only result from one of them is needed, but order is important?

Question

I am doing left outer join to my table with around 5 different tables and I need result from only one of these 5 tables.

Now I know I could use Coalesce to get what I want. However, I was wondering if there is a way to tell SQL to not keep on joining as soon as one left join hits. However, the left outer joins need to occur in the same order as I write my query in.

Answer 1

Use UNION ALL to glue together 5 separate outer join queries, and take just the first row.

Answer 2

In general, unless you only need a single row and column, you cannot do this. Of perhaps I should say that you may be able to figure out an obtuse way to do this, but you should not.

SQL Server does not normally access single rows at a time as this is actually very inefficient, rather it will try to stack its I/O to a table up by doing many seeks to it at once, or even scanning the entire table or whole sections of it at a time. In this context, it is frequently just too slow to stop and consider whether which single record to get next.

So my recommendation is to just use COALESCE and let SQL Server sort it out.

Answer 3

You can convert LEFT JOIN queries to use EXISTS, which is more efficient.

And also that is probably your intention; To stop looking/joining when the condition is met.