Greenplum Different Joins and Examples

Joins in SQL are used to retrieve or combine specific columns from two or more tables based on common data columns available in the tables. Columns from multiple tables are integrated using different joins. In this article, we will check Greenplum different joins and demonstrate using live examples. These joins are same as PostgreSQL joins.

Test Data

Below are the tables and associated data that we will be using to demonstrate different joins available in Greenplum database.

Table1:

template1=# select * from table1;
 id | name  |   city
----+-------+-----------
  1 | Bill  | London
  3 | Roy   | Bangalore
  5 | ramie | Bangalore
  2 | Ram   | Mumbai
  4 | Nam   | Mumbai
(5 rows)

Table2 :

template1=# select * from table2;
 did  |  name   | sid
------+---------+-----
 1001 | Maths   |   1
 1003 | Chem    |   3
 1000 | CSE     |   1
 1002 | Physics |   2
(4 rows)

Greenplum Different Joins

Greenplum uses PostgreSQL that supports all join types that are supported in SQL-99.

Following are the different Vertica join types:

Inner join
Left [outer] join
Right [outer] join
full outer join
Natural joins
Cross join

Now let us verify these join types in detail with syntax and examples.

Inner Join

The inner join is called as simply “join” in SQL queries. Inner join combines two tables based on the joining condition and return column list specified in the SELECT clause that satisfy the joining condition. In general, inner join return columns which are common to two or more tables.

Inner Join Syntax

SELECT t1.col1, t2.col1 
FROM table2 as t1
JOIN table1 as t2
ON (t1.common_column = t2. common_column);

Results:

SELECT t1.ID, t2.DID
FROM table1 as t1
JOIN table2 as t2
ON (t1.id = t2. sid);

 id | did
----+------
  1 | 1000
  1 | 1001
  3 | 1003
  2 | 1002
(4 rows)

Left Outer Join

Left outer join will usually returns complete set of records from the left table along with any matched records from right table. All unmatched records from right tables will be kept as NULLs by default.

Left Outer Join Syntax

SELECT t1. col1, t1.col2, t2.col1, t2.col2
FROM table1 as t1
LEFT OUTER JOIN table2 as t2
ON (t1.matching_col = t2.matching_col);

Results:

SELECT t1.ID,t1.name, t2.DID, t2.name
FROM table1 as t1
LEFT OUTER JOIN table2 as t2
ON (t1.id = t2. sid);

 id | name  | did  |  name
----+-------+------+---------
  2 | Ram   | 1002 | Physics
  4 | Nam   |      |
  1 | Bill  | 1001 | Maths
  1 | Bill  | 1000 | CSE
  3 | Roy   | 1003 | Chem
  5 | ramie |      |
(6 rows)

Right Outer Join

Right outer join usually returns complete records from the right table along with any matched records from left table. All unmatched records from left tables will be returned as NULL by default.

Right Outer Join Syntax

SELECT t1. col1, t1.col2, t2.col1, t2.col2
FROM table1 as t1
RIGHT OUTER JOIN table2 as t2
ON (t1.matching_col = t2.matching_col);

Results:

SELECT t1.ID,t1.name, t2.DID, t2.name
FROM table1 as t1
RIGHT OUTER JOIN table2 as t2
ON (t1.id = t2.sid);

 id | name | did  |  name
----+------+------+---------
  1 | Bill | 1000 | CSE
  1 | Bill | 1001 | Maths
  3 | Roy  | 1003 | Chem
  2 | Ram  | 1002 | Physics
(4 rows)

Full Outer Join

If you combine both LEFT and RIGHT outer join you will get full outer join. Full outer join returns all rows from the both tables for columns listed in SELECT clause that fulfill the join condition. The unmatched rows from both tables will be returned as a NULL.

Full Outer Join Syntax

SELECT table1. col1, table1.col2, table2.col1, table2.col2
 FROM table1 
 FULL OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);

Results:

SELECT t1.ID,t1.name, t2.DID, t2.name
FROM table1 as t1
FULL OUTER JOIN table2 as t2
ON (t1.id = t2. sid);

 id | name  | did  |  name
----+-------+------+---------
  4 | Nam   |      |
  2 | Ram   | 1002 | Physics
  3 | Roy   | 1003 | Chem
  1 | Bill  | 1001 | Maths
  1 | Bill  | 1000 | CSE
  5 | ramie |      |
(6 rows)

Natural joins

You don’t have to specify the joining condition when using natural join as it is implicit. Implicit join condition is formed by matching all pairs of columns in participating tables with same name and compatible data type. The result set contains only one column representing the pair of equally named column.

Natural Joins Syntax

SELECT  t1.col1, t2.co2
 FROM  table1 as t1
 NATURAL [ INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER ] JOIN
 Table2 as t2;

Results:

SELECT t1.ID,t1.name, t2.DID, t2.name
FROM table1 as t1
NATURAL LEFT JOIN table2 as t2;

 id | name  | did | name
----+-------+-----+------
  1 | Bill  |     |
  3 | Roy   |     |
  5 | ramie |     |
  2 | Ram   |     |
  4 | Nam   |     |
(5 rows)

Cross Joins

Cross join returns all possible combinations of matching values. This type of join produces a result set with all combinations of rows from the left and right tables.

Cross Join Syntax

SELECT t1. col1, t1.col2, t2.col1, t2.col2
 FROM table1 as t1 
 CROSS JOIN table2 as t2;

Results:

SELECT t1.ID,t1.name, t2.DID, t2.name
FROM table1 as t1
CROSS JOIN table2 as t2;

 id | name  | did  |  name
----+-------+------+---------
  1 | Bill  | 1000 | CSE
  3 | Roy   | 1000 | CSE
  5 | ramie | 1000 | CSE
  1 | Bill  | 1002 | Physics
  3 | Roy   | 1002 | Physics
  5 | ramie | 1002 | Physics
  1 | Bill  | 1001 | Maths
  3 | Roy   | 1001 | Maths
  5 | ramie | 1001 | Maths
  1 | Bill  | 1003 | Chem
  3 | Roy   | 1003 | Chem
  5 | ramie | 1003 | Chem
  2 | Ram   | 1000 | CSE
  4 | Nam   | 1000 | CSE
  2 | Ram   | 1002 | Physics
  4 | Nam   | 1002 | Physics
  2 | Ram   | 1001 | Maths
  4 | Nam   | 1001 | Maths
  2 | Ram   | 1003 | Chem
  4 | Nam   | 1003 | Chem
(20 rows)