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:

product_id	product_name	category_id
33	Geitost	4
34	Sasquatch Ale	1
35	Steeleye Stout	1
36	Inlagd Sill	8

Then, look at a selection from the categories table:

category_id	category_name
1	Beverages
2	Condiments
3	Confections
4	Dairy Products

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 :

roduct_id	product_name	category_name
33	Geitost	Dairy Products
34	Sasquatch Ale	Beverages
35	Steeleye Stout	Beverages
36	Inlagd Sill	Seafood

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 *: