Up till now, we've been joining our tables on columns that were part of an equality relation between the ids of different tables. Let's briefly recall what that means.
Imagine we have two tables: student and band.
| student | ||
|---|---|---|
| id | name | fav_band_id |
| 1 | Mark Goal | 2 |
| 2 | John Doe | 2 |
| 3 | Martin Kowalsky | 1 |
| 4 | Jessica Jones | 3 |
| band | |
|---|---|
| id | name |
| 1 | Metallum |
| 2 | Classical Guys |
| 3 | Mirin |
The green columns contain the main identifier of each table. The technical term for main identifier is primary key. Primary key columns uniquely identify rows; their values cannot be repeated.
The pink column fav_band_id refers to an id column in a different table. Technically, it's called a foreign key.
Most JOIN queries use an ON condition specifying that the primary key of one table should equal the foreign key of another table. But nothing stops us from joining tables based on different columns. Let's see an example.
Take a look at the tab. You'll see two tables: band and student.
Suppose you want to show each student name along with the name of the band that plays their favorite genre of music. How can you approach this problem?
Take a look at the template – we've connected the two tables on the type of music. This gives us the desired result. Note that if there are multiple bands matching a particular student's favorite genre of music, then there will be multiple rows listing that student's name alongside the name of a particular band.
Click the button.
