Joins in SQL are used to combine specific columns from two or more tables based on common data columns available. Multiples tables are integrated using joins. In general, joins are used to combine multiple tables. In this article, we will check different Vertica join types with an example.
Test Data
Below are the tables and associated data that we will be using to demonstrate different joins available in Vertica database:
Stud:
VMart=> select * from testdb.stud;
Id | name | city
----+-------+-----------
1 | Bill | London
2 | Ram | Mumbai
3 | Roy | Bangalore
4 | Nam | Mumbai
5 | ramie | Bangalore
(5 rows)
Dept:
VMart=> select * from testdb.dept;
Did | name | sid
------+---------+-----
1000 | CSE | 1
1001 | Maths | 1
1002 | Physics | 2
1003 | Chem | 3
(4 rows)
Before jumping into Vertica join types, let us check how Vertica optimizer uses various algorithms to resolve different join conditions.
Related Articles:
- Run SQL Script File using Vertica vsql Variable Substitution
- Vertica WITH Clause, Syntax, Usage and Examples
Vertica Join Algorithms
Vertica optimizer implements join using one of the following algorithms:
Merge Join
Merge joins in Vertica are faster and uses less memory compared to other algorithm. Vertica optimizer chooses merge join if two tables are joined on sorted columns.
Hash Join
Vertica uses hash joins if joining columns are not already sorted. Vertica optimizer uses memory to build hash table on inner or left tables join column. Optimizer then scans outer table or right table for matches to the hash table which is already available in memory. Finally, two tables are joined on joining columns.
Performance cost is low if inner table is small enough to fit into memory, cost will go up if inner table size is large as Vertica optimizer must write hash table to disk.
Hint: Avoid joining columns that contains NULL values, otherwise use Vertica NVL functions to replace NULLs with custom values.
Different Vertica Join Types
Vertica 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
Inner Join
The inner join is also represented as simply “join” in SQL queries. Inner join combines two tables based in the joining condition and return columns specified in the SELECT clause that satisfy the joining condition. In general, inner join return common values from two 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 testdb.stud as t1
JOIN testdb.dept as t2
ON (t1.id = t2. sid);
ID | DID
----+------
1 | 1000
1 | 1001
2 | 1002
3 | 1003
(4 rows)Left Outer Join
Left outer join returns complete set of records from the left table along with any matched records from right table. Unmatched records from right tables will be NULL 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 testdb.stud as t1
LEFT OUTER JOIN testdb.dept as t2
ON (t1.id = t2. sid);
ID | name | DID | name
----+-------+------+---------
1 | Bill | 1000 | CSE
1 | Bill | 1001 | Maths
2 | Ram | 1002 | Physics
3 | Roy | 1003 | Chem
4 | Nam | |
5 | ramie | |
(6 rows)Right Outer Join
Right outer join returns complete set of records from the right table along with any matched records from left table. Unmatched records from left tables will be returned 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 testdb.stud as t1
RIGHT OUTER JOIN testdb.dept as t2
ON (t1.id = t2. sid);
ID | name | DID | name
----+------+------+---------
1 | Bill | 1000 | CSE
1 | Bill | 1001 | Maths
2 | Ram | 1002 | Physics
3 | Roy | 1003 | Chem
(4 rows)Full Outer Join
The full outer join is a combination of left and right 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 testdb.stud as t1
FULL OUTER JOIN testdb.dept as t2
ON (t1.id = t2. sid);
ID | name | DID | name
----+-------+------+---------
1 | Bill | 1000 | CSE
1 | Bill | 1001 | Maths
2 | Ram | 1002 | Physics
3 | Roy | 1003 | Chem
4 | Nam | |
5 | ramie | |
(6 rows)Natural joins
Join condition in natural join as implicit; you don’t have to specify the join condition explicitly. Implicit join condition is formed by matching all pairs of columns in 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;Result:
SELECT t1.ID,t1.name, t2.DID, t2.name
FROM testdb.stud as t1
NATURAL LEFT JOIN testdb.dept as t2;
ID | name | DID | name
----+-------+-----+------
1 | Bill | |
2 | Ram | |
3 | Roy | |
4 | Nam | |
5 | ramie | |
(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 testdb.stud as t1
CROSS JOIN testdb.dept as t2;
ID | name | DID | name
----+-------+------+---------
1 | Bill | 1000 | CSE
1 | Bill | 1001 | Maths
1 | Bill | 1002 | Physics
1 | Bill | 1003 | Chem
2 | Ram | 1000 | CSE
2 | Ram | 1001 | Maths
2 | Ram | 1002 | Physics
2 | Ram | 1003 | Chem
3 | Roy | 1000 | CSE
3 | Roy | 1001 | Maths
3 | Roy | 1002 | Physics
3 | Roy | 1003 | Chem
4 | Nam | 1000 | CSE
4 | Nam | 1001 | Maths
4 | Nam | 1002 | Physics
4 | Nam | 1003 | Chem
5 | ramie | 1000 | CSE
5 | ramie | 1001 | Maths
5 | ramie | 1002 | Physics
5 | ramie | 1003 | Chem
(20 rows)Hope this helps 🙂