Joining Arrays in Postgres Using Inner Join

To filter out unwanted rows and columns and only retrieve records that meet a specific condition, the PostgreSQL Inner Join is utilized. This can be achieved through two syntaxes: the PostgreSQL Inner Join with the USING clause and the PostgreSQL Inner Join with the WHERE clause. To join Table A with Table B, we need to first determine the column list from both tables that will be selected in the SELECT condition.

This section will cover the functioning of PostgreSQL’s inner join, a technique used to
select data
from multiple tables. Additionally, we will explore the application of various tools such as the WHERE clause, USING clause, operators, as well as the ability to join three tables and utilize table-aliasing within PostgreSQL’s inner join.

Could you explain the purpose of the INNER JOIN clause in PostgreSQL?

In a database that follows a relational model, information is typically distributed across multiple tables. Therefore, when we require aggregated data, it is common to need data from multiple tables.

The PostgreSQL Inner Join is a default Join in PostgreSQL that enables the retrieval of only the records that meet the specified condition while excluding other rows and columns. The use of the Inner Join keyword is optional in the query.

The Venn diagram presented illustrates the concept of PostgreSQL’s inner join, which exclusively retrieves the matching records from Table1 and Table2.

PostgreSQL INNER Join

PostgreSQL Inner Join Syntax

The use of Inner Join keyword is limited to SELECT command and it should be placed following the FROM clause.

The following syntaxes provide a clearer description.

SELECT [column_list |*]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column.name; 

The PostgreSQL Inner Join Syntax that incorporates the USING clause.

SELECT [column_list |* ]
FROM table1
INNER JOIN table2
USING (column.name);

Structure of PostgreSQL’s Inner Join along with a WHERE Clause.

SELECT [column_list |*]
FROM table1, table2
WHERE table.column_name=table2.column_name;

The process of joining Table A with Table B will be executed using the following steps.

  • To start with, we’ll establish the list of columns to be selected in the ‘SELECT’ condition from tables 1 and 2.
  • Next, we will establish table 1 in the FROM clause as the foundational table.
  • The second table (table 2) will be explained and its corresponding join condition will be written after the ON keyword in the INNER JOIN clause.
Note: The Join condition returns the similar rows between the tables described in the Inner condition.

Example of PostgreSQL Inner join

To comprehend the functioning of the PostgreSQL inner join, let’s take an instance.

To join two tables by using PostgreSQL INNER JOIN

To accomplish this, two tables will be generated using the CREATE command. These tables will be called “Employee” and “Department”. Subsequently, values will be added by utilizing the INSERT command.

Initially, we shall generate tables for employees and departments through the utilization of the CREATE statement.

create table Employee(
emp_id int primary key, 
emp_fname varchar not null, 
emp_lname varchar not null, 
location varchar(30) );

To generate a table for Departments, use the following command:

Create table department  
(emp_id int primary key,
 dept_id int not null,
 dept_name varchar NOT NULL);

After executing the commands mentioned above, the tables for both the employees and departments were created successfully.

After generating both tables, we can insert values using the INSERT command.

INSERT INTO Employee (emp_id, emp_fname, emp_lname, location)
VALUES
(1, 'John', 'Smith', 'New York'),
(2, 'Mia', 'Clark','Florida'),
(3, 'Noah','Rodriguez','Chicago'),
(4, 'Ava','Gracia','Houston'),
(5,'James','Luther','Los Angeles');

Below, we have a command that inserts values into the table named “department”.

INSERT INTO department (emp_id, dept_id, dept_name)
VALUES
(1, 1, 'ACCOUNTING'),
(2, 2,'SALES'),
(3, 3,'RESEARCH'),
(4, 4,'OPERATIONS'),
(5, 5,'HUMAN RESOURCES');

Following the creation and insertion of values into both the Employee and department tables, the resulting output will be as follows:


Table1: Employee

PostgreSQL INNER Join

Department information is presented in Table 2.

PostgreSQL INNER Join

This query can be utilized to retrieve data from both tables, namely Employee and department.

SELECT emp_fname, emp_lname,location,dept_name
FROM Employee  
INNER JOIN department   
ON Employee.emp_id= department.dept_id;  


Output

Upon executing the aforementioned command, the resulting output will be as follows:


PostgreSQL INNER Join

