In this section, we are going to understand the working of PostgreSQL inner join, which is used to select data from many tables. We also learn how to use WHERE clause, USING clause, operators, and join three tables, table-aliasing in PostgreSQL inner join.
What is PostgreSQL INNER JOIN clause?
In a relational database, data is naturally spread in more than one table, and to select aggregate data, we often need to select data from various tables.
The PostgreSQL Inner Join is used to return only those records from the tables, which are equivalent to the defined condition and hides other rows and columns. In PostgreSQL, it as a default Join, therefore it is not compulsory to use the Inner Join keyword with the query.
The following Venn diagram displays the PostgreSQL inner join where we can easily understand that the Inner Join returns only the matching records from Table1 and Table2:
PostgreSQL Inner Join Syntax
The Inner Join keyword is used with the SELECT command and must be written after the FROM clause.
The below syntaxes describe it more clearly:
SELECT [column_list |*]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column.name;Syntax of PostgreSQL Inner Join with USING clause
SELECT [column_list |*]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column.name;Syntax of PostgreSQL inner Join with WHERE Claus:
SELECT [column_list |*]
FROM table1, table2
WHERE table.column_name=table2.column_name;We will follow the below steps to join Table A with Table B:
- Firstly, we will define the column list from both tables (tables 1 and 2), where we want to select data in the SELECT condition.
- Then, we will define the base table, which is table 1 in the FROM clause.
- And lastly, we will describe the second table (table 2) in the INNER JOIN condition, and write the join condition after the ON keyword.
Note: The Join condition returns the similar rows between the tables described in the Inner condition.
Example of PostgreSQL Inner join
Let us see an example to understand how the PostgreSQL inner join works:
To join two tables by using PostgreSQL INNER JOIN
For this, we will create two tables named Employee and department table with the help of the CREATE command and inserting some values by using the INSERT command.
Firstly, we are going to create an Employee and department tables by using the CREATE command:
create table Employee(
emp_id int primary key,
emp_fname varchar not null,
emp_lname varchar not null,
location varchar(30) );The below command is used to create a Department table:
Create table department
(emp_id int primary key,
dept_id int not null,
dept_name varchar NOT NULL);The Employee and department tables have been successfully created after executing the above commands.
Once the both the tables have been generated, we are ready to insert some values into it by using the INSERT command as follows:
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');In the below command, we are inserting the values in the department table:
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');After creating and inserting the values in the Employee and department table, we will get the following output:
Table1: Employee
Table2: department
The below query is used to select records from both tables (Employee and department):
SELECT emp_fname, emp_lname,location,dept_name
FROM Employee
INNER JOIN department
ON Employee.emp_id= department.dept_id;Output
Once we implemented the above command, we will get the below result:
Working of PostgreSQL inner join
- The Inner Join is used to analyze each row in Table1 (Employee) and compare the values in the emp_id column with value in the dept_id of every row in Table2 (department).
- If these records are similar, then the inner Join creates a new row containing columns from both tables and enhances the particular row into the output.
- Or if these records are not similar, then the inner Join disregards them and moves to the next row.
Table-aliasing with PostgreSQL inner Join
Generally, the tables we want to join will have columns with a similar name like the emp_id column.
If we reference columns with a similar name from different tables in a command, the error will have occurred, and to avoid this particular error, we need to use the below syntax.
table_name.column_name In real-time, we will use table aliases to assign the joined tables short names to make the command more understandable.
In the below command, we will use the table aliasing, and it returns the similar outcome 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
Once we implemented the above command, we will get the below output:
PostgreSQL inner join with USING Clause
Here, we will see how the PostgreSQL inner join works with the USING clause because sometimes the name of the columns is similar in both the tables; that?s why we can use the USING clause to get the values.
In the below example, we are using the USING clause as both tables have a similar emp_id column.
SELECT emp_id, emp_fname,location, dept_name
FROM Employee
INNER JOIN department
USING (emp_id);Output
After executing the above statement, we will get the below result:
PostgreSQL inner join using WHERE clause
We can also use the inner join with a WHERE condition. The WHERE clause allows us to return the filter outcome.
In the below example, we will select rows from both tables Employee and department where dept_name is equal to Sales:
SELECT emp_fname, dept_id, dept_name, location
FROM Employee
INNER JOIN department
USING (emp_id) WHERE dept_name ='SALES';Output
After successful execution of the above command, it will give the below output:
To join three tables using PostgreSQL INNER JOIN
In the above section of this tutorial, we have already created two tables as Employee and department. Now, we want to join one more table and get the records from that particular table with the help of the Inner join.
So, for this, we will create one more table as Jobs by using CREATE command as we can see in the following command:
CREATE TABLE Jobs(
job_id int primary key,
job_description varchar not null);Once the Jobs table has been created successfully, we will insert some values into it with the help of INSERT command as we can see in the following command:
INSERT INTO Jobs (job_id, job_description)
VALUES (1, 'Training'),
(2, 'Management'),
(3, 'Executive'),
(4, 'Non-Executive');After creating and inserting the values in Jobs table, we will get the following output:
Table3: Jobs
The below command is used to join the three tables, such as Employee, department, and Jobs. So, we will use the second INNER JOIN clause after the first 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
Once we implemented the above query, we will get the below result:
Note: To join more than three tables, we can use the above method.
PostgreSQL Inner Join using Operators
PostgreSQL allows many operators, which we can use with Inner Join, like equal (=), not equal (!=), greater than (>), less than (<), etc.
In the below example, we are using the not equal (!=) operator with 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
Once we click on the Execute button, we will get the following output that displays those values whose dept_name is not equal to SALES.
Overview
In the PostgreSQL inner join section, we have learned the following topics:
- We used the PostgreSQL inner join clause to select data from two tables or more than two tables.
- We used the PostgreSQL inner join condition with WHERE clause, USING clause, Operators, and table-aliasing.
Leave a Reply