Until now, we have coupled our SELECT statements with various filtering and sorting techniques to query the student information extensively. As we are operating in a relational-model of data storage and since our data is stored in different tables, we are yet to figure out how our SELECT statements can be fired across multiple tables. In our case, this would help us find out what course or courses a student has registered for, or to find our which course has the most number of students. Following the relational-model of data allows us to store data in a more efficient manner, allows us to independently manipulate the data in different tables, and allows for greater scalability; however, querying the data across multiple tables is going to be difficult when compared to retrieving records from a single table. We will use JOINS to associate multiple tables, to retrieve, update, or delete data.
A SQL JOIN is a virtual entity and is performed at run time, during the execution of the SQL statement. Similar to any other SQL statement, the data would only be available during the query execution and is not implicitly persisted to the disk. A SQL JOIN can be coupled with a SELECT statement to retrieve data from multiple tables. Let us go through the most common JOIN: the INNER JOIN, a join based on the equality comparison on the join-predicate.
Let us look at a few examples that perform SQL INNER JOIN between two or more tables:
In the preceding example, we are joining the students and students_courses tables to retrieve a list of all the students who have registered for a course. This is similar to the SELECT statements that we worked with earlier; a big difference is that we can now add a column that is part of a different table. We use the INNER JOIN clause to build the association between students and the students_courses table where the values for student_id in the students table exist in the students_courses table; this is referred to as the join-predicate. Now let us join all the tables and retrieve the names of the courses for which each student has registered.
Note
In this example, we are creating an alias name for the name column in the courses table. We are using the AS statement to explicitly create a temporary alias to make the column name more intuitive. We can build aliases for tables in a similar manner
In the previous example, we have joined the three tables that are available in our course_registry database and are now able to retrieve the list of courses for which the students have registered. Similar to our previous SELECT statements, let us add a filter criterion to narrow down our search:
In the preceding example, we are filtering the data by student_id and are searching for records with student_id equal to 2. We have discussed the most commonly used form JOIN statement, which is the INNER JOIN or the equi-join. There are other types of JOIN in SQL that are supported by MariaDB such as OUTER JOIN, SELF JOIN, and NATURAL JOIN, we will be skipping these JOIN statements.