JOINS - INNER, LEFT AND RIGHT

This lesson on JOINS - INNER, LEFT AND RIGHT is hands-on and example-driven. You will be able to combine data from two or more tables using various JOIN types, ensuring data integrity and resolving column conflicts. You will master INNER, LEFT, RIGHT, and SELF joins to retrieve precise datasets based on matching criteria.

What You'll Be Able To Do

• Combine two tables using the INNER JOIN clause based on a shared key.
• Resolve column ambiguity errors by prefixing column names with appropriate table aliases.
• Implement LEFT JOIN to retain all records from the primary table, regardless of matches.
• Perform a SELF JOIN to compare rows within the same table for hierarchical or sequential analysis.
• Chain multiple JOIN operations efficiently to connect three or more related tables.

Detailed Concept Walkthrough

1. INNER JOIN Fundamentals

The INNER JOIN returns only the rows that have matching values in both tables being joined. It is the most restrictive join type, ensuring data integrity by excluding unmatched records.

• Mechanism: The database engine scans both tables and pairs rows where the condition specified in the ON clause evaluates to TRUE.
• Under the Hood: If you use the keyword JOIN without specifying INNER, SQL defaults to performing an INNER JOIN.
• Syntax Rule: The join condition must always be specified using the ON keyword, typically equating the common key column from both tables.

SELECT * 
FROM employee_demographics AS dem
JOIN employee_salary AS sal
	ON dem.employee_id = sal.employee_id;

Key Takeaway: INNER JOIN only keeps the intersection of the two datasets.

2. Handling Ambiguous Columns and Aliasing

Ambiguity occurs when the same column name exists in both joined tables; aliasing provides short, readable prefixes to resolve this conflict and improve query clarity.

• Best Practice: Assign short aliases (e.g., dem for employee_demographics) immediately after the table name in the FROM or JOIN clause using the AS keyword.
• Mechanism: When selecting columns, prefix the column name with the table alias (e.g., dem.employee_id) to explicitly tell the database which table the column belongs to.
• Execution Flow: The database reads the alias definition first, then uses that alias throughout the rest of the query (SELECT, WHERE, ON clauses).

SELECT dem.first_name, sal.salary 
FROM employee_demographics AS dem
JOIN employee_salary AS sal
	ON dem.employee_id = sal.employee_id;

Key Takeaway: Always use aliases for joined tables to prevent ambiguity and increase readability.

3. OUTER JOINS (LEFT and RIGHT)

Outer joins retrieve all records from one table (the 'side' specified) and only the matching records from the other, filling non-matches with NULL values.

• LEFT JOIN: Returns all rows from the table listed immediately after FROM (the 'left' table) and matched rows from the 'right' table.
• RIGHT JOIN: Returns all rows from the table listed immediately after the RIGHT JOIN keyword (the 'right' table) and matched rows from the 'left' table.
• Under the Hood: Unmatched columns from the non-retained side are automatically populated with NULL values in the result set.

SELECT * 
FROM employee_demographics AS dem
LEFT JOIN employee_salary AS sal
	ON dem.employee_id = sal.employee_id;

Key Takeaway: Use LEFT or RIGHT JOIN when you need to retain all records from one specific table, even if no match exists.

4. SELF JOIN

A SELF JOIN is a regular join where a table is joined to itself, allowing you to compare or relate rows within the same dataset, often used for hierarchical data or sequential analysis.

• Mechanism: The table is treated as two separate, distinct tables by assigning two different aliases to the same table in the FROM clause.
• Syntax Rule: Aliases are mandatory for SELF JOINs; without them, the database cannot distinguish between the two instances of the table.
• Practical Use: Useful for finding employees who make more than their manager, or comparing sequential records (e.g., employee ID N vs employee ID N+1).

SELECT emp1.employee_id, emp2.employee_id
FROM employee_salary AS emp1
JOIN employee_salary AS emp2
	ON emp1.employee_id + 1 = emp2.employee_id;

Key Takeaway: A SELF JOIN requires two distinct aliases to treat one table as two separate entities.

Topics Covered in JOINS - INNER, LEFT AND RIGHT

1. Basic INNER JOIN (0:46 - 2:20) — The video introduces the concept of joining tables using the common column key.
2. Ambiguity and Aliasing (2:21 - 5:51) — The instructor demonstrates how to use table names and aliases to resolve column ambiguity errors.
3. LEFT and RIGHT Joins (6:15 - 8:23) — Outer joins are explained, focusing on how LEFT and RIGHT joins retain all rows from one side.
4. SELF JOIN Implementation (8:25 - 13:13) — A SELF JOIN is performed by aliasing the same table twice to compare rows internally.
5. Joining Multiple Tables (13:16 - 16:51) — The lesson concludes by showing how to chain multiple join operations sequentially in a single query.

SQL Cheat Sheet

• JOIN / INNER JOIN — Returns only rows with matches in both tables

  SELECT * FROM T1 JOIN T2 ON T1.id = T2.id;
  

• LEFT JOIN — Returns all rows from the left table, plus matches

  SELECT * FROM T1 LEFT JOIN T2 ON T1.id = T2.id;
  

• RIGHT JOIN — Returns all rows from the right table, plus matches

  SELECT * FROM T1 RIGHT JOIN T2 ON T1.id = T2.id;
  

• AS (Aliasing) — Assigns a temporary, short name to a table or column

  FROM employee_demographics AS dem
  

• ON clause — Specifies the condition used to link the tables

  ON dem.employee_id = sal.employee_id
  

• Chaining Joins — Connects three or more tables sequentially

  • In practice: T1 JOIN T2 ON ... JOIN T3 ON ...

Comparison Table

Common Pitfalls

• Mistake: Not qualifying ambiguous columns. Avoid: Always use alias.column_name in SELECT and ON clauses.
• Mistake: Forgetting the mandatory ON clause. Avoid: Every JOIN statement must be followed by an ON condition.
• Mistake: Confusing LEFT and RIGHT join results. Avoid: The table listed first (FROM) is the 'left' table.
• Mistake: Using a SELF JOIN without aliases. Avoid: Assign two unique aliases to the single table instance.

FAQs

• Is there a difference between JOIN and INNER JOIN? No. The keyword JOIN is shorthand; SQL defaults to performing an INNER JOIN if no type (LEFT, RIGHT, FULL) is specified.
• Why are aliases necessary if column names are unique? Aliases are not strictly necessary if column names are unique, but they drastically improve code readability and reduce typing, which is essential for complex queries.
• When would I use a SELF JOIN in a real scenario? SELF JOINs are commonly used to compare employees to their managers (who are also employees) or to find sequential events in a log table.
• How do I join three tables? You chain them: Table A JOIN Table B ON condition 1 JOIN Table C ON condition 2. Each join requires its own ON clause.