In this tutorial, we learn about the complete join mechanism in PostgreSQL Joins.
PostgreSQL joins are used to combine columns from one (self-join) or more tables based on the values of the common columns between the tables. The common columns are typically the primary key columns of the first table and foreign key columns of the second table.
Table of Contents
PostgreSQL joins
PostgreSQL supports the following join types
- inner join
- left join
- right join
- full outer join
- cross join
- natural join
- self join
Let’s discuss each of these join types in detail
Before learning about join types, let’s create two sample tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
CREATE TABLE android ( id INT PRIMARY KEY, feature character varying (20) NOT NULL ); CREATE TABLE iphone ( id INT PRIMARY KEY, feature character varying (20) NOT NULL ); INSERT INTO android VALUES (1, 'Ginie'), (2, 'touchscreen'), (3, 'android'), (4, 'calling'); INSERT INTO iphone VALUES (1, 'calling'), (2, 'siri'), (3, 'touchscreen'), (4, 'ios'); |
Lets consider android as left table and iphone as right table.
PostgreSQL inner join
Syntax
SELECT
a.id id_android,
a.feature feature_android,
b.id id_iphone,
b.feature feature_iphone
FROM
android a
INNER JOIN iphone b ON a.feature = b.feature;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
testdb=> testdb=> SELECT testdb-> a.id id_android, testdb-> a.feature feature_android, testdb-> b.id id_iphone, testdb-> b.feature feature_iphone testdb-> FROM testdb-> android a testdb-> INNER JOIN iphone b ON a.feature = b.feature; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 2 | touchscreen | 3 | touchscreen 4 | calling | 1 | calling (2 rows) testdb=> |
PS: inner join returns the records that are in common between two tables
PostgreSQL left join
Syntax
SELECT
a.id id_android,
a.feature feature_android,
b.id id_iphone,
b.feature feature_iphone
FROM
android a
LEFT JOIN iphone b ON a.feature = b.feature;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
testdb=> testdb=> SELECT testdb-> a.id id_android, testdb-> a.feature feature_android, testdb-> b.id id_iphone, testdb-> b.feature feature_iphone testdb-> FROM testdb-> android a testdb-> LEFT JOIN iphone b ON a.feature = b.feature; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 1 | Ginie | | 2 | touchscreen | 3 | touchscreen 3 | android | | 4 | calling | 1 | calling (4 rows) testdb=> |
PS: The left join returns a complete set of rows from the left table with the matching rows if available from the right table. If there is no match, the right side will have null values.
We can apply where clause to get only records that are in left table which are not present in right table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
testdb=> SELECT a.id id_android, a.feature feature_android, b.id id_iphone, b.feature feature_iphone FROM android a LEFT JOIN iphone b ON a.feature = b.feature where b.id is NULL; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 1 | Ginie | | 3 | android | | (2 rows) testdb=> |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
testdb=> SELECT a.id id_android, a.feature feature_android, b.id id_iphone, b.feature feature_iphone FROM android a LEFT JOIN iphone b ON a.feature = b.feature where b.id is NULL; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 1 | Ginie | | 3 | android | | (2 rows) testdb=> |
PostgreSQL right join
Right join is complete opposite to left join, it returns all rows from the right table with matching rows from the left table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
testdb=> SELECT a.id id_android, a.feature feature_android, b.id id_iphone, b.feature feature_iphone FROM android a RIGHT JOIN iphone b ON a.feature = b.feature; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 4 | calling | 1 | calling | | 2 | siri 2 | touchscreen | 3 | touchscreen | | 4 | ios (4 rows) testdb=> |
PostgreSQL full outer join
Full outer is like a union of both tables. It produces all records in both tables.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
testdb=> SELECT a.id id_android, a.feature feature_android, b.id id_iphone, b.feature feature_iphone FROM android a FULL OUTER JOIN iphone b ON a.feature = b.feature; id_android | feature_android | id_iphone | feature_iphone ------------+-----------------+-----------+---------------- 1 | Ginie | | 2 | touchscreen | 3 | touchscreen 3 | android | | 4 | calling | 1 | calling | | 4 | ios | | 2 | siri (6 rows) testdb=> |
PostgreSQL cross join
A CROSS JOIN clause allows you to produce the Cartesian Product of rows in two or more tables.
select * from table1 cross join table2;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
testdb=> testdb=> testdb=> select * from android cross join iphone; id | feature | id | feature ----+-------------+----+------------- 1 | Ginie | 1 | calling 1 | Ginie | 2 | siri 1 | Ginie | 3 | touchscreen 1 | Ginie | 4 | ios 2 | touchscreen | 1 | calling 2 | touchscreen | 2 | siri 2 | touchscreen | 3 | touchscreen 2 | touchscreen | 4 | ios 3 | android | 1 | calling 3 | android | 2 | siri 3 | android | 3 | touchscreen 3 | android | 4 | ios 4 | calling | 1 | calling 4 | calling | 2 | siri 4 | calling | 3 | touchscreen 4 | calling | 4 | ios (16 rows) testdb=> |
PostgreSQL Natural Join
A natural join is a join that creates an implicit join based on the same column names in the joined tables.
SELECT *FROM Table1NATURAL [INNER, LEFT, RIGHT] JOIN Table2;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
testdb=> select * from emp natural join dept; job | deptno | empno | ename | mgr | hiredate | sal | comm | deptaddress -----------+--------+-------+-----------+------+----------------------------------+---------+---------+------------- CLERK | 20 | 7777 | ADAMSMITH | 5555 | 2018-12-30 09:22:56.546907+05:30 | 666.00 | | CANADA CLERK | 20 | 7876 | ADAMS | 7788 | 1987-05-23 00:00:00+05:30 | 1100.00 | 4.00 | CANADA CLERK | 20 | 7369 | SMITH | 7902 | 1980-12-17 00:00:00+05:30 | 800.00 | 300.00 | CANADA PRESIDENT | 10 | 7839 | KING | 4554 | 1981-11-17 00:00:00+05:30 | 5000.00 | 68.00 | LA SALESMAN | 30 | 7499 | ALLEN | 7698 | 1981-02-20 00:00:00+05:30 | 1600.00 | 300.00 | NY SALESMAN | 30 | 7844 | TURNER | 7698 | 1981-09-08 00:00:00+05:30 | 1500.00 | 4.00 | NY SALESMAN | 30 | 7521 | WARD | 7698 | 1981-02-22 00:00:00+05:30 | 1250.00 | 500.00 | NY SALESMAN | 30 | 7654 | MARTIN | 7698 | 1981-09-28 00:00:00+05:30 | 1250.00 | 1400.00 | NY (8 rows) testdb=> |
PostgreSQL self join
A self-join is a query in which a table is joined to itself. Self-joins are useful for comparing values in a column of rows within the same table. Read here for more about self joins.
Words from postgreshelp
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.
