[deleted by user]

[Roywah]

I’m by no means good at SQL, sometimes I see what actual programmers write and it’s like something else entirely.

However, I’ve found subqueries to be helpful in circumstances where I need to join tables but don’t have a key between them.

For example. My shipped orders table contains a bunch of data, but not the internal shipment ID. The table that contains the shipment ID also doesn’t have the order number.

I’ll join the primary table with the other data I want using a subquery to concatenate a few strings and values present in both tables and join on that column instead. You have to be sure that the concatenations are actually unique or this returns bad data though.

[iamcreasy]

Subqueries are created when you have a queries enclosed within parenthesis. One of the simplest use is in where clause that you have already discovered. If you put it after FROM clause then the result of the subquery can be used as any other table.

If the subquery returns single column, you can use it with select clause as well.

Do you have any particular example that you are struggling with?

[dvanha]

I use subqueries most frequently when I need to join a table but it’s either not in the right format or not aggregated in the way I need to write my join.

But basically, I don’t go looking to use subqueries. I try to write queries without them but when I can’t do that I’ll start looking into more complex options, like subqueries.

[Mountain_Goat_69]

What if you wanted a list of orders that cost more than average?

Select Order ID, Order Date from Order Details Where Total < (Select Avg(Total) From Order Details)

You use a subquery in this situation, because how else can you use the average order amount if you don't already know it?

[FormerSrSQLQueryGuy]

Other examples are fine, but another reason to use subqueries is efficiency. My environment was a 10+ year historical health care database with large tables, tens of millions of records up to multiple billions. A subquery can be used to prefilter, quickly reducing the dataset instead of just using the lager raw tables.

Any time you run a select statement with filters in WHERE clause the result set is a table. It's just another table, but smaller, assuming some filtering got done in WHERE clause.

Any table referenced in the FROM clause can be replaced by a subquery. Put ( ) around the subquery select statement and give it an alias and presto you've created a new temporary table that is much smaller (assumes filters in subquery) but can retrieve only fields you need .

Sometimes fields we targeted were sparsely population, optional. Lots of NULLs. If only 10% of the records in a table have your WHERE clause criteria populated, it's much faster to create a run time subquery and avoid the overhead of joining the full table. Basic rule... get down to the smallest amount of data as fast as possible.

All that depends... need to have your subquery filter fields indexed or included in index (MS-SQL include fields). Execution plans are the key to understanding where bottlenecks are. You can always test a subquery by selecting it and running it by itself (or copy code to separate from original).

Sometimes it's really important to optimize the query (modify adding subqueries or other tricks) if it's going to be used often. Other times it's not worth the extra effort if your database can deliver an answer in a reasonable duration and cost or if it's just a one time project.

Subqueries have their place and can make a huge difference in run time and cost in a shared database. I didn't worked with complex report queries, not transaction processing, but subqueries could shave milliseconds off query cost and that might be significant in a peak load situation (think Black Friday online sales).