Table Joins Week 3

Table Joins Week 3 - Table Joins Subqueries and Views Table...

Info iconThis preview shows pages 1–2. Sign up to view the full content.

View Full Document Right Arrow Icon
Table Joins, Subqueries, and Views Table Joins | Subqueries | Using Views | Indexes | Clustered Indexes | Query Optimization An advantage of relational databases is that data is stored in multiple tables and relationships are created between the tables. This week, we will learn how to extract information from multiple tables to support more complex business needs. When we are joining tables together, we have to determine which field will be used in the join - there has to be a matching field in the tables being joined. The aggregate functions we used to summarize our data can still be used when joining tables. We will also introduce some complex SQL statements that require the use of subqueries. A view in SQL Server is a query that has been named and saved to be used at a later time. Views reduce the complexity of the database for the users and, at the same time, add some security to the database. Table Joins In responding to a request for data, we often need to extract information from more than one table. Using the Coffee Merchant database, if we need to provide a list of consumer names and information on every order that our consumers have placed with us, we will need to use two tables: the consumer name is stored in the Consumers table and order information is stored in the Orders table. Before attempting to join two tables together, you will have to review the tables and determine if it is even possible. There must be a matching field in each table. The ER diagram is an invaluable tool for this. The Consumers table uses a ConsumerID to uniquely identify each consumer; the Orders table needs to keep track of which order was placed by which consumer. It does this by placing the ConsumerID field in the Orders table. How will joining tables affect our SELECT statements? First of all, the required fields from both tables will be in the field list. Then, we have to specify exactly how these tables will be joined. To join tables, you will use an INNER JOIN in the FROM clause: SELECT C.ConsumerID, C.LastName, C.FirstName, O.OrderID, O.OrderDate, O.CustomerPO FROM Consumers C INNER JOIN Orders O ON C.ConsumerID = O.ConsumerID; Notice that each field name in the SELECT clause uses a table qualifier (or alias) indicating the table this field is drawn from. The table qualifier must be specified after the table name in the FROM clause and even though it is an abbreviation for the table name, it should still be meaningful – don’t just use A, B, C, and so on. The only change in using the WHERE and ORDER BY clauses is that we need to use the table alias before the field names. Let’s try another join. The inventory items in the Coffee Merchant database come from different countries. We need to produce a list of inventory items and the name of the country each item comes from. The matching field between these two tables is the CountryID field. So our statement will be: SELECT C.CountryName, I.Name FROM Countries C INNER JOIN Inventory I ON C.CountryID = I.CountryID;
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
Image of page 2
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 5

Table Joins Week 3 - Table Joins Subqueries and Views Table...

This preview shows document pages 1 - 2. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online