Joins in MySQL | Intermediate MySQL

Summary

This tutorial explores various types of SQL joins, including inner joins, left and right outer joins, and self joins, using two tables: employee demographics and employee salary. It explains how joins allow the combination of data from multiple tables based on common columns. The video demonstrates how to write SQL queries for each join type, highlighting how inner joins return matching rows, outer joins include unmatched rows from one table, and self joins connect a table to itself. Additionally, the video covers joining multiple tables, showcasing how different joins can efficiently combine data from various sources.

Takeaways

• 😀 **Inner Join**: An inner join returns only rows that have matching values in both tables. It combines data from tables based on a shared column like `employee_id`.
• 😀 **Outer Join**: Outer joins retrieve all rows from one table and the matching rows from the other table, with `NULL` values for non-matching rows. This includes **Left Join** and **Right Join**.
• 😀 **Left Join**: A left join retrieves all rows from the left table, even if there is no match in the right table. If no match exists, the right table’s values will be `NULL`.
• 😀 **Right Join**: A right join retrieves all rows from the right table and only matching rows from the left table. Non-matching rows in the left table will be filled with `NULL` values.
• 😀 **Self Join**: A self join is when a table is joined with itself. This is useful for comparing rows within the same table, such as assigning Secret Santa in an employee list.
• 😀 **Aliases in Joins**: Using aliases (shortened names) for tables can simplify SQL queries, making them easier to read and understand, especially when working with multiple tables.
• 😀 **Ambiguous Columns**: When column names are the same in both tables being joined, SQL may get confused. This can be resolved by specifying the table names (e.g., `dm.employee_id`).
• 😀 **Selecting Specific Columns**: When using joins, specifying which columns to select avoids ambiguity. For example, `dm.employee_id` instead of just `employee_id`.
• 😀 **Multiple Joins**: SQL allows joining multiple tables together. This is done by chaining joins, where each subsequent table is linked via a shared column.
• 😀 **Using Multiple Tables**: When combining more than two tables, ensure there is a logical relationship between the tables. For instance, an employee table can be joined to a salary table and a department reference table.
• 😀 **Handling Missing Data**: In outer joins, when no matching row exists in one of the tables, the result will include `NULL` values in the columns of the non-matching table.
• 😀 **Best Practices**: Use joins efficiently by understanding when to apply inner or outer joins based on your data requirements and ensuring you use clear aliases and specify columns to avoid ambiguity.

Q & A

What is an inner join in SQL?

An inner join combines rows from two tables where there is a match on the specified columns. It only returns rows that have matching values in both tables.

What does the 'ON' keyword do in an SQL join?

The 'ON' keyword specifies the condition for the join, determining which columns from each table should be used to match rows together.

What is the difference between a left join and a right join?

A left join returns all rows from the left table, including those with no match in the right table (filled with NULLs). A right join returns all rows from the right table, including those with no match in the left table.

Why does the SQL query return an 'ambiguous' error when using columns with the same name in both tables?

The error occurs because SQL cannot determine which table the column belongs to when two tables have the same column name. To resolve this, you need to specify the table name or alias.

How can aliases help improve SQL query readability?

Aliases provide short, meaningful names for tables, making queries easier to read and preventing errors from column name ambiguity, especially when joining multiple tables.

What is a self-join, and when would you use it?

A self-join is a join where a table is joined with itself. It's useful when comparing rows within the same table, such as assigning a 'Secret Santa' in an employee list.

How does a left outer join differ from an inner join in terms of the rows returned?

A left outer join returns all rows from the left table, even if there's no match in the right table, whereas an inner join only returns rows that have matching values in both tables.

What is a scenario where a right join would be more appropriate than a left join?

A right join is useful when you want to return all rows from the right table, including those that have no matching rows in the left table, such as when prioritizing data from the right table.

In the case of joining three tables, how would you structure the SQL query?

You would use multiple JOIN clauses to combine the tables. Each JOIN condition specifies how to match columns between the tables. For example, joining employee salary, department, and demographics tables would require separate ON conditions for each relationship.

How can you join multiple tables that don't directly relate to each other?

You can join tables indirectly by linking them through common columns. For instance, you might join an employee table to a salary table, then link the salary table to a department table using a shared department ID.