Advanced SQL Queries: Subtleties of Joins

Visit Tutorial Page ( Report)
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.
  • Welcome to Advanced SQL
    • About The Course

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

    • About the Instructor

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

    • Why Use Advanced SQL?

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

  • Case Study
    • Data Model, Part 1

      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 2

      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

    • Database Logical Design

      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 Physical Design

      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

    • Case Study Data

      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.

  • SQL Queries
    • Join vs. Where

      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 Filtering, Part 1

      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 2

      This lecture explains the difference among... inner join left outer join right outer join full outer join

    • Join Filtering, Part 3

      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 vs. Nested SQL

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

  • Conclusion
    • Conclusion

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

Write Your Review

Reviews