1 / 10

Advanced SQL

In this lesson, we explore advanced SQL join techniques, including Equi-Join, One-Sided Outer Join, and Full Outer Join. We will guide you on how to leverage these joins in Microsoft SQL Server Management Studio (SSMS) to retrieve and analyze data from multiple tables. You will learn how to specify which side of the join to maintain all records from, determine NULL conditions, and understand the significance of counting fields correctly. Resources and examples will deepen your understanding of relational database queries.

trey
Télécharger la présentation

Advanced SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft Enterprise Consortium Advanced SQL Equi-Join One-sided Outer Join Full Outer Joiin Microsoft Enterprise Consortium: http://enterprise.waltoncollege.uark.edu Microsoft Faculty Connection/Faculty Resource Center http://www.facultyresourcecenter.com

  2. What you’ll need … • Log in to MEC for this lesson and into MSSMS (Microsoft SQL Server Management Studio). • Be sure to select your account ID under Database in the Object Explorer pane, similar to the example shown here. • You should be very comfortable with the SQL commands covered in the SQL Fundamental series.

  3. Equi-Join • Equi-join: When you join two tables in a query, by default, you only get output for data that has related data in both tables. This is called an “equi-join.” • We’ve seen an example of this in the Student-Team database. The output on the left is based on 1 table. The one on the right uses 2 tables.

  4. Equi-Join and One-Sided Join • One-sided join: A one-sided join includes all the data on the “one side” and any related data from the other table. • The output on the left uses an equi-join. Add either “right” or “left” to specify from which table you want all data.

  5. One-Sided Join: Left or Right? • The choice of “left” or “right” is based on which table you want all the data from and the order it is listed in the FROM clause. • Left and right have been used below and the output is the same because the order the tables are listed was switched.

  6. One-Sided Join: Show all teams • Let’s do a one-sided outer join that shows ALL teams and the # of students on each team. • The output on the left demonstrates that sometimes it is important to count a field, not count(*).

  7. Full outer join • A FULL outer join shows all data from both tables. /* Full outer join. Show ALL students and ALL teams. */ select teams.teamID, team_name, stdid, stdlname from teams full join students on teams.teamID = students.std_teamID;

  8. One-Sided JOIN and NULL • If we ask the question “Which teams don’t have students assigned?”, we use a one-sided join and the IS NULL criterion. /* Show teams without students assigned. */ select teams.teamID, team_name, stdid, stdlname from teams left join students on teams.teamID = students.std_teamID where students.std_teamID is null;

  9. What was covered … • Equi-join • One-sided outer join • Left • Right • Full outer join • One-sided outer join with IS NULL

  10. Resources • http://enterprise.waltoncollege.uark.edu/mec.asp • Microsoft Faculty Connection—Faculty Resource Center http://www.facultyresourcecenter.com/ • Microsoft Transact-SQL Reference • http://msdn.microsoft.com/en-us/library/aa299742(v=SQL.80).aspx • AdventureWorks Sample Database • http://msdn.microsoft.com/en-us/library/ms124659%28v=sql.100%29.aspx

More Related