Job Ready SQL

Lesson 11Adding JOIN Queries

Relational databases model both data and relationships within the data. Their approach to relationships is clever.

• Related things don't have to be stored together.
• You can ignore related data when you don't need it.
• When you need related data, it's quick to retrieve.

Up to this point, the SELECT queries you've created ignore related data. They retrieve data from one table at a time. A single table SELECT is powerful, but it doesn't take advantage of SQL's full potential. With one small tweak, adding a bit of JOIN syntax, SELECT queries can read from multiple tables and express relationships between data explicitly.

Learning Objectives

By the end of this lesson, you will be able to:

• See the relationship between tables in a database relationship diagram, also called an entity‐relationship diagram
• Understand the purpose of the JOIN keyword
• Use the JOIN keyword to retrieve data from two or more tables in a single query
• Differentiate between an INNER JOIN, an OUTER JOIN, a CROSS JOIN, and a SELF JOIN
• Use SQL aliases

STARTING WITH A SCHEMA

For this lesson, the TrackIt schema will be used. You can create this schema by running the trackit‐schema‐and‐data.sql script that can be found in the Lesson 11 folder on the downloadable files for this book.

NOTE If you haven't downloaded the files for this book, you can find them at www.wiley.com/go/JobReadySQL. For this lesson, you should use the script file in the Lesson 11 folder.

The script ...