PostgreSQL Joins

This PostgreSQL Tip provides syntax and examples to help users understand how to implement PostgreSQL JOINS (inner and outer).

Data is frequently spread across several tables while dealing with relational databases in order to reduce redundancy and enhance data management. However, merging data from several related tables is typically necessary to obtain significant information. PostgreSQL joins become crucial in this situation.

You can combine rows from two or more tables based on a related column using a variety of join types offered by PostgreSQL. Joins provide a strong and effective method of querying relational data, whether you need to get matching records, include mismatched rows, or compare data between databases.

This article will explain PostgreSQL joins, their significance, and how to use various join types, such as INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN. To help you utilize joins with confidence in real-world PostgreSQL applications, we'll walk you through each join's straightforward syntax, useful examples, and anticipated results.

The problem we have when accessing data from various tables based on logical relationships between them is the goal of JOINs in SQL. Data from database tables is fetched using JOINS, which then represents the resulting dataset as a different table.

PostgreSQL Joins

Data from two or more tables can be combined in a database using the PostgreSQL joins clause. The following list of PostgreSQL types:

1. The cross join
2. The inner join
3. The left outer join
4. The right outer join
5. The full outer join

Create Initial Tables

Let's take into account the two tables for the joins.

1. Company Table
2. Department Table

Company Table

Let’s make the Company Table

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           CHAR(20),
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         INT,
   JOIN_DATE      DATE
);

Step 1: Create the Table

CREATE TABLE COMPANY

Creates a new table named COMPANY.

Step 2: Define the Columns

1.

sql ID INT PRIMARY KEY NOT NULL

Unique employee ID; cannot be empty.
2.

sql NAME CHAR(20)

Stores the employee's name (up to 20 characters).
3.

sql AGE INT NOT NULL

Stores the employee's age; cannot be empty.
4.

sql ADDRESS CHAR(50)

Stores the employee's address.
5.

sql SALARY INT

Stores the employee's salary.
6.

sql JOIN_DATE DATE

Stores the employee's joining date.

Step 3: Result

A COMPANY table is created with six columns to store employee details.

Output-

Inserting data

Let’s insert data into the COMPANY table

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Achyut', 20, 'Gorakhpur', 30500.00,'2022-10-29');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (2, 'Neha', 22, 'Banaras', 40500.00,'2022-12-15');

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (3, 'Kirti', 26, 'Lucknow', 50500.00,'2022-08-03');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (4, 'Ankit', 27, 'Delhi', 60500.00,'2022-02-19');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (5, 'Ankur', 30, 'Goa', 70500.00,'2022-08-27');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (6, 'Pallavi', 34, 'Jaipur', 80500.00,'2022-04-22');
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (7, 'Radha', 36, 'Bali', 90500.00,'2022-01-26');

After successfully inserting all the data, let’s see the data

select * from company;

Output-

Department Table

Let’s make a Department Table

CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY NOT NULL,
   DEPT CHAR(40),
   EMP_ID INT NOT NULL
);

Step 1: Create the Table

CREATE TABLE DEPARTMENT

Creates a new table named DEPARTMENT.

Step 2: Define the Columns

1.

sql ID INT PRIMARY KEY NOT NULL

Unique department record ID; cannot be empty.
2.

sql DEPT CHAR(40)

Stores the department name (up to 40 characters).
3.

sql EMP_ID INT NOT NULL

Stores the employee ID associated with the department; cannot be empty.

Step 3: Result

A DEPARTMENT table is created with three columns to store department information and the corresponding employee ID.

Output-

Inserting Data

Let’s insert data into the Department table

INSERT INTO Department(ID,DEPT,EMP_ID) VALUES (1,'IT',1);
INSERT INTO Department(ID,DEPT,EMP_ID) VALUES (2,'SALES',2);
INSERT INTO Department(ID,DEPT,EMP_ID) VALUES (3,'SDE',6);

After successfully inserting all the data let’s see the data

select * from Department;

Output-

THE CROSS JOIN

The PostgreSQL Cross Join is used to aggregate all potential results from several tables and to deliver the output, which includes each row from all the chosen tables. The Cartesian join, as it is sometimes known, enables the production of the Cartesian sum of all associated tables.

Every row from one table is combined with every entry from another table using a SQL join technique called a CROSS JOIN. It does not necessitate a matching condition between the tables, in contrast to other joins. The total number of rows in the result is therefore equal to the number of rows in the first table multiplied by the number of rows in the second table. This is known as the Cartesian product.

While you need to generate every possible combination of records—for example, while developing product variations, scheduling combinations, or testing various data scenarios—CROSS JOIN comes in handy. However, it should be used carefully with large tables to prevent performance difficulties because it might generate a very large number of rows.

Syntax

SELECT <COLUMN NAME> FROM TABLE1 CROSS JOIN TABLE2;

Example

A cross-join operation between the company table and the department table will result in any feasible combination, like

Query

SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;

retrieves the EMP_ID, NAME, and DEPT columns by performing a CROSS JOIN between the COMPANY and DEPARTMENT tables. A CROSS JOIN combines every row from the COMPANY table with every row from the DEPARTMENT table, creating all possible combinations of records. Since no join condition is specified, the result is the Cartesian product of both tables.

Output

It will produce the following result.

THE INNER JOIN

