postgresql joins

PostgreSQL Joins demystified

by

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.

PostgreSQL joins

PostgreSQL supports the following join types

  1. inner join
  2. left join
  3. right join
  4. full outer join
  5. cross join
  6. natural join
  7. self join

Let’s discuss each of these join types in detail

Before learning about join types, let’s create two sample tables.

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;

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;

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.

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=>

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

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.

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;

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 Table1
NATURAL [INNER, LEFT, RIGHT] JOIN Table2;
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.

Leave a Comment