How Does a MySQL Self-Join Work?

A self-join in MySQL involves joining two instances of the same table, typically with different aliases. It allows you to compare rows of the same table based on specific criteria.

The Query Explained

Let's break down the given query:

SELECT event2.id, event2.startdate, event2.price
FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 
ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)
WHERE event1.id=$id

FROM Clause

This specifies which tables to join:

FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 

The same table, mm_eventlist_dates, is used twice and aliased as event1 and event2.

ON Clause

This joins the two instances of the table based on a condition:

ON event2.startdate = date_add(event1.enddate, INTERVAL 1 DAY)

It finds rows in event2 where the startdate matches the day after the enddate of the corresponding row in event1.

WHERE Clause

This filters the results based on the specified condition:

WHERE event1.id=$id

It selects rows from event1 where the id matches the given variable, $id.

SELECT Clause

This selects the desired fields from the joined rows:

SELECT event2.id, event2.startdate, event2.price

It retrieves the ID, start date, and price from the event2 instance, which contains the information about events following the specified event (event1) by one day.

Visual Demonstration

Imagine you have the following records in the mm_eventlist_dates table:

With the given query:

• It retrieves event1 with id equal to $id (e.g., 1).
• It computes the end date plus one day (2023-03-02).
• It searches event2 for rows with the same start date (2023-03-02).
• It returns the ID, start date, and price of the matching event2 row.

This demonstrates how a MySQL self-join allows you to identify related rows based on specified criteria, even within the same table.