How does a MYSQL Self-Join Work?

Question

I recently asked a question about Self-Joins and I got a great answer.

The query is meant to find the ID, Start Date, and Price of Event2, Following Event1 by 1 Day.

The code WORKS fine. But I don't understand HOW.

Could someone explain as thoroughly as you can- what the different parts of the query are and what they do?

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

I really appreciate your help, for whatever reason I'm having a really hard time wrapping my head around this.

Answer 1

The way I'd try to understand this is to write out two lists on piece one labelled event1 and one event2. Then list a few records in each list (the lists will be identical) now start at the WHERE in the description below.

We're taking data from two tables (OK the same table used twice, but try to ignore that for the moment)

FROM mm_eventlist_dates event1
JOIN mm_eventlist_dates event2 

It probably helps to read the rest from the bottom up.

  WHERE event1.id=$id

So we want the record from event1 that has the specified record id. Presumably that's exactly one record. Now we figure out the day after that event ended.

 date_add(event1.enddate, INTERVAL 1 DAY)

Now that tells us the records from event2, they need to start on that date,

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

We now have two records identified, what fields do we want?

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

Oh, just the fields from the one whose start date we figured out.

Answer 2

When you create a join in a query you are literally doing that - joining 2 tables together. This can be 2 different or 2 of the same tables, it doesn't matter. When specifying a join, creating an alias for the table (a name that refers to it in the rest of the query) is useful if the tables are different, and essential if they are the same. Your query is taking table 1 (event1) which has the columns:

event1.id, event1.startdate, event1.price

and joining table 2 (event2):

event2.id, event2.startdate, event2.price

which leaves you with the result set:

event1.id, event1.startdate, event1.price, event2.id, event2.startdate, event2.price

The criteria for the join is specified as:

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

which is saying 'For each row in event1 join the row(s) in event2 that has a startdate of 1 day after the startdate in event1'

Then as you are only interested in the data for one event the where clause limits the result set.

WHERE event1.id=$id

Finally, as you don't need the information from event1 about the original event your select statement simply selects the event2 columns from the resultset:

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