MS SQL Server Concepts and Programming Question: Download Questions PDF
How To Test Subquery Results with the EXISTS Operator?
Answer:
EXISTS is a special operator used to test subquery results. EXISTS can be used in two ways:
EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery has one or more rows returned.
NOT EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery no rows returned.
The following tutorial exercise shows you two examples of EXISTS operators. The sample database AdventureWorksLT provided by Microsoft is used.
USE AdventureWorksLT
GO
-- Number of customers with orders
SELECT COUNT(*) FROM SalesLT.Customer c
WHERE EXISTS (
SELECT * FROM SalesLT.SalesOrderHeader s
WHERE s.CustomerID = c.CustomerID
)
GO
32
-- Number of customers without orders
SELECT COUNT(*) FROM SalesLT.Customer c
WHERE NOT EXISTS (
SELECT * FROM SalesLT.SalesOrderHeader s
WHERE s.CustomerID = c.CustomerID
)
408
EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery has one or more rows returned.
NOT EXISTS (SELECT ...)
-- Returns TRUE if the specified subquery no rows returned.
The following tutorial exercise shows you two examples of EXISTS operators. The sample database AdventureWorksLT provided by Microsoft is used.
USE AdventureWorksLT
GO
-- Number of customers with orders
SELECT COUNT(*) FROM SalesLT.Customer c
WHERE EXISTS (
SELECT * FROM SalesLT.SalesOrderHeader s
WHERE s.CustomerID = c.CustomerID
)
GO
32
-- Number of customers without orders
SELECT COUNT(*) FROM SalesLT.Customer c
WHERE NOT EXISTS (
SELECT * FROM SalesLT.SalesOrderHeader s
WHERE s.CustomerID = c.CustomerID
)
408
Download MS SQL Server Interview Questions And Answers
PDF
| Previous Question | Next Question |
| How To Use Wildcard Characters in LIKE Operations in MS SQL Server? | How To Test Values Returned by a Subquery with the IN Operator? |