SQL INNER JOIN Explained with Simple Examples

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.

 

SQL JOINS

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 :

• Table1 represents the First Table
• Table2 represents the Second Table
• join_predicate represent 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 >, <, >=, <=, =, !=, LIKE, BETWEEN, etc and Logical Operators are AND, OR, and 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.

1. The students table is used to store the records about the students.
2. The courses table is used to store the information about the courses.
3. A teacher table is used to store the information about teachers.
4. 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

The Teacher Table

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

• Students is the Table1
• Courses is the Table2
• Students.ID = Courses.SID is the join_predicate
• SELECT, FROM, INNER JOIN, and ON are the keyword of SQL

 

OUTPUT

Table 1

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 Courses.SID filed.
• INNER JOIN clause that this will find only match values in both Students.ID and must be equal to Courses.SID field.
• 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;

OUTPUT

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;

OUTPUT

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"));

OUTPUT 

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 (AND, OR, NOT, ALL, ANY, LIKE, 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"));

OUTPUT

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;

OUTPUT

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;

OUTPUT

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.

 

Summary

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.

 

Further Readings

SQL JOINS