SQL Outer Join - improper execution

Question

I am working with learning SQL, I have taken the basics course on pluralsight, and now I am using MySQL through Treehouse, with dummy databases they've set up, through the MySQL server. Once my training is complete I will be using SQLServer daily at work.

I ran into a two-part challenge yesterday that I had some trouble with. The first question in the challenge was:

"We have a 'movies' table with a 'title' and 'genre_id' column and a 'genres' table which has an 'id' and 'name' column. Use an INNER JOIN to join the 'movies' and 'genres' tables together only selecting the movie 'title' first and the genre 'name' second."

Understanding how to properly set up JOINS has been a little confusing for me, because the concepts seem simple but like in cooking, execution is everything ---and I'm doing it wrong. I was able to figure this one out after some trial and error, work, and rewatching the Treehouse explanation a few times; here is how I solved the first question, with a Treehouse-accepted answer:

SELECT movies.title, genres.name FROM movies INNER JOIN genres ON movies.genre_id = genres.id;

--BUT--

The next question of the challenge I have not been so successful with, and I'm not sure where I'm going wrong. I would really like to get better with JOINS, and picking the brains of all you smartypantses is the best way I can think of to get an explanation for this specific (and I'm sure, pitifully simple for you guys) problem. Thanks for your help, here's where I'm stumped:

"Like before, bring back the movie 'title' and genre 'name' but use the correct OUTER JOIN to bring back all movies, regardless of whether the 'genre_id' is set or not."

This is the closest (?) solution that I've come up with, but I'm clearly doing something (maybe a lot) wrong here:

SELECT movies.title, genres.name FROM movies LEFT OUTER JOIN genres ON genres.id;

I had initially tried this (below) but when it didn't work, I decided to cut out the last portion of the statement, since it's mentioned in the requirement criteria that I need a dataset that doesn't care if genre_id is set in the movies table or not:

SELECT movies.title, genres.name FROM movies LEFT OUTER JOIN genres ON movies.genre_id = genres.id;

I know this is total noob stuff, but like I said, I'm learning, and the questions I researched on Stack and on the Internet at large were not necessarily geared for the same problem. I am very grateful to have your expertise and help to draw on. Thank you for taking the time to read this and help out if you choose to do so!

Answer 1

Your solution is correct:

SELECT movies.title, genres.name 
    FROM movies 
        LEFT OUTER JOIN genres ON movies.genre_id = genres.id

This is my interpretation:

When you tell "Left join" or "left outer join", in fact,

it's not that "You don't care if genre_id is set in the movies table or not",

but "You want all genres of each movie to be shown, however, you don't care if genre_id is not set in the movies table for some records; just show the movie in these cases [and show 'genre = NULL' for those records]"

generally, in "left join", you want:

all the records of the left table, with their corresponding records in the other table, if any. Otherwise with NULL.

In your example, these two sets of records will be shown:

1- All the movies which have been set to a genre
(give movie.title, Genres.name)

2- All other movies [which do not have a genre, i.e., genre_id = NULL]
(give movie.title, NULL)

Example (with left join):

Title, Genre
--------------
Movie1, Comedy
Movie1, Dramma
Movie1, Family
Movie2, NULL
Movie3, Comedy
Movie3, Dramma
Movie4, Comedy
Movie5, NULL

Example (with inner join):

Title, Genre
--------------
Movie1, Comedy
Movie1, Dramma
Movie1, Family
Movie3, Comedy
Movie3, Dramma
Movie4, Comedy

Answer 2

Your'e specific question was already answered, though:

I'd like to add another perspective about JOIN, that i think will help you understand how to use it in the future (after that, I also recommend you follow this link: SQL JOINS ).

This perspective is from the DB eyes, which is "dumb" and can't guess what you really want it to do for you.

I help it helps and won't confuse you too match:

Lets first understand what a join does (without using any SQL script), and than we'll understand better how to use it.

Say this is a movie list:

1. Armageddon
2. Batman
3. Cinderella

and a list of genres:

1. Action
2. Fantasy
3. Western

When you join both tables, the DB creates a new tables, that for each row in movies table, you'll get all possible rows in genres table, like this:

1. Armageddon <-> Action
2. Armageddon <-> Fantasy
3. Armageddon <-> Western
4. Batman <-> Action
5. Batman <-> Fantasy
6. Batman <-> Western
7. Cinderella <-> Action
8. Cinderella <-> Fantasy
9. Cinderella <-> Western

You can also see that the NEW table row number is 3*3 ([table 1 row number] multiply [table 2 row number]). Can you explain yourself why? If so, lets continue to our second step...

In your DB, you keep track of which movie is which genre (identifying genre by it's id), so lets talk about NEW tables, that look like this and have info about movies genre:

1. 1 - Armageddon - 1
2. 2 - Armageddon - 2
3. 4 - Batman - 1
4. 5 - Batman - 2
5. 6 - Batman - 3
6. 7 - Cinderella - 2

And the genre:

1. 1 - Action
2. 2 - Fantasy
3. 3 - Western

As we've just explained, joining both tables will get you... 18 rows (6*3=18. why? because for each row in movies table, you'll get all possible rows from genres table). I won't write those 18 rows, I hope you get the point...

Each time you call a join (doesn't matter which kind of join: LEFT/RIGHT/OUTER/INNER), the DB creates a new table with all passible options([table 1 row number] multiply [table 2 row number]). Now, you're probably thinking: How does the DB erase the rows I don't want?

1. First, you define an ON condition. You tell your DB: "please mark for me all rows that meet my condition: movies.genre_id = genres.id (But don't drop any unmarked rows yet!!!)".
2. Second, you tell your DB which kind of rows you want to drop (or edit!!!): now comes the JOIN kind, which is a bit tricky.

INNER JOIN is easy to understand- just tell the DB: "drop all rows that don't meet my condition: movies.genre_id = genres.id" (and of course show me the updated table, after you've dropped these rows I don't need).

LEFT/RIGHT JOINs are more complicated. Lets start for example with LEFT JOIN. You're telling your DB: "well, in case a row doesn't match my condition: movies.genre_id = genres.id, mark the RIGHT part of my row (meaning, the columns that represent my 2nd table) as null, AND LEAVE THE ROW. That way, I know you this row in table1, doesn't have a matching row in table2.

In RIGHT JOIN, it's the opposite: you tell the DB, that if your condition isn't met, mark the LEFT side with null.

FULL JOIN tells your DB: "well, from a row that doesn't meet my condition, make 2 rows: 1 that has it's RIGHT part marked as null, and a second that has it's LEFT part marked as null" (this is a bit complicated for understanding for why the hack you'll need that, and you'll hardly need to use FULL JOIN in your first steps, so drop it for now).

In conclusion, my advice for you when you design your JOIN query

1. first, understand what YOU want to get, see illustration in answer: SQL JOINS.
2. Then, comes the part when you need to explain to you DB what it should do:
   • first, tell it which rows it should mark,
   • than, tell it which rows it should drop/edit.