When to use scaler subquery

Bikram

Hi All,

I know,we can fetch data from different tables using join condition and we can also sub-query to get the value from another table.

Can anybody tell me when we should go for scaler subquery.

Regards,

Bikram

BluShadow

There's no hard and fast rule and there are so many different situations when writing queries that it's not something that could be easily answered.

Me personally, I rarely use a scalar subquery, preferring my tables to be joined as I find that just reads clearer in the code.

Frank Kulash

Hi, @Bikram

I know,we can fetch data from different tables using join condition and we can also sub-query to get the value from another table.

Sometimes we can use a scalar sub-query to get a value from another table. A scalar sub-query can only return one column and (at most) only one row. If we need to get multiple columns from the same table, a join is probably better. If we need to get multiple rows, a join is definitely better. It usually has to be an outer join.

Aside from that, like Blushadow, I don't know of any specific rule about when a scalar sub-query is better. Like Blushadow, I use joins much more than scalar sub-queries.

Sven W.

As a general rule I would always prefer the join. but as with any rule there are exceptions.

Here are a few cases when I would consider if that is such an exception [Focus Topic in parenthesis]

• If there are many tables (> 7) in the join it can help with performance (hard parse) to move some of them into a scalar subquery [Performance]
• Lookup tables that are technically detail tables. For example a language based name lookup. This tends to confuse the optimizer [Performance]
• Sometimes it helps to avoid cluttering the code [Maintainability]
• For aggregation queries I often prefer to do lookups at the end to avoid having to pass the additional value though all the layers of aggregation [Maintainability].
• In rare cases uncorrelated subqueries can be (result) cached. This is often preferable to a join. [Performance]
• If the subquery can be embedded in a case construct that can avoid executing the subquery is a large number of cases (short circuit evaluation). This is sometimes not easy to do in a join, althogh the optimizer still might find a better execution plan [Performance, Error Handling]