How To Test Subquery Results with the EXISTS Operator?
Submitted by: AdministratorEXISTS 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
Submitted by: Administrator
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
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | How To Declare and Use Cursor Variables? |
☺ | How To Find Out What Is the Default Collation in a Database? |
☺ | What Are Binary String Data Types in MS SQL Server? |
☺ | How To Convert a Numeric Expression from One Data Type to Another? |
☺ | What Happens If Strings Are Casted into Wrong Code Pages in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |