PostgreSQL JOINS

JOIN

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Let's look at a selection from the products table:

Then, look at a selection from the categories table:

Notice that the category_id column in the products table refers to the category_id in the categories table. The relationship between the two tables above is the category_id column.

Then, we can create the following SQL statement (with a JOIN), that selects records that have matching values in both tables:

Example

Join products to categories using the category_id column:

SELECT product_id, product_name, category_name
FROM products
INNER JOIN categories ON products.category_id = categories.category_id

Result :

Different Types of Joins

Here are the different types of the Joins in PostgreSQL:

INNER JOIN: Returns records that have matching values in both tables
LEFT JOIN: Returns all records from the left table, and the matched records from the right table
RIGHT JOIN: Returns all records from the right table, and the matched records from the left table
FULL JOIN: Returns all records when there is a match in either left or right table
CROSS JOIN: Returns the Cartesian product of two or more tables (combines every row from the first table with every row from the second table)

create table products
(product_id int,
product_name varchar(25),
category_id int);

insert into products values
(33,'apple',4),
(34,'carrot',2),
(35,'mango',4),
(36,'goodDay',1),(37,'laddu',3);


create table categories(
category_id int,
category_name  varchar(25));

insert into categories values
(1,'biscuit'),
(2,'veggies'),
(3,'sweet'),
(4,'fruits');

select product_id,product_name , category_name 
from products inner join  categories on products.category_id=categories.category_id

*Output *: