PostgreSQL: Inner Join

The INNER JOIN query is used to retrieve the matching records from two or more tables based on the specified condition. PostgreSQL follows the SQL standards for inner join queries.

Here is a diagram that represents INNER JOIN.

SELECT <table_1.column_name(s)>, <table_2.column_name(s)>
FROM <table_1>
INNER JOIN <table_2>
    ON <table_1.column_name> = <table_2.column_name>;

As per the above syntax, we have table_1 and table_2 and they have some matching columns between them. This way, we can retrieve data from both tables based on matching conditions specified.

Let's use the following Employee and Department tables to demonstrate inner joins.

Notice that the Department table is parent table with dept_id as a primary key. The Employee table is child table that has dept_id as foreign key referencing dept_id column of the Department table.

Now let's join both tables, first table (Employee) with a second table (Department) by matching values of the dept_id column. Please note we are using table alias here, referring Employee table as emp and Department table as dept.

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp INNER JOIN Department dept
ON emp.dept_id = dept.dept_id;

Here, the INNER JOIN compares the value in dept_id column of every row of the Employee table with the value of dept_id column of every row of the Department table. It will show only rows from both tables that have matching values. Hence it does not show emp_id = 6, who does not belong to any department.

From the resultset, we can select any column from Employee or Department table in the SELECT clause. As you can see, in the above query we selected emp_id, first_name, and last_name from the Employee table and dept_id and dept_name from the Department table.

If both the tables have same column name used for joining in ON clause, like in our case dept_id is joining column from Employee and Department tables, then you can use the USING syntax like this

SELECT emp.emp_id, emp.first_name, emp.last_name, dept.dept_id, dept.dept_name 
FROM Employee emp INNER JOIN Department dept
USING (dept_id);