Working of PostgreSQL inner join
  • To compare the values in the emp_id column of Table1 (Employee) with the dept_id values of every row in Table2 (department), the Inner Join is utilized. Every row in Employee is examined during this process.
  • When the records are alike, the inner Join combines the columns from both tables into a new row and adds it to the output.
  • In case the records are dissimilar, the inner join moves forward to the next row without considering them.

Table-aliasing with PostgreSQL inner Join

Typically, when we intend to join tables, the columns we need to merge will possess analogous names, such as the emp_id attribute.

To prevent errors while referencing columns with similar names from multiple tables in a command, the following syntax should be utilized.

table_name.column_name

To enhance the comprehensibility of the query, we will allocate brief names to the joined tables using table aliases in real-time.

Using
table aliasing
in the command below will yield the same result as above.

SELECT e.emp_id, emp_fname, emp_lname,location,dept_name   
FROM Employee e 
INNER JOIN department d 
ON e.emp_id = d.dept_id;


Output

Upon execution of the aforementioned command, the resulting output will be as follows:

PostgreSQL INNER Join

PostgreSQL inner join with USING Clause

In this section, we’ll explore the functionality of PostgreSQL’s inner join alongside the USING clause. This clause is particularly useful when the column names in both tables are identical, allowing us to retrieve values more easily.

In the example below, we utilize the “USING” clause since both tables contain a corresponding “emp_id” column.

SELECT emp_id, emp_fname,location, dept_name    
FROM Employee  
INNER JOIN department   
USING (emp_id);  


Output

Upon running the aforementioned statement, the resulting output will be as follows:

PostgreSQL INNER Join

PostgreSQL inner join using WHERE clause

The utilization of an inner join with a WHERE clause enables us to retrieve the filtered outcome.

The following example demonstrates the selection of rows from two tables, namely “Employee” and “Department,” based on the condition that the value of “dept_name” is “Sales”.

SELECT emp_fname, dept_id, dept_name, location 
FROM Employee 
INNER JOIN department  
USING (emp_id) WHERE dept_name ='SALES';


Output

Upon performing
successful execution
, the resulting output will be as follows:


PostgreSQL INNER Join

To join three tables using PostgreSQL INNER JOIN

Earlier in this tutorial, we generated the “Employee” and “Department” tables. Presently, we aim to incorporate another table and retrieve its data using Inner join.

To accomplish this task, we need to use the CREATE command to generate an additional table named “Jobs”. The command for creating the table is illustrated below.

CREATE TABLE Jobs(
job_id int primary key, 
job_description varchar not null);

After successful creation of Jobs table, we can utilize INSERT command to add values to it, as demonstrated in the following command:

INSERT INTO Jobs (job_id, job_description)
VALUES (1, 'Training'),
(2, 'Management'),
(3, 'Executive'),
(4, 'Non-Executive');

Following the creation and insertion of values into the Jobs table, the resulting output can be obtained.


Table3: Jobs

PostgreSQL INNER Join

To merge the tables of Employee, Department, and Jobs, we will use two INNER JOIN clauses. The first INNER JOIN clause will be followed by the second INNER JOIN clause.

SELECT emp_id, emp_fname, dept_name, location, job_description
FROM Employee 
INNER JOIN department USING (emp_id) 
INNER JOIN Jobs  
ON department.emp_id = jobs.job_id
ORDER BY emp_id;


Output

After executing the aforementioned query, the resulting output would be as follows.

PostgreSQL INNER Join

Note: To join more than three tables, we can use the above method.

PostgreSQL Inner Join using Operators

Various operators are supported by PostgreSQL for Inner Join, such as equal (=), not equal (!=), greater than (>), less than (<), and so on.

We are utilizing the inequality operator (!=) in the following instance while performing an inner join.

SELECT emp_fname, emp_lname,location,dept_name
FROM Employee  
INNER JOIN department   
ON Employee.emp_id= department.dept_id   
WHERE dept_name != 'SALES';


Output

Upon clicking the
Execute
button, the resulting output will showcase values where the
dept_name
is unequal to
SALES
.

PostgreSQL INNER Join

Overview

Throughout the section on inner joins in PostgreSQL, we have covered the following topics:

  • We utilized the inner join clause of PostgreSQL to link multiple tables, exceeding a count of two.
  • To implement an inner join in PostgreSQL, we utilized multiple techniques such as the WHERE clause, USING clause, Operators, and table-aliasing.

Frequently Asked Questions

Posted in Sql