SQL Tutorial: Self-ish joins, just in CASE

Want to learn more? Take the full course at https://learn.datacamp.com/courses/joining-data-in-postgresql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- You'll now dive into inner joins where a table is joined with itself. Sounds a little selfish, doesn't it? These types of joins, as you may have guessed, are called self joins. You'll also explore how to slice a numerical field into categories using the CASE command. Joining a table to itself may seem like a bit of a crazy, strange thing to ever want to do. Self-joins are used to compare values in a field to other values of the same field from within the same table. Let's further explore this with an example. Recall the `prime_ministers` table from earlier. What if you wanted to create a new table showing countries that are in the same continent matched as pairs? Let's explore a chunk of INNER JOIN code using the `prime_ministers` table. You might want to pause the video and think about what the resulting table will look like. The `country` column is selected twice as well as `continent`. The `prime_ministers` table is on both the left and the right. The vital step here is setting the key columns by which we match the table to itself. For each country, we will have a match if the country in the "right table" (that is also `prime_ministers`) is in the same continent. Lastly, since the results of this query are more than can fit on the slide, you'll only see the first 14 records. See how we have exactly this in the result! It's a pairing of each country with every other country in its same continent. But do you see a problem here? We don't want to list the country with itself after all. In the next slide, you'll see one way to do this. Pause to think about how to get around this before continuing. We don't want to include rows where the country is the same in the `country1` and `country2` fields. The `AND` clause can check that multip