SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  4. 4.  Views
  1. 1.  Best Practices for Readable and Maintainable SQL Code
  2. 2.  Writing Efficient SQL Queries
  3. 3.  Understanding Query Optimization Methods
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Lesson 5.7: SELF JOIN - Joining a Table to Itself

A SELF JOIN is not a SQL keyword at all, but simply a common term for a situation where a table is joined to itself. In practice, this is implemented using regular kinds of JOIN, most often INNER JOIN or LEFT JOIN, depending on the required logic. This is useful for querying hierarchical data or comparing rows within the same table.

What is a SELF JOIN?

To perform a self-join, you must treat one table as if it were two separate tables. To do this, you must use table aliases to give each instance of the table a unique name. Without aliases, the database won't know which column belongs to which instance of the table.

Visualization (Employee Hierarchy): Imagine an employee table where each row has a manager_id that points to the employee_id of their supervisor.

   Table A (Employees)          Table B (Managers)
   +----+-------+---------+     +----+-------+
   | id | name  | mgr_id  |     | id | name  |
   +----+-------+---------+     +----+-------+
   | 1  | Alice | NULL    |     | 1  | Alice |
   | 2  | Bob   | 1       | <-> | 1  | Alice | (Bob's Manager is Alice)
   | 3  | Carol | 1       | <-> | 1  | Alice | (Carol's Manager is Alice)
   +----+-------+---------+     +----+-------+

SELF JOIN Syntax

SELECT
    e.name AS employee_name,
    m.name AS manager_name
FROM
    employee AS e
LEFT JOIN
    employee AS m ON e.manager_id = m.id;
  • employee AS e: The first instance (representing the employees).
  • employee AS m: The second instance (representing the managers).
  • ON e.manager_id = m.id: The condition that links them.

Practical Examples (Sakila Database)

1. Finding Films with the Same Duration

Suppose we want to find pairs of films that have exactly the same length. We can join the film table to itself.

SELECT
    f1.title AS film_1,
    f2.title AS film_2,
    f1.length
FROM
    film AS f1
INNER JOIN
    film AS f2 ON f1.length = f2.length
WHERE
    f1.film_id <> f2.film_id -- Ensure we don't match a film with itself
LIMIT 10;

The condition f1.film_id <> f2.film_id is critical. Without it, every film would match itself (since it has the same length as itself).

2. Finding Customers from the Same City

If we want to see which customers live in the same city (based on address_id in this simplified example):

SELECT
    c1.first_name AS cust_1_first,
    c1.last_name AS cust_1_last,
    c2.first_name AS cust_2_first,
    c2.last_name AS cust_2_last,
    c1.address_id
FROM
    customer AS c1
INNER JOIN
    customer AS c2 ON c1.address_id = c2.address_id
WHERE
    c1.customer_id < c2.customer_id; -- Use '<' instead of '<>' to avoid duplicate pairs (A-B and B-A)

Key Takeaways from This Lesson

  • A SELF JOIN is a term for joining a table to itself, not a separate SQL keyword.
  • It is usually implemented with regular JOIN types such as INNER JOIN or LEFT JOIN.
  • Table Aliases are mandatory to distinguish between the two instances of the table.
  • Use ON conditions to define the relationship between rows (e.g., hierarchy or shared attributes).
  • Use filter conditions such as id1 <> id2 or id1 < id2 to avoid matching a row with itself or returning redundant pairs. In the case of LEFT JOIN, part of this logic may be placed not only in WHERE, but also in the ON condition.