INNER JOIN - Continued
Let’s notice a few more points in the basic
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
1 student (with id 24) has a
courseId(20) which does not match any
Observe that these students are not included in the final result of the
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
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.