Advanced SQL Queries: Subtleties of Joins
3.8 (130 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,196 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Advanced SQL Queries: Subtleties of Joins to your Wishlist.

Add to Wishlist

Advanced SQL Queries: Subtleties of Joins

A guide to writing correct and efficient SQL join logic
3.8 (130 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
4,196 students enrolled
Created by Dr. michael blaha
Last updated 11/2016
English
Price: Free
Includes:
  • 1 hour on-demand video
  • 12 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Understand that there are different ways of phrasing joins with trade-offs.
  • Be able to write join logic that efficiently and correctly accesses data.
  • Have examples that demonstrate proper SQL join logic.
View Curriculum
Requirements
  • Students must have prior experience with SQL. The course does not teach basic SQL. This is an advanced course.
Description

Do you work with databases? Do you use SQL as part of your work? If so, would you like to use SQL with greater proficiency?

Then this course is for you! In “Subtleties of Joins” we study different ways of using joins in database queries and their trade-offs.

Joins can be computationally expensive, so it’s important to express them properly. Furthermore, a misstated query can yield a wrong result, Correct use of joins makes your SQL code more readable and understandable. This is especially important for lengthy and complex queries.

Specifically, this course covers the following

  • Join vs. Where. There are guidelines for placing logic in joins vs. where clauses.
  • Join filtering. Outer joins necessitate that some data filters be in joins rather than in where clauses.
  • Joins vs. Nested SQL. Queries with nested SQL can sometimes be restated to use joins instead.

Who is the target audience?
  • Developers who write SQL code and have at least a year of SQL experience.
  • You should take this course if you want to develop deep database skills as part of your career path.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
+
Welcome to Advanced SQL
3 Lectures 06:23

This lecture welcomes you to the course and provides a high-level outline.

About The Course
02:19

This lecture tells you about the instructor, Dr. Michael Blaha.

About the Instructor
02:52

This is a brief lecture about the importance of advanced SQL and the mindset you should have for this course.

Why Use Advanced SQL?
01:12
+
Case Study
5 Lectures 29:59

The course includes a sample database for executing SQL queries.

This lecture presents the data model for the database. We use the Oracle Data Modeler tool, which is free.

Data Model, Part 1
04:57

The course includes a sample database for executing SQL queries.

Here are fine details for the data model and a few references in case you have further interest in data modeling.

Data Model, Part 2
04:31

The course includes a sample database for executing SQL queries.

In this lecture we elaborate the data model with logical design details. We cover the steps...

  • Create domains.
  • Assign domains to attributes.
  • Set nullability for attributes.
Database Logical Design
05:44

The course includes a sample database for executing SQL queries.

In this lecture we add physical design details. We cover the steps...

  • Set identity for primary keys.
  • Add indexes for foreign keys.
  • Set referential integrity defaults.
  • Generate database schema.
  • Run the schema on SQL Server to create an empty case study database.

You can obtain a development copy of SQL Server from Microsoft for free.

Database Physical Design
09:58

The course includes a sample database for executing SQL queries.

In this lecture we present sample data for populating the case study database and load it with SQL Server.


Case Study Data
04:49
+
SQL Queries
5 Lectures 19:49

This lecture presents two equivalent SQL queries, with and without joins. We give advice for what logic to place in where clauses and what logic to place in joins.

Join vs. Where
03:31

This lecture shows a nice technique for placing logic in join clauses. You can join a SELECT...FROM...WHERE statement to another SELECT...FROM...WHERE statement.

Join Filtering, Part 1
01:53

This lecture explains the difference among...

  • inner join
  • left outer join
  • right outer join
  • full outer join
Join Filtering, Part 2
03:11

This lecture revisits the lecture from Join Filtering, Part 1 and adds the complexity of an outer join. We show that the query with join filters has a different result than the query with where filters. We carefully explain the reason for the difference.

Join Filtering, Part 3
09:25

Now we look at join vs. nested SQL. You should use joins instead of nested SQL where possible.

Join vs. Nested SQL
01:49
+
Conclusion
1 Lecture 01:56

And finally now we conclude the course! We summarize the tips presented earlier and invite your feedback.

Conclusion
01:56
About the Instructor
Dr. michael blaha
3.8 Average rating
130 Reviews
4,196 Students
1 Course
Expert with all aspects of databases

Michael Blaha is a consultant, author, and trainer who specializes in conceiving, architecting, modeling, designing, and tuning databases. He has worked with dozens of organizations around the world. Blaha has authored seven U.S. patents, seven books, many articles, and several video courses. He received his doctorate from Washington University in St. Louis and is an alumnus of GE Global Research in Schenectady, New York. He is a member of Chicago DAMA.