As
per the requirement of the project data, normalization, and table relationships
are two factors that come to our mind when creating the database schema, it
means we minimize the redundancy from a relation or set of relations, and for
the referential integrity between them, we draw the relationship. For the
user-friendly application's interface when we obtain the records from the database sometimes we need to combine the column from one (self-join) or more
tables based on the values of the common columns between the tables. Using
PostgreSQL JOIN we combine the column from two or more tables, based on a
related columns between them.
JOINs available in PostgreSQL are:-
1- INNER JOIN
2- LEFT JOIN
3- RIGHT JOIN
4- FULL OUTER JOIN
5- CROSS JOIN
6- NATURAL JOIN
7- SELF-JOIN
To
understand the concept of the JOIN I am creating the following table and make a
relationship between them.
--
Employee table
CREATE TABLE public.employee
(
empid serial NOT NULL,-- primary key
fname text,
lname text,
username text,
password text,
contact text,
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL,
managerid bigint,
age integer,
CONSTRAINT employee_primarykey PRIMARY
KEY (empid),
CONSTRAINT "uk_UserName" UNIQUE
(username),
CONSTRAINT chk_employee_age CHECK (age > 18)
);
|
--
Address of employee
CREATE TABLE public.address
(
id serial NOT NULL,
empid integer NOT NULL,-- reference of
employee
address1 text,
address2 text,
city text,
state text,
country text,
CONSTRAINT pk_address_id PRIMARY
KEY (id)
);
|
1- INNER JOIN
INNER
JOIN is used massively in PostgreSQL query statements, INNER JOIN only displays
the rows that match the two joined tables on a particular column. To understand
the INNER JOIN, please create the tables described above. EMPLOYEE and ADDRESS
are the two tables that have a relation on id and empid column, EMPLOYEE table
has a primary key on column id and ADDRESS has the foreign key of EMPLOYEE
table on empid. Suppose we want to display the data of employees with their
address, then we will write a PostgreSQL query statement with INNER JOIN. If we
are using INNER JOIN in the statements of PostgreSQL, it means that the query
will only return employee records that have address data in the ADDRESS table.
Syntax of PostgreSQL INNER JOIN
SELECT
t1.column1,t1.column2,t2.column1,....
FROM
table1 t1
INNER
JOIN table2 t2
ON
t1.matching_column = t2.matching_column;
|
Example of INNER JOIN
For
example, we inserted some records in EMPLOYEE and ADDRESS tables.
EMPLOYEE
table containing the following records
The table ADDRESS is containing the following records
Now,
execute the following PostgreSQL statement
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
INNER JOIN address a
ON e.empid=a.empid
|
Result:
table1:
EMPLOYEE table
table2:
ADDRESS table
matching_column:
e.empid and a.empid
Here
e and a are the alias of EMPLOYEE and ADDRESS table respectively.
|
2- LEFT JOIN
If you looked at
the INNER JOIN result there are 4th row missing of EMPLOYEE table, which is
because of INNER JOIN statement displays the data when data matched in both
tables (EMPLOYEE and ADDRESS) on the particular columns(e.empid=a.empid). The
ADDRESS table doesn't have any record for the id = 4 (Employee) that's why this
record has been missed in the result-set of the INNER JOIN statement. But this
is the wrong result set because it may be Admin forgot to update the address of
the employee, but the employee other data (exists in EMPLOYEE table) should be
displayed. We can resolve this issue using PostgreSQL LEFT JOIN.
If you want to
select the row of EMPLOYEE table which may or may not have corresponding rows
in the ADDRESS table, you should use the LEFT JOIN clause. In case, there is no
matching row in the ADDRESS table, the values of the columns in the ADDRESS table is substituted by the NULL values.
Syntax of PostgreSQL LEFT JOIN
SELECT
t1.column1,t1.column2,t2.column1,....
FROM
table1 t1
LEFT
JOIN table2 t2
ON
t1.matching_column = t2.matching_column;
|
Example of LEFT JOIN
Execute
the following PostgreSQL statement
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
LEFT JOIN address a
ON e.empid=a.empid
|
Result:
table1:
EMPLOYEE table
table2:
ADDRESS table
matching_column:
e.empid and a.empid
Here
e and a are the alias of EMPLOYEE and ADDRESS table respectively.
|
You
can see the result-set of PostgreSQL LEFT JOIN query for the employee Naina, we
don't have the address records that's why the ADDRESS table's column value
substituted by NULL.
3- RIGHT JOIN
RIGHT
JOIN is the same as LEFT JOIN, in the above (LEFT JOIN query) if the ADDRESS
table has a row which but it is not corresponding to the EMPLOYEE table. In
case, there is no matching row in the EMPLOYEE table, the values of the columns
in the EMPLOYEE table are substituted by the NULL values.
Example of RIGHT JOIN
Execute
the following PostgreSQL statement
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
RIGHT JOIN address a
ON e.empid=a.empid
|
Result:
table1:
EMPLOYEE table
table2:
ADDRESS table
matching_column:
e.empid and a.empid
Here
e and a are the alias of EMPLOYEE and ADDRESS table respectively.
|
4- FULL OUTER JOIN
If
we are using PostgreSQL FULL OUTER JOIN then it will return result-set by
combining the result-set of INNER JOIN, LEFT JOIN and RIGHT JOIN. We have to
tables(EMPLOYEE and ADDRESS table as above), and if we applied the FULL OUTER
JOIN on these two tables, for the
matching rows, it will return a single row included in the result set that
contains columns populated from both joined tables. If the rows in the joined
table do not match, the full outer join sets NULL values for every column of
the table that lacks a matching row.
Syntax of PostgreSQL FULL OUTER
SELECT
t1.column1,t1.column2,t2.column1,....
FROM
table1 t1
FULL
OUTER JOIN table2 t2
ON
t1.matching_column = t2.matching_column;
|
Example of PostgreSQL FULL OUTER JOIN
Execute
the following PostgreSQL statement
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
FULL OUTER JOIN address a
ON e.empid=a.empid
|
Result:
table1:
EMPLOYEE table
table2:
ADDRESS table
matching_column:
e.empid and a.empid
Here
e and a are the alias of EMPLOYEE and ADDRESS table respectively.
|
5- CROSS JOIN
Joins
between tables without conditions produce the Cartesian product called cross
join. The size of the cartesian product result set is the number of rows in the
first table multiplied by the number of rows in the second table.
For
example, suppose we have tables A and B and Table A have n records and table B
have m record if we write a PostgreSQL statement of CROSS JOIN for table A and
table B its result-set will return the n*m rows. When I wrote about cross
joining for SQL server, a lot of people asked me for a live example.
Let's
explain the PostgreSQL CROSS JOIN with a live example. First of all, we will
create two tables, first is PRODUCT and second is SUB PRODUCT, run the below
query.
--Create
product table
CREATE TABLE product
(
id serial not null,
product_name
text,
cost numeric,
CONSTRAINT
pk_product_id PRIMARY
KEY (id)
);
--Create
sub_product table
CREATE TABLE sub_product
(
sub_id serial,
sub_product_name
text,
cost numeric,
CONSTRAINT
pk_sub_product_id PRIMARY
KEY (sub_id)
);
|
To insert some
records run the following query.
--Insert
some records into the product table
INSERT INTO product(product_name,cost)
VALUES('DK Pizza',400);
INSERT INTO product(product_name,cost)
VALUES('DK Burger',200);
INSERT INTO product(product_name,cost)
VALUES('DK Pizza Puff',150);
--Insert
some records into sub_product table
INSERT INTO sub_product(sub_product_name,cost)
VALUES('Soft Drink',100);
INSERT INTO sub_product(sub_product_name,cost)
VALUES('Garlic Bread',80);
|
Now,
if we are selling this product online then we have to display user-friendly
data so that customers can get the details of the combined product and its
total cost. It makes easy to select their respective choice.
Example of PostgreSQL CROSS JOIN
Execute
the following PostgreSQL statement
SELECT p.product_name,sp.sub_product_name,(p.cost+sp.cost)asTotalCost
FROM product p
CROSS JOIN sub_product sp
|
Result:
6- NATURAL JOIN
NATURAL
JOIN creates an implicit join based on the same column names in the joined
table, for example, you can see the example table (EMPLOYEE and ADDRESS table),
and both tables have the column empid. It means we do not need to write
"ON e.empid=a.empid" statement part in the query. NATURAL JOIN can be
INNER JOIN, LEFT JOIN, and RIGHT JOIN by default it works like INNER JOIN.
Syntax of NATURAL JOIN
SELECT
t1.column1,t1.column2,t2.column1,....
FROM
table1 t1
NATURAL
< INNER, LEFT, RIGHT>JOIN table2 t2
|
After NATURAL keyword we have to specify one of these
<INNER, LEFT, RIGHT>, by default it works like an INNER JOIN.
Example of PostgreSQL NATURAL JOIN
For
example, I have considered the table EMPLOYEE and ADDRESS, Both have the common
column name (empid).
a) Default NATURAL Joins
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
NATURAL JOIN address a
|
Result:
Note:
By default, it behaves like INNER JOIN
b) With LEFT keyword
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
NATURAL LEFT JOIN address a
|
Result:
c) With RIGHT keyword
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
NATURAL RIGHT JOIN address a
|
Result:
b) With FULL OUTER keyword
SELECT e.fname,e.lname,e.username,e.contact,a.address1,a.address2,a.city,a.state,a.country
FROM employee e
NATURAL FULL OUTER JOIN address a
|
Result:
7- SELF-JOIN
A
self-join is the PostgreSQL statement in which a table is joined to itself, it
is useful for comparing values in a column of rows within the same table.
Syntax of PostgreSQL SELF-JOIN
SELECT
Col_1, Col_2...Col_N
FROM
my_table a
INNER
JOIN my_table b ON a.Col_1=b.Col_2;
|
Example of PostgreSQL SELF-JOIN
In
the above EMPLOYEE table, I want to get the corresponding reporting managers of
employees, then I will use the PostgreSQL SELF-JOIN
Execute
the following query to get an accurate result.
SELECT a.fname,a.lname, (b.fname ||' '|| b.lname) managername
FROM employee a
LEFT JOIN employee b
ON b.empid=a.managerid
|
Result: