_edited_edited.png)
This blog will explore why we use joins and different types of joins.
Joins are used to combine columns from different tables based on a common column.
Joins combine rows from one table with rows from a second table, using an expression specifying which rows are to be paired.
Queries that access multiple tables at a time are called join queries.
Tables are related to each other using Primary and Foreign keys.
Following are the different types of joins in PostgreSQL:
Inner Join – Returns matching data from both tables.
Full outer join – Returns matching data and unmatched data from both tables.
Left outer join or Left join – Returns matching data from both the tables and all the unmatched data from the left table.
Right outer join or Right join – Returns matching data from both the tables and all the unmatched data from the right table.
To understand the different joins, let's create two tables named product_a and sales_b.
We will start by creating a database named product_sales.
1. Right-click on Databases > Create >Database.
2. Provide a name for the Database and click on Save.
3. A database named product_sales is created.
Next, let's create the tables product_a and sales_b
Right-click on product_sales > Query Tool, to open the query tool.
To create table product_a, execute the following query:
CREATE TABLE product_a(
prod_id INT PRIMARY KEY NOT NULL,
prod_name VARCHAR(150) NOT NULL
);
We can see that the query was executed successfully.
Following are the steps to check whether the table was created:
1. Right-click on the product_ sales database and click on Refresh.
2. Then expand Schemas>Tables>product_a.
3. Execute the following query:
SELECT * FROM product_a;
To create table sales_b, execute the following query:
CREATE TABLE sales_b(
sales_id INT PRIMARY KEY NOT NULL UNIQUE,
amount INT NOT NULL,
prod_name VARCHAR(150) NOT NULL
);
Execute the following query to check table sales_b:
SELECT * FROM sales_b;
Next, we will insert some data into the above two tables as follows:
Inserting data into table product_a:
INSERT INTO product_a(prod_id,prod_name)
VALUES
(1,'ipad'),
(2,'iphone'),
(3,'macbook'),
(4,'airpod'),
(6,'fitbit'),
(7,'applewatch');
Executing the following query to view table product_a:
SELECT * FROM product_a;
Now, let’s insert data into the table sales_b as follows:
INSERT INTO sales_b(sales_id,amount,prod_name)
VALUES
(64,500,'ipad'),
(74,900,'macbook'),
(88,1100,'iphone'),
(95,200,'fitbit'),
(28,300,'ipod'),
(31,40,'charger');
Executing the following query to view table sales_b:
SELECT * FROM sales_b;
Inner Join
An Inner join will return a result table with records that match in both tables.
The syntax for Inner join is as follows:
SELECT columns FROM tableA
INNER JOIN tableB
ON tableA.column_match = tableB.coulmn_match;
Here column_match represents the common column in both tables.
The inner join query compares each row of tableA with each row of tableB.
If the value in the tableA column matches with the value in the tableB column then the inner join creates a new row in the result set with matched data from tableA and matched data from tableB.
Now let’s see how Inner join works on the product_a table and sales_b tables.
SELECT prod_id,product_a.prod_name,amount FROM product_a
INNER JOIN sales_b
ON product_a.prod_name = sales_b.prod_name;
Here we perform an inner join between the product_a table and sales_b table.
We can notice that the prod_name column is common in both tables.
Hence, the inner join between both tables will be performed based on the values from column prod_name.
We can see that ipad,iphone,macbook, and fitbit are the values that are matching in both tables. The rows in both tables that have matching prod_name data will be returned in the result set. The result set of this inner join will be as follows:
Full outer join
A full outer join returns all the matched and unmatched rows from the joined tables.
The syntax for Full outer join is as follows:
SELECT columns FROM tableA
FULL OUTER JOIN tableB
ON tableA.column_match = tableB.coulmn_match;
Here column_match represents the common column in both tables.
Full outer join grabs records from both the tables, whether it is present in one table or present in both tables.
For tables product_a and sales_b, the following is the query for full outer join:
SELECT *
FROM product_a
FULL OUTER JOIN sales_b
ON product_a.prod_name = sales_b.prod_name;
When we compare the common column (prod_name ) in both the tables, we see that the records airpod and applewatch in the product_a.prod_name (ie prod_name column in table product_a) column don’t have a match in sales_b.prod_name(ie prod_name column in table sales_b).
Also, the records ipod and charger in sales_b.prod_name doesn’t have match in product_a.prod_name.
product_a Full outer join on sales_b returns all the matching records between both tables and also returns unmatched records. When the record doesn’t find a match in the other table then the null value will fill in that position in the result table.
Left outer join or Left join
A left outer join will return all the records from the left table, if there are unmatched records from the right table then a null value is returned in the result table.
The syntax for the Left outer join is as follows:
SELECT columns FROM tableA
LEFT OUTER JOIN tableB
ON tableA.column_match = tableB.coulmn_match;
Here column_match represents the common column in both tables.
For tables product_a and sales_b, the following is the query for left outer join:
SELECT *
FROM product_a
LEFT OUTER JOIN sales_b
ON product_a.prod_name = sales_b.prod_name;
Here all the records from the product_a table will be returned.
But from the sales_b table, only those records which have a match in the product_a table will be returned in the result table. Those records which don’t match with the prod_name records in the product_a table will be filled in by a null value. So the records which are exclusive to the sales_b table will have a null value in the result set.
Right outer join or Right join
A right outer join is the opposite of a left join.
The right join will return all the records from the right table, if there are unmatched records from the left table then a null value is returned in the result table.
The syntax for the Right outer join is as follows:
SELECT columns FROM tableA
RIGHT OUTER JOIN tableB
ON tableA.column_match = tableB.coulmn_match;
Here column_match represents the common column in both tables.
For tables product_a and sales_b, the following is the query for the right outer join:
SELECT *
FROM product_a
RIGHT OUTER JOIN sales_b
ON product_a.prod_name = sales_b.prod_name;
Here all the records from the sales_b table will be returned.
But from the product_a table, only those records which have a match in the sales_b table will be returned in the result table. Those records which don’t match with the prod_name records in the sales_b table will be filled in by a null value. So, the records which are exclusive to the product_a table will have a null value in the result.
Hence to conclude, we have seen in this blog that joins are useful in joining multiple tables. We have also explored different types of joins like inner, full outer, left outer, and right outer.