Learn SQL

INNER JOIN - Continued

Let’s notice a few more points in the basic JOIN query.

Notice, that the no. of students is 25, but the no. of records in the result of JOIN query are only 22. This is because

  • 2 students (with id 15 and 31) do not have a courseId data.

  • 1 student (with id 24) has a courseId (20) which does not match any id in the courses table.

Observe that these students are not included in the final result of the JOIN query.

This behaviour of the basic JOIN, i.e including strictly those rows from both tables which match the criteria specified in the ON clause, is called INNER JOIN. To assert the same, we can replace JOIN in the above query with INNER JOIN, and observe that the results still remain the same.

Notice that in the headers in the result, both columns show up as name. This is because a JOIN simply returns the name of the columns as headers by default. Since both the columns expected are names, we can use the familiar ALIAS technique to differentiate between the two, and removing the confusion in display.

Finally, the need to specify the column names with table’s name in the SELECT clause, is only enforced in case we want to select a column from a table, which has a name same as a column in one of the other tables in the JOIN list. For example, in the above case, the name column from students conflicts with the name column from courses. Hence the need to specify students.name and courses.name for each of them.

However, if one has to select a column which does not have such a conflict with name from a table, one can get away with simply mentioning the column in the SELECT list without the table name preceding it. For example, if the task was to list the names of students with the abbreviatedName of the courses they are enrolled in, the following query would work fine.

Notice that using just abbreviatedName, instead of courses.abbreviatedName in the SELECT list, works fine in the above query.