Learn SQL

Context for JOINS

Currently in the students table we have two types of information - students information and course information.

Let’s say we want to add more infoomation about the courses themselves, like their abbreviated names. This abbreviated name is about the course, and not about the students themselves. It’s better to keep this information in a separate table called “courses”. In this way “students” table will be about students and the “courses” table will be about “courses”.

Here is our new courses table.

Now we have course information at two places at students tab Now we need to remove “course” column from “students” table.

Here is our new students table where we have removed the course.

Now we have a problem. How do we know which student is taking which course. To solve this problem we need to undertand what is a primary key.

In USA there are 48,110 people with name “John Smith”. Then how do we identify which John Smith we are dealing with when they open bank account. Well each John Smith is given a unique number. In USA it is their social security number. Also the passport number of each John Smith would be different.

Similarly in the database world we might have two students with the name “John Smith”. However the id of these two students would be different. In our students table “id” is the first column.

Coming back to the issue of how do we identify which student is taking which course we can add “courseId” column to the students table and that courseId would contain the primary key of the courses table.

After making that change now our tables look like this and now we can select students name and their courseId.

Notice, that we have a new table courses above which contains id, name and abbreviatedName for a few courses. The course column from students table from earlier is gone. In it’s place, we have a courseId column. This courseId column contains a reference to the course the student is enrolled in, by storing the actual id of the course from the courses table.

For example, in courseId for student Johnson, the courseId is 1. This means Johnson is enrolled in the course JavaScript(id 1).

But now, how do we get a result containing names of all students and the courses they are enrolled in from this new set of tables, similar to query at the top of this page?

That’s what we will explore in the next few chapters.