Do you understand the difference between (inner) join and left join? Knowing the differences can help
left join selects all rows from the left and table and the matching records from the right table
inner join select all rows which match from both tables
Translating to the exercise at hand: left join would give all classes, even the classes with no students, while inner join while only give the classes with students (at least one student has to be in the class for the row to be returned by the query)
It’s inconvenient when you enter a simple query to see what’s on the tables (if they don’t automatically appear in the viewer), follow that with what the instructions request, are returned an error saying that you didn’t follow the instructions, take 30 minutes trying to figure out what you did wrong without using the hint, only to realize that you were correct on the first attempt yet was told you were incorrect because you still had the initial query up.
You’d think that the system wouuld be optimized in a way that acknowledges your correct input while ignoring irrelevant (albeit correctly typed) queries that came before it.
In the example, Why are we using classes.id = students.class_id?
Instead of classes.class_id = students.class_id ? Is this because class_id in the students table is a foreign key and has to be more descriptive?
Although a common practice, based on position is not a good way to determining primary key. Simple connect to the database and run a command to get the primary key or use GUI tool, they often can also display which column is marked as primary
Hello, i have a question,
the lesson’s description says that:
Primary keys have a few requirements:
None of the values can be NULL.
and
Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
But i’ve tried adding some code here and have a different result for this description.
I added and had:
INSERT INTO students (id, class_id)
VALUES (NULL, 999);
INSERT INTO classes (id)
VALUES (999), (999);
SELECT *
FROM students
JOIN classes
ON students.class_id = classes.id;
Probably not strictly related to what was being discussed … but I feel this is a useful add for anyone who might be a better visual learner here’s a really simple primer on Primary and Foreign key … This guy does amazing math explanations too !!!
Yes, this is definitely a useful video so thanks for posting, @ngwolfhare.
I was actually looking for a video to better explain this process as I’m having a bit of challenge trying to relate (contextualize) the concepts I’m learning in the SQL track, and this video provides some clarity and context here.
Sometimes text just doesn’t do it justice … Glad wasn’t alone it was also after being a little bit of a YouTube coding tutorial nerd it was certainly the best explained.
First they told us, that primary keys in both tables are called “id” but then when I try to join it this way I get an error. I checked the solution and now it tells me that the primary key in students is called class_id. What the heck, why is it so inconsistent? Why does it suddenly change names?
) below!
) to up-vote the contribution!
it was also after being a little bit of a YouTube coding tutorial nerd it was certainly the best explained.