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 column 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 display 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
which 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
ADDRESS table 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, that is because of INNER
JOIN statement display 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 wrong result
set because of 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 are
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 ADDRESS table's column value substituted by NULL.
3- RIGHT JOIN
RIGHT JOIN is the same as LEFT JOIN, in above (LEFT JOIN
query) if ADDRESS table has a row which but it is not corresponding to 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 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 the 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:
