Views can contain complex joins and subqueries. The sections that follow cover these and other advanced topics.
Creating a view based on data from multiple tables is quite simple. While in the View Builder, you use the Add Table tool on the toolbar to add additional tables to the view. SQL Server automatically joins the tables based on relationships established in the database.
Just as a T-SQL statement can contain a subquery (see Chapter 6, “Mastering T-SQL”), a view can contain a subquery. The syntax looks like this:
CREATE VIEW vwCustomersWithoutOrders AS SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE NOT EXISTS (SELECT CustomerID ...