Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

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 https://northwinddatabase.codeplex.com/ 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: https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

Note, Questions on the remaining sections will be added later.
ASKER CERTIFIED SOLUTION
Avatar of Mark Bullock
Mark Bullock
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Eghtebas

ASKER

Thank you very much.