Chapter 4 slides - Chapter 4 How to retrieve data from two...

This preview shows page 1 - 11 out of 44 pages.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 1 Chapter 4 How to retrieve data from two or more tables
Image of page 1

Subscribe to view the full document.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 2 Objectives Applied Use the explicit syntax to code an inner join that returns data from a single table or multiple tables. Code a union that combines data from a single table or multiple tables. Knowledge Explain when column names need to be qualified. Describe the proper use of correlation names. Describe the differences between an inner join, a left outer join, a right outer join, a full outer join, and a cross join. Explain why you don’t need to use right outer joins. Describe the use of the implicit syntax for coding inner joins.
Image of page 2
Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 3 Objectives (cont.) Describe the use of unions including the use of the EXCEPT and INTERSECT operators.
Image of page 3

Subscribe to view the full document.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 4 The explicit syntax for an inner join SELECT select_list FROM table_1 [INNER] JOIN table_2 ON join_condition_1 [[INNER] JOIN table_3 ON join_condition_2]...
Image of page 4
Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 5 An inner join of the Vendors and Invoices tables SELECT InvoiceNumber, VendorName FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID; The result set (114 rows)
Image of page 5

Subscribe to view the full document.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 6 The syntax for an inner join that uses correlation names SELECT select_list FROM table_1 [AS] n1 [INNER] JOIN table_2 [AS] n2 ON n1.column_name operator n2.column_name [[INNER] JOIN table_3 [AS] n3 ON n2.column_name operator n3.column_name]...
Image of page 6
Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 7 Correlation names that make the query more difficult to read SELECT InvoiceNumber, VendorName, InvoiceDueDate, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Vendors AS v JOIN Invoices AS i ON v.VendorID = i.VendorID WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0 ORDER BY InvoiceDueDate DESC; The result set (11 rows)
Image of page 7

Subscribe to view the full document.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 8 A correlation name that simplifies the query SELECT InvoiceNumber, InvoiceLineItemAmount, InvoiceLineItemDescription FROM Invoices JOIN InvoiceLineItems AS LineItems ON Invoices.InvoiceID = LineItems.InvoiceID WHERE AccountNo = 540 ORDER BY InvoiceDate; The result set (6 rows)
Image of page 8
Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 9 The syntax of a fully-qualified object name linked_server.database.schema.object A join with fully-qualified table names SELECT VendorName, CustLastName, CustFirstName, VendorState AS State, VendorCity AS City FROM DBServer.AP.dbo.Vendors AS Vendors JOIN DBServer.ProductOrders.dbo.Customers AS Customers ON Vendors.VendorZipCode = Customers.CustZip ORDER BY State, City; The result set (37 rows)
Image of page 9

Subscribe to view the full document.

Murach's SQL Server 2012, C4 © 2012, Mike Murach & Associates, Inc. Slide 10 The same join with partially-qualified table names SELECT VendorName, CustLastName, CustFirstName,
Image of page 10
Image of page 11
  • Fall '14
  • Articles with example SQL code, Query optimizer, Join, Mike Murach

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern