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

asked on

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'.

SELECT ProductID
     , 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.

SELECT ProductID
     , 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'.

SELECT ProductID
     , 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: 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

Done.

Thanks.