The INNER JOIN keyword in PostgreSQL selects all rows from both tables if the criteria is met. INNER JOIN keyword will combine all rows from both tables whose conditions, i.e., the common field's value, are met to produce the result set.
One of the most popular SQL join operations is an INNER JOIN. Based on a matched value in a linked column, it merges rows from two or more tables. Non-matching rows are not included in the result; only records with matching values in both tables are. Working with relational databases requires an understanding of INNER JOIN, which is frequently used to obtain relevant data from several tables.

Syntax

SELECT <Column_name> FROM table1 INNER JOIN table2 ON table1.column=table2.column;

Query

SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID=DEPARTMENT.EMP_ID;

retrieves the EMP_ID, NAME, and DEPT columns by performing an INNER JOIN between the COMPANY and DEPARTMENT tables. The ON COMPANY.ID = DEPARTMENT.EMP_ID condition matches each employee in the COMPANY table with the corresponding department in the DEPARTMENT table. Only the records with matching IDs in both tables are included in the result, while non-matching records are excluded.

Output

It will produce the following result.

THE LEFT OUTER JOIN

All rows from the left table and all rows from the other table that satisfy the join criteria specified in the ON condition are returned using the PostgreSQL LEFT JOIN or Left Outer Join. It will return null if no matching entries were located in the appropriate table.
All records from the left table and the matching records from the right table are returned via a SQL join operation known as an LEFT OUTER JOIN, or LEFT JOIN. The row from the left table is still included in the result with NULL values for the right table's columns if there is no matching record in the right table. When retrieving all data from one dataset while incorporating relevant data from another table whenever it is available, the LEFT OUTER JOIN is frequently utilized.

Syntax

SELECT columns FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;

Query

SELECT columns FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;

uses an LEFT OUTER JOIN between tables 1 and 2 to get data. Based on the given join condition, it retrieves every record from the left table (table 1) and the corresponding records from the right table (table 2). The row from the left table is still included in the result with NULL values for the right table's columns if there is no matching record in the right table.

Output

It will produce the following result.

THE RIGHT OUTER JOIN

To retrieve all rows from the right table and rows from the other table when the join requirement is satisfied, as stated in the ON condition, PostgreSQL's RIGHT JOIN, or Right Outer Join, is utilised. It will return null if no comparable records from the left table were located.
An SQL join technique known as a RIGHT OUTER JOIN (or RIGHT JOIN) yields all of the records from the right table along with the corresponding records from the left table. The result still contains the entry from the right table with NULL values for the left table's columns if there is no matching record in the left table. When retrieving all of the data from the right table and adding relevant data from the left table once a match is found, the RIGHT OUTER JOIN is helpful.

Syntax

SELECT columns FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;

Query

SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID=DEPARTMENT.EMP_ID;

uses a RIGHT OUTER JOIN between the COMPANY and DEPARTMENT tables to retrieve the EMP_ID, NAME, and DEPT columns. Based on the criteria COMPANY.ID = DEPARTMENT.EMP_ID, it retrieves every entry from the DEPARTMENT table and the corresponding entries from the COMPANY table. The result still includes a department with NULL values for the columns from the COMPANY table if there isn't a corresponding employe.

Output

It will produce the following result.

THE FULL OUTER JOIN

An inner join is executed first. Then, a joined row is inserted with null values in columns of table T2 for each record in table T1 that does not meet the join criteria with any row in table T2. Additionally, a joined row with null values in the columns of T1 is added for each row of T2 that does not meet the join criteria with any row in T1.
A SQL join operation that returns every record from both tables is called a FULL OUTER JOIN, or FULL JOIN. The information is merged into a single row when a matching record is found. The entry from either table is still included in the result if there is no match, but the missing columns have NULL values. When you wish to retrieve every record from both tables, regardless of whether a matching value exists, FULL OUTER JOIN is helpful.

Syntax

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

Query

SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID=DEPARTMENT.EMP_ID;

retrieves the EMP_ID, NAME, and DEPT columns by performing a FULL OUTER JOIN between the COMPANY and DEPARTMENT tables. It returns all records from both tables, matching rows based on the condition COMPANY.ID = DEPARTMENT.EMP_ID. If a record in either table has no matching record in the other table, it is still included in the result, with NULL values for the missing columns.

Output

It will produce the following result.

Conclusion

Use an inner join to display just data that matches from both tables. Use an outer join to display all the data from both tables, and a left outer join to display all the data from one table and just the data from the second table that matches the data in the first table.
One of PostgreSQL's most crucial features is SQL joins, which let you obtain and merge relevant data from several tables. Joins allow you to get comprehensive query results and establish meaningful relationships across tables without maintaining redundant data. Writing effective and precise SQL queries requires an understanding of how joins operate.
The various PostgreSQL join types—CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN—were examined in this article. Every join has a distinct function, such as producing every possible combination of records, obtaining only matching rows, or including unmatched data from one or both databases. Selecting the right join type guaranties that your results precisely satisfy the needs of your application and enhances query efficiency.
The various PostgreSQL join types—CROSS JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN—were examined in this article. Every join has a distinct function, such as producing every possible combination of records, obtaining only matching rows, or including unmatched data from one or both databases. Selecting the right join type guaranties that your results precisely satisfy the needs of your application and enhances query efficiency.

Next Steps

• PostgreSQL Trigger
• PostgreSQL Indexes