Inner Join

In this tutorial, we will cover the concept of Inner Join in PostgreSQL. An Inner Join is one of the several ways to combine rows from two or more tables based on a related column between them. It allows you to combine data from two or more tables in a single output based on a common column.

Understanding Inner Join

In PostgreSQL, the Inner Join keyword selects records that have matching values in both tables. It is the most common type of join.

Here is a basic syntax for Inner Join:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example Schema

To understand Inner Join more clearly, we will create two tables, 'Students' and 'Courses'.

The 'Students' table:

CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name TEXT NOT NULL,
    Age INT NOT NULL
);

The 'Courses' table:

CREATE TABLE Courses (
    ID INT PRIMARY KEY,
    CourseName TEXT NOT NULL,
    StudentID INT REFERENCES Students(ID)
);

Using Inner Join

Let's say we want to get a list of all students and the courses they're enrolled in. We would use an Inner Join for this:

SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.ID = Courses.StudentID;

This query will return a list of students and their corresponding courses.

The Join Condition

The join condition is the part of the query that specifies how the two tables are related to each other. In the above example, the join condition is Students.ID = Courses.StudentID.

Inner Join with Multiple Tables

You can also perform an Inner Join with more than two tables. Here's an example with three tables, 'Students', 'Courses', and 'Teachers':

SELECT Students.Name, Courses.CourseName, Teachers.Name
FROM ((Students
INNER JOIN Courses ON Students.ID = Courses.StudentID)
INNER JOIN Teachers ON Courses.TeacherID = Teachers.ID);

This query will return a list of students, the courses they're enrolled in, and the teachers who teach those courses.

Conclusion

The Inner Join is a powerful tool in SQL that allows you to combine data from two or more tables based on a related column between them. It's an essential part of SQL and a skill that every data analyst or database administrator should have. Practice using Inner Join with different tables and join conditions to get a good grasp of how it works.

Inner Join

Understanding Inner Join​

Example Schema​

Using Inner Join​

The Join Condition​

Inner Join with Multiple Tables​

Conclusion​