Microsoft® SQL Server® 2012 Bible

227 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 to those rows where LastName is equal to Adams. The net effect is ...

228 Part II: Building Databases and Working with Data MgrID int FOREIGN KEY REFERENCES Employee(EmployeeID)); INSERT INTO dbo.Em ...

229 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 The key to constructing a self-join is to include a second refere ...

230 Part II: Building Databases and Working with Data FIGURE 9-7 A graphical representation of a cross join is simply two tables ...

231 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 is not a requirement. The condition between the two columns is no ...

232 Part II: Building Databases and Working with Data Set Difference Queries ................................................... ...

233 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 FROM dbo.Customer2 c LEFT OUTER JOIN dbo.SalesOrder2 so ON c.Cust ...

234 Part II: Building Databases and Working with Data FIGURE 9-9 A union vertically appends the result of one SELECT statement t ...

235 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 CustomerID Source ----------- -------------------- 103 Customer2 ...

236 Part II: Building Databases and Working with Data Intersection union queries are useful to prove that two queries give the s ...

237 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 Subqueries ...................................................... ...

238 Part II: Building Databases and Working with Data The subquery (SELECT 3) returns a single value of 3 , which is passed to t ...

239 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 Common Table Expressions The common table expression (CTE) defi n ...

240 Part II: Building Databases and Working with Data SELECT... FROM CTE1Name INNER JOIN CTE2Name ON... Although CTEs may includ ...

241 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 Road Bikes 185513.0436 Mountain Frames 54949.602 Jerseys 7094.166 ...

242 Part II: Building Databases and Working with Data Using Subqueries as Tables In the same way that you can use a view in the ...

243 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 (5), (7), (9) ) AS ValuesTable(a) ); Result: AllTest ------------ ...

244 Part II: Building Databases and Working with Data WHERE 5 = SOME (SELECT a FROM (VALUES (2), (3), (5), (7), (9) ) AS MyTable ...

245 Chapter 9: Merging Data with Joins, Subqueries, and CTEs 9 The query asks, “Which products have a list price that is less th ...

246 Part II: Building Databases and Working with Data First, set up some sample data: USE tempdb; CREATE TABLE TableA (ID INT); ...