c# generate 2d barcode Figure 4-28. The results of a query using a CTE in Font

Generator PDF 417 in Font Figure 4-28. The results of a query using a CTE

Figure 4-28. The results of a query using a CTE
PDF-417 2d Barcode Drawer In None
Using Barcode creator for Font Control to generate, create PDF 417 image in Font applications.
www.OnBarcode.com
Draw Denso QR Bar Code In None
Using Barcode drawer for Font Control to generate, create QR Code image in Font applications.
www.OnBarcode.com
CHAPTER 4 QUERYING MULTIPLE TABLES
Paint UPC Symbol In None
Using Barcode generator for Font Control to generate, create UPC A image in Font applications.
www.OnBarcode.com
Barcode Creator In None
Using Barcode printer for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
Using a CTE to Solve a Complicated Join Problem
Drawing Barcode In None
Using Barcode creator for Font Control to generate, create Barcode image in Font applications.
www.OnBarcode.com
Code 128C Encoder In None
Using Barcode generation for Font Control to generate, create ANSI/AIM Code 128 image in Font applications.
www.OnBarcode.com
The examples in the previous sections on joining tables demonstrated very simple join conditions, one or two columns from one table equal to the same number of columns in another table. Join conditions may be much more complicated. For example, suppose you wanted to produce a list of all customers along with the orders, if any, placed on a certain date. Figure 4-29 shows a diagram of this query.
Encode Data Matrix ECC200 In None
Using Barcode creation for Font Control to generate, create ECC200 image in Font applications.
www.OnBarcode.com
Generating ITF In None
Using Barcode drawer for Font Control to generate, create ITF image in Font applications.
www.OnBarcode.com
Figure 4-29. A diagram of a CTE query Listing 4-20 demonstrates the problem and how to solve it with a CTE. Listing 4-20. Using a CTE to Solve a Problem USE AdventureWorks2008; GO --1 SELECT c.CustomerID, s.SalesOrderID, s.OrderDate FROM Sales.Customer AS c LEFT OUTER JOIN Sales.SalesOrderHeader AS s ON c.CustomerID = s.CustomerID WHERE s.OrderDate = '2001/07/01'; --2 WITH orders AS ( SELECT SalesOrderID, CustomerID, OrderDate FROM Sales.SalesOrderHeader WHERE OrderDate = '2001/07/01' ) SELECT c.CustomerID, orders.SalesOrderID, orders.OrderDate FROM Sales.Customer AS c LEFT OUTER JOIN orders ON c.CustomerID = orders.CustomerID ORDER BY orders.OrderDate DESC;
Reading PDF-417 2d Barcode In Java
Using Barcode recognizer for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Scan PDF-417 2d Barcode In None
Using Barcode reader for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
CHAPTER 4 QUERYING MULTIPLE TABLES
Read Code 39 Full ASCII In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
www.OnBarcode.com
Encode Barcode In VS .NET
Using Barcode encoder for Reporting Service Control to generate, create Barcode image in Reporting Service applications.
www.OnBarcode.com
Take a look at the results in Figure 4-30. Query 1 returns only the 43 rows with the specified order date. The nonmatching rows dropped out of the query because of the NULLs and values other than 2001/07/01 in the OrderDate column. If you want to show all customers even if there is not an order placed on the specified date, then by adding the WHERE clause to the CTE instead, the NULL values and other OrderDate values do not cause any problems, and the correct results are returned.
Code 39 Generator In .NET Framework
Using Barcode drawer for ASP.NET Control to generate, create Code 3/9 image in ASP.NET applications.
www.OnBarcode.com
Code 3/9 Creator In Java
Using Barcode printer for Android Control to generate, create Code39 image in Android applications.
www.OnBarcode.com
Figure 4-30. The results of using a CTE to solve a tricky query This section demonstrated how to use derived tables and common table expressions. The examples, except for the last one, covered queries that you could have easily written using joins. In 5, you will learn more examples of how to use these techniques when regular joins will not work. Practice writing queries with derived tables and common table expressions by completing Exercise 4-4.
Making Barcode In Visual Studio .NET
Using Barcode creation for ASP.NET Control to generate, create Barcode image in ASP.NET applications.
www.OnBarcode.com
EAN-13 Supplement 5 Maker In Java
Using Barcode generator for Java Control to generate, create European Article Number 13 image in Java applications.
www.OnBarcode.com
CHAPTER 4 QUERYING MULTIPLE TABLES
Code-39 Creation In Java
Using Barcode generator for Java Control to generate, create USS Code 39 image in Java applications.
www.OnBarcode.com
Paint Barcode In Visual Studio .NET
Using Barcode creator for Visual Studio .NET Control to generate, create Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Exercise 4-4
Barcode Decoder In Visual Basic .NET
Using Barcode reader for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Draw Code 39 Full ASCII In Objective-C
Using Barcode creator for iPad Control to generate, create Code 3/9 image in iPad applications.
www.OnBarcode.com
Use the AdventureWorks2008 database to complete this exercise. You can find the solutions in the appendix. 1. Using a derived table, join the Sales.SalesOrderHeader table to the Sales.SalesOrderDetail table. Display the SalesOrderID, OrderDate, and ProductID columns in the results. The Sales.SalesOrderDetail table should be inside the derived table query. 2. Rewrite the query in question 1 with a common table expression. 3. Write a query that displays all customers along with the orders placed in 2001. Use a common table expression to write the query and include the CustomerID, SalesOrderID, and OrderDate columns in the results.
Thinking About Performance
Often, using different query techniques can produce the same execution plan, or at least similar performance. To see an example of this, toggle on the Include Actual Execution Plan setting, and run Listing 4-21, which shows two techniques to get the same results. Listing 4-21. Comparing the Performance of Two Techniques USE AdventureWorks2008; GO --1 SELECT DISTINCT c.CustomerID FROM Sales.Customer AS c INNER JOIN Sales.SalesOrderHeader AS o ON c.CustomerID = o.CustomerID; --2 SELECT CustomerID FROM Sales.Customer WHERE CustomerID IN (SELECT CustomerID FROM Sales.SalesOrderHeader); Figure 4-31 shows the identical execution plans. These queries produce identical results but with very different techniques.
CHAPTER 4 QUERYING MULTIPLE TABLES
Figure 4-31. The identical execution plans As you learn to write more complicated queries, especially using aggregate functions in 5, you will learn that how you join the tables can make a big difference in performance. To experiment a bit more with performance, complete Exercise 4-5.
Exercise 4-5
Use the AdventureWorks2008 database to complete this exercise. You can find the solutions in the appendix. Run the following code to add and populate a new column, OrderID, to the Sales.SalesOrderDetail table. After running the code, the new column will contain the same data as the SalesOrderID column.
USE AdventureWorks2008; GO ALTER TABLE Sales.SalesOrderDetail ADD OrderID INT NULL; GO UPDATE Sales.SalesOrderDetail SET OrderID = SalesOrderID;
Copyright © OnBarcode.com . All rights reserved.