Introduction - section 0

Edit starts here:
Introduction: When a new description on subject like subqueries is read, with various types and each with unique features, often readers try to make a mental picture of the process. Such a mental picture as a point of reference becomes very helpful when one attempts to apply the technique to solve a problem. This is why the old saying "One Picture is Worth a Thousand Words" holds true.

I have prepared the content in this article with visual presentation followed by descriptions and samples to make the path much easier for those interested in having a good understanding of the world of subqueries and retention of techniques to apply them later. Your comment below and/or indicating if this article was helpful is very much appreciated and could be used to improve my future work.

You may download Northwind.bak to restroe in your local SQL Server from to practice or test most of the examples included in this article.
0.pngIn T-SQL we can use the results returned from one query in another. The embedded or referenced subquery (inner query) returns required data to enable the outer query to perform more complex operations compared to a simple select clause. Below is the list of different subqueries discussed later in detail:

1) Self-contained subqueries (embedded).
2) Correlated subqueries (embedded).
3) Derived Table Expressions (embedded).
4) Common Table Expression, CTE (referenced).
5) Views and Inline Table-Valued Functions (referenced).

To reference a Table Expressions (or an encapsulated named query ) that is executed prior to its use at referenced points in an outer query and/ or in an intermediate table expressions. The major advantage of using a named table expressions is to facilitate recursive operation and to be referenced more than once in a query operation.

* Inline table-valued FUNCTIONS return a table data type, the other types of functions not related to the discussion here are scalar FUNCTIONS and Built-in functions. Scalar functions return a single data value and Built-in functions come with SQL Server to facilitate various operations.
Edit ends here:
also see:

Note, Questions on the remaining sections will be added later.
LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark BullockQA EngineerCommented:
restroe --> restore
0.pngIn T-SQL --> In T-SQL
Table Expressions --> Table Expression
named table expressions --> named table expression

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Thank you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.