Table of Contents
SQL INNER JOIN
This tutorial will explain the use of SQL INNER JOIN with examples and how one can use it to query data from multiple tables.
The SQL LEFT JOIN is a type of join that returns the rows from both tables that have matching values between them. The SQL INNER JOIN clause works only on the matched columns and selects all rows from both tables as long as a match is found. If both tables do not have any matched records, then no records will be returned in the Inner Join Select Query output.
Before starting the definition and practical examples of an inner join, we need to review SQL joins. SQL Joins are divided into two broad categories which are OUTER JOINS and INNER JOINS. SQL Inner join is the widely used clause among these. SQL joins are used to display combined information from different tables based on matched columns. It helps us to maintain the normalization of the database with lower redundancy and decrease anomalies. Formally we can define SQL join as
“SQL join is used to combine rows from two or more tables based on related attributes”.
Venn Representation of SQL INNER JOIN
Set theory and Venn diagrams can describe the result set and concepts of a relational database like queries, predicates, and Joins. Following Venn Diagram represents the result set of rows that will be returned by INNER JOIN. It is apparent from the diagram(Figure 1) that only matched rows between these tables will be part of the output.
SQL LEFT JOIN Syntax
This is the syntax to be used with SQL LEFT JOIN:
SELECT columns_name(s) FROM Table1 INNER JOIN Table2 ON join_predicate;
In this Syntax, keyword and object representation is as under :
Table1represents the First Table
Table2represents the Second Table
join_predicaterepresent the conditional expression with ON clause to find the Matched Columns from both Table 1 and Table 2
- SELECT, FROM, INNER JOIN, and ON are the keyword of SQL
The conditional expression may consist of a logical and comparison expression. It is made up of comparison and logical operators of SQL. It is to remind you that Comparison Operators are
BETWEEN, etc and Logical Operators are
NOT. It must be kept in mind while writing the
join_predicate that it must contain one column (field) from Table 1 and the second Colum from Table 2 in the following format/syntax.
Table1.Column_name [comparison Operator] Table2.Column_name
SQL INNER JOIN is also called only JOIN, so if we will use only JOIN in Select Statement, it will make no difference in the output result. For example, the equivalent syntax for above mentioned INNER JOIN clause with only JOIN Clause will be as under.
SELECT columns_name(s) FROM Table1 JOIN Table2 ON join_predicate;
Let take a quick example here to see the same output result produced by INNER JOIN and JOIN only. Let Suppose we have two tables the Students and Courses in our demo database.
SQL INNER JOIN Examples
Suppose we have a demo Database with four tables.
- The students table is used to store the records about the students.
- The courses table is used to store the information about the courses.
- A teacher table is used to store the information about teachers.
- The CourseRegistration table is used to store the information about the students and their registered courses with teachers.
The following diagram (Figure 2) shows the Tables structure and relationship among these. You can observe that the Students, the Courses, and the Teacher all these tables have one-to-many with CourseRegistration.
The Students Table
The Courses Table
|301||Data Structures||6 Months||1||3|
The Teacher Table
|T101||Habib Ullah Qamar||Admin block||17|
|T102||Deepak Prasad||CS Block||18|
|T105||Michael Sisper||CS Block||19|
The CourseRegistration Table
SQL INNER JOIN | EXAMPLE 1
Let us see example 1 to join the table of Students with Courses with INNER Join Class and with only JOIN clause. We will observe the output result table whether the result of the INNER JOIN and only the JOIN keyword are the same. As it has been mentioned that you can use either INNER JOIN or only JOIN.
SELECT Students.*, Courses.* FROM Students INNER JOIN Courses ON Students.ID = Courses.SID;
Syntax / Code Comparison
Studentsis the Table1
Coursesis the Table2
Students.ID = Courses.SIDis the join_predicate
- SELECT, FROM, INNER JOIN, and ON are the keyword of SQL
Observe the output of the first INNER JOIN Example shown by Table 1, You can see only the four rows are present in the output. You can see above the Students Table that it has five students. The Courses Table has a total of four records. In the output, you can see that only those records are taken which have similar values in the ID field of the Students Table and the SID field of the Courses Table. INNER JOIN defines. Here is the analysis of the rows from both tables.
- ON clause of this SELECT query states that Students.ID must be equal to
- INNER JOIN clause that this will find only match values in both Students.ID and must be equal to
- Stephen, Lucas, and Brendon are those students from the Students Table who have matched values in the Courses table.
- Computing, Data structure, programming, and OOP are the courses to which Stephen, Lucas, and Brendon have registered.
- All other 2 students from the Students table do not have any match in the SID column of the Courses table.
SQL JOIN | EXAMPLE 2
This is similar to the above SELECT statement, but only the JOIN clause is being used in place of INNER JOIN to observe that similarity between these.
SELECT Students.*, Courses.* FROM Students JOIN Courses ON Students.ID = Courses.SID;
The only JOIN clause has produced the 100% same output generated in this SELECT Query. Hence it is clear that you can use either use INNER JOIN or JOIN clause for similar results.
SQL JOIN | EXAMPLE 3 | Courses and CourseRegistration
In this example, we will join the Courses and CourseRegistration tables. We will try to find the courses which have been registered by the Students. As per the above Figure-2 representing the structure of our DEMO Database, Course and CourseRegistration Tables has a one-to-many relationship between them. CID is the Primary key of the Courses Table and it has been used as Foreign Key in CourseRegistration Table as well to create this relationship.
NOTE: We will use INNER JOIN Clause, as we have already observed that only JOIN does the same.
SELECT Courses.CID, Courses.CourseName, CourseRegistration.StudentID, CourseRegistration.Semester, Courses.CR, Courses.Duration, CourseRegistration.TeacherID, CourseRegistration.CourseID FROM Courses INNER JOIN CourseRegistration ON Courses.CID = CourseRegistration.CourseID;
In this INNER JOIN Example, values of the First Column(CID) and the last Column (CourseID) are important to get a better understanding the working how INNER JOIN has generated this output. First Column(CID) is from Courses Table and the Last Column (CourseID) is from the Courses Registration table. All values of these two Columns are the same, which depicts the working of INNER JOIN to retrieve the rows with exact matches.
- You can see above the Courses Table, information on four courses has been stored.
- CourseRegistration contains a total of 10 rows counting the registration of these courses with different Students and Teachers.
- OUTPUT shown by Table 3 contains 10 rows which means that All four Courses got more than one match in the CourseRegistration.
- More than one Students have registered for these courses.
SQL INNER JOIN | EXAMPLE 4 | Use of Where Clause with INNER JOIN
In this example, we will learn how to use the WHERE Clause with SQL INNER JOIN. We will apply this clause to our previous output shown in Table 3 to see the clear picture.
- The WHERE CLAUSE can also be used with INNER JOIN like it can be in LEFT JOIN in conjunction with ON Clause.
- The WHERE CLAUSE is used to filter the rows in a SELECT Query on single or multiples tables.
- In INENR JOIN, it performs the same filtering function after applying INNER JOIN JOIN.
- The WHERE CLAUSE can be applied on either of the Tables in the in INNER JOIN either Table1 or Table2.
Syntax of WHERE Clause in SQL INNER JOIN
Here is the syntax of how you can use WHERE CLAUSE with SQL INENR JOIN like in SQL LEFT JOIN.
SELECT columns_name(s) FROM Table1 INENR JOIN Table2 ON Join_predicate WHERE_CLAUSE predicate;
SQL WHERE CLAUSE is used to create matches between two tables under some specific circumstances. Here is our example 3 for the use of WHERE Clause.
SELECT Courses.CID, Courses.CourseName, CourseRegistration.StudentID, CourseRegistration.Semester, Courses.CR, Courses.Duration, CourseRegistration.TeacherID, CourseRegistration.CourseID FROM Courses INNER JOIN CourseRegistration ON Courses.CID = CourseRegistration.CourseID WHERE (((CourseRegistration.Semester)="Spring"));
In this WHERE Clause and INNER JOIN Example, the Total number of rows states the difference of the OUTPUT Table 3 with the previous example. Again values of the First Column(CID) is from Courses Table and the Last Column (CourseID) is from the Courses Registration table have an exact match.
- You can see only four rows are in this JOIN on the same tables which had generated 10 rows in the output Table 3 to the previous example.
- CourseRegistration contains a total of 10 rows but the WHERE Clause has filtered this table to only four Rows.
- Now OUTPUT Table 4 contains only these four rows that have been joined by INNER JOIN based on matched values with ON Clause
ON Courses.CID = CourseRegistration.CourseID
Use of Logical Operators in INNER JOIN (ON Clause / WHERE CLAUSE)
You can also use logical operators (
IN, etc.) in the ON clause and in WHERE CLAUSE as well while using SQL INNER JOIN
In the above example, we have used WHERE Clause and have used =(equal to) Operator in SQL INNER JOIN. If you want to use more than one Join_Predicates or WHERE_Predicates, you need to use logical operators. For example, you want to apply two conditions in or Two Conditions in the WHERE Clause.
Let’s take another example on the same tables Courses and CourseRegistration.
Suppose You want to retrieve the records of such courses which are registered in Spring and have a Duration of 3 months. Hence you will need to apply two predicates/conditions here in the WHERE CLAUSE this query. The required SELECT Statment with AND of two WHERE Clauses and INNER JOIN will be as under:
SELECT Courses.CID, Courses.CourseName, CourseRegistration.StudentID, CourseRegistration.Semester, Courses.CR, Courses.Duration, CourseRegistration.TeacherID, CourseRegistration.CourseID FROM Courses INNER JOIN CourseRegistration ON Courses.CID = CourseRegistration.CourseID WHERE (((CourseRegistration.Semester)="Spring") AND ((Courses.Duration)="3 Months"));
Now you can see that only two records are in the OUTPUT shown in Table 5. This is the same query as our previous example. It contains the use of AND logical operator and shows how Logical or Comparison Operators can be applied in WHERE Clause. The same use is also applicable for the ON clause as well
Use of ORDER BY Clause with INNER JOIN
It has been mentioned in every example and in every output, the order of the output rows is taken from the order of Table1 in the SELECT Query. You can use the ORDER BY clause in INNER JOIN as well as WHERE Clause if you want to change the order of the output rows. ORDER By clause can use any of the Column names either from Table 1 or Table 2 in the following way.
This example will generate the same output as Table 4 but CourseID will be ordered in ascending order. The required SELECT statement expressing How to use the ORDER BY clause with SQL INNER JOIN will be as under :
SELECT Courses.CID, Courses.CourseName, CourseRegistration.StudentID, CourseRegistration.Semester, Courses.CR, Courses.Duration, CourseRegistration.TeacherID, CourseRegistration.CourseID FROM Courses INNER JOIN CourseRegistration ON Courses.CID = CourseRegistration.CourseID WHERE (((CourseRegistration.Semester)="Spring")) ORDER BY Courses.CID;
This time output is the same as the output of Table 5, but the order of the rows is different. CID Columns is in Ascending order with the use of the ORDER BY clause. As far as ORDER is concerned.
SQL INNER JOIN | EXAMPLE 5 | 3 Tables
Now it's time to add more tables in INNER JOIN to see the output of the table. In this example, We will use Three Tables i.e. Students, CourseRegistration, and Teacher. In Figure 2, you can see the Tables Structures and relationships among these three tables. Both Students and Teacher tables are making the one-to-many relationship with the CourseRegistration Table. We have already created the third table (Teacher) in our demo database that will be used in this example 5.
Let's try to find the list of students and their registered courses with the teachers. We have six students in the Students Table, Four courses in the Courses table, and a total of 10 courses registered in CourseRegistration. The SELECT Statement with Three Tables using INNER JOIN will be as under:
SELECT Students.ID, Students.StName, Teacher.TeacherName, CourseRegistration.CourseID FROM Teacher INNER JOIN (Students INNER JOIN CourseRegistration ON Students.ID = CourseRegistration.StudentID) ON Teacher.TeacherID = CourseRegistration.TeacherID;
Table 6 shows the output of the SQL INNER JOIN in Three Tables. In three tables, First INNER JOIN is applied on Students and the Courses Tables and secondly the resultant output table si applied with Students. Matches records based on ON clauses mentioned in the above SELECT Statement are retrieved then in the OUTPUT Table.
The SQL INNER JOIN clause allows you to query data from multiple tables. It returns all rows from Table1, Table2, and Table 3 so on with exact matching rows from all tables. In short, the INNER JOIN clause returns all rows from all Tables used in INNER JOIN with matched values only. Like all other SELECT queries, you can use the WHERE clause, ORDER BY clause. Multiple conditions can be logically concatenated in WHERE Clauses and in the ON clause as well.