Database Joins in MySQL

Database Joins in MySQL

Introduction to Joins

• In real-world databases, data is often spread across multiple tables for efficiency.
• The JOIN keyword allows you to pull data from multiple tables using a single SELECT query.
• Joins match related data across tables, often using primary key and foreign key relationships.

Types of Joins

• Inner Join (Simple Join): Returns rows where the specified data appears in both tables.
  • If you imagine the data in one table as a set and the data in a second table as a set, then pick a type of value like a name or an ID number, and an inner join will return rows where that same data appears in both tables.
• Outer Join: Selects all data from one table and only matching data from a second table.
  • If there is no matching data in the second table, NULL values are returned for the columns from the second table.
  • In an inner join, these rows would not appear in the results at all.

Left vs. Right Outer Joins

• Outer joins can be either left or right.
• Left Outer Join: Returns all rows from the first table (left table) in the JOIN clause and matching rows from the second table.
• Right Outer Join: Returns all rows from the second table (right table) in the JOIN clause and matching rows from the first table.
• The difference lies in which table returns all rows versus only matching rows.
• Left outer joins are more common than right outer joins.

Join Conditions

• Most JOIN clauses require a condition that specifies which columns will be matched across the tables (e.g., table1.ID = table2.ID).
• If an inner join is used without a condition, it will match every row from the first table with every row from the second table, creating a very large dataset. This is generally not desired unless one of the datasets is very small.
• In MySQL, JOIN, INNER JOIN, and CROSS JOIN are often used interchangeably.
  • This differs from standard SQL, where inner joins require a condition, and cross joins do not.

Inner Joins in Detail

• Inner joins are the most common type of join in MySQL.
• If you simply use the JOIN keyword without specifying the type, it defaults to an inner join.

Example: Joining Movies Data

• The movies database is split into multiple tables (e.g., titles, studio).
• Suppose you want to match the title of each film from the titles table to the city it was filmed in, available in the studio table.
• The titles table has a studioID column (foreign key) that refers to the ID column (primary key) of the studio table.

Query Example:

• To select the film title and the city where it was filmed, you can use the following query:
  sql SELECT titles.title, studio.city FROM titles INNER JOIN studio ON titles.studioID = studio.ID ORDER BY titles.title;
• It is best practice to explicitly specify the table name for each column (e.g., titles.title) when joining multiple tables.

The Importance of the ON Clause

• The ON clause is crucial because it tells MySQL which data is the same in both tables.
• Using foreign keys enforces the integrity of the link between tables.
• Joining on a primary key/foreign key relationship ensures data consistency.
• An incorrect join condition can return invalid data that may appear correct at first glance (e.g., joining on directorID instead of studioID).

Using Multiple Joins

• It is possible to use multiple joins in a single query.
• Example: Linking a director to the critic score for their movies.

Query Example:

• To select the director name, movie title, and critic rating, you can use the following query:
  sql SELECT director.dir_name, titles.title, criticrating.criticsRating FROM titles INNER JOIN director ON titles.directorID = director.ID INNER JOIN criticrating ON criticrating.titlesID = titles.ID ORDER BY director.dir_name;
• The order in which you join the tables becomes less meaningful as you add more joins.
• The equality in the ON clause is reversible (e.g., titles.ID = criticrating.titlesID is equivalent to criticrating.titlesID = titles.ID).

Selecting Columns from Joined Tables

• Even if a table is required to join data, you do not necessarily need to select any columns from it.
• You can link data without including all columns in the final result.

Outer Joins in Detail

• An outer join is used to select all data from one table and only matching data from a second table.
• This is useful for identifying missing data or when data in one table is optional.

Example: Finding Titles Without Poster Art

• Create a new table named posters that contains filenames and resolutions for film posters.
  sql CREATE TABLE posters ( ID INT PRIMARY KEY AUTO_INCREMENT, titlesID INT, poster_filename VARCHAR(30), resolution VARCHAR(10), FOREIGN KEY (titlesID) REFERENCES titles(ID) );
• Populate the posters table with data.
• Note that the posters table has fewer rows than the titles table, indicating that some titles do not have poster art.

Query Example:

• To find titles without poster art, use a left outer join:
  sql SELECT titles.title, director.dir_name, posters.filename, posters.resolution FROM titles INNER JOIN director ON titles.directorID = director.ID LEFT OUTER JOIN posters ON posters.titlesID = titles.ID ORDER BY titles.title;
• Rows with NULL values in the filename and resolution columns indicate titles without corresponding poster art.
• If you change the left outer join to an inner join, only rows with matching data in the posters table will be returned.
• A right outer join would return all rows from the posters table and matching rows from the titles table. Because of the foreign key relationship, this will produce a result very similar to an inner join due to no posters existing without a corresponding title.

Challenge: Finding the Best Film

• Use inner and outer joins to find information about the best film in the database.
• The result should include the title, director name, critic's rating, and poster filename (if available).
• The query should return only one row (the best film).

Solution:

• Select the required columns:
  sql SELECT titles.title, director.dir_name, criticrating.criticsRating, posters.filename FROM titles INNER JOIN director ON titles.directorID = director.ID INNER JOIN criticrating ON criticrating.titlesID = titles.ID LEFT OUTER JOIN posters ON titles.ID = posters.titlesID ORDER BY criticrating.criticsRating DESC LIMIT 1;
• The best film is determined by the highest critic's rating.
• The query returns the film with the highest rating, its director, rating, and poster filename (if available).