Subquery - section 1

Note: T-SQL code is included for reference only not to be edited.
Edit starts here:
A self-contained subquery embedded in an outer query is a stand-alone query with no dependencies to its outer query. You can highlight such subquery to test run it independently for QC purpose.

A scalar subquery, by definition, returns a single value to be used in its outer query either as predicate in the outer query's WHERE clause or included directly in its outer query's SELECT clause as an alised column.

-- Problem statement: Show product names for ContactName ='Peter Wilson'.

     , ProductName 
FROM dbo.Products
WHERE SupplierID = (SELECT SupplierID                -- scalar subquery in WHERE clause, example 1.1
                    FROM dbo.Suppliers
	            Where ContactName ='Peter Wilson');

-- Problem statement: Display all products with first contact name from Suppliers table. Not very
-- practicle, but it shows how to include a self-containe independent subquery in a SELECT clause.

     , ProductName
     , (SELECT Top(1) ContactName                    -- scalar subquery in SELECT clause, example 1.2
        FROM dbo.Suppliers) AS FirstContactName 
FROM dbo.Products;

Open in new window

If a self-contained scalar subquery returns an empty set, its outer query sees it a null mark. And, if such a subquery by mistake coded returning multiple values will produce the following error upon runing the entire query (Outer + Subquery):

Msg 512, Level 16, State 1, Line 23
Subquery returned more than 1 value...

Note: The new and acceptable terminolg is null or null mark. The term null value, often used, is not correct because by definition null is neither value nor can have a value.

The multiple value self-contained query is used where a multi-valued result is expected, for example when using the IN predicate. As an example, the following query uses a multi-valued subquery to return products supplied by suppliers from UK. The inner query returns supplierid for suppliers from UK. You can use ... WHERE supplierid NOT IN (SELECT supplierid... 0 to return all others but UK if required.
-- Problem statement: Show product names not associated with ContactName ='Peter Wilson'.

     , ProductName
FROM dbo.Products 
WHERE SupplierID IN (SELECT SupplierID               -- Multi-value subquery in WHERE clause, example 1.3
                     FROM dbo.Suppliers
                     WHERE CompanyName <> 'Peter Wilson') 

-- There is no practical application for multi-value subquery in SELECT clause.

Open in new window

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:
Add "as" to this? query sees it a null mark
Change alised to aliased.
Change runing to running.
Change terminolg to terminology.
Change neither value to neither a value.

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:

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.