SQL Join is a clause that is used for combining specific fields from two or more tables based on the common columns available. Joins are used to combine rows from multiple tables. In this article, we will learn about different Redshift join types with some examples.
Below are the tables that we will be using to demonstrate different Join types available in Redshift:
Students:
Id name city
1 AAA London
2 BBB Mumbai
3 CCC Bangalore
4 DDD Mumbai
5 EEE Bangalore
Dept:
Did name sid
100 CS 1
101 Maths 1
102 Physics 2
103 Chem 3
Different Redshift Join Types
Following are Different Redshift Join Types
JOIN or INNER JOIN
JOIN or INNER JOIN It returns the matching rows from both the tables. Redshift SQL inner join is a type of join operation used to combine data from two or more tables based on a related column between them. The resulting output only contains the rows that match the join condition.
Inner join is also sometimes referred to as an equijoin because the join condition typically involves an equality operator (=). An inner join returns only the rows that have matching values in both tables based on the join condition.
Syntax:
Following is the inner join syntax:
SELECT table1.col1, table2.col1 FROM table2 JOIN table1 ON (table1.common_col = table2. common_col);
Results:
Following is the inner join output:
strudent_id | student_name | dept_id | dept_name -------------+--------------+---------+----------- 2 | BBB | 102 | Physics 3 | CCC | 103 | Chem 1 | AAA | 101 | Maths 1 | AAA | 100 | CS (4 rows)
LEFT JOIN or LEFT OUTER JOIN
This type of join returns all rows from the left table even if there is no matching row in the right table. Unmatched right tables records will be NULL. The left join is also known as a left outer join.
The Redshift SQL left join is a type of join operation that combines data from two or more tables based on a related column between them. Unlike an inner join, which only returns the rows that match the join condition, a left join returns all the rows from the left table and the matching rows from the right table. The unmatched rows form right table would be set to NULL.
Syntax:
Following is the left join syntax:
SELECT table1. col1, table1.col2, table2.col1, table2.col2 FROM table1 LEFT OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);
Result:
Following is the left join output:
id | name | city | did | name | sid ----+------+-----------+-----+---------+----- 2 | BBB | Mumbai | 102 | Physics | 2 3 | CCC | Bangalore | 103 | Chem | 3 1 | AAA | London | 101 | Maths | 1 1 | AAA | London | 100 | CS | 1 4 | DDD | Mumbai | | | 5 | EEE | Bangalore | | | (6 rows)
RIGHT JOIN or RIGHT OUTER JOIN
This type of join returns all rows from the right table even if there is no matching row in the left table. Table returns all rows from the right table and matching rows from left table. All unmatched left table records will be NULL.
The Redshift SQL right join is a type of join operation that combines data from two or more tables based on a related column between them. Unlike an inner join, which only returns the rows that match the join condition, a right join returns all the rows from the right table and the matching rows from the left table. The unmatched rows form left table would be set to NULL.
Syntax:
Following is the right join syntax:
SELECT table1. col1, table1.col2, table2.col1, table2.col2 FROM table1 RIGHT OUTER JOIN table2 ON (table1.matching_col = table2.matching_col);
Result:
Following is the right join output:
id | name | city | did | name | sid ----+------+-----------+-----+---------+----- 2 | BBB | Mumbai | 102 | Physics | 2 3 | CCC | Bangalore | 103 | Chem | 3 1 | AAA | London | 101 | Maths | 1 1 | AAA | London | 100 | CS | 1 4 | DDD | Mumbai | | | 5 | EEE | Bangalore | | | (6 rows)
FULL JOIN or FULL OUTER JOIN
This type of join returns all rows from the both tables that fulfill the JOIN condition. The unmatched rows from both tables will be returned as a NULL.
The FULL OUTER JOIN in Redshift SQL is used to combine the results of both left join and right join. It returns all the rows from both the left table and the right table, with matching rows from both tables combined and non-matching rows from either table set to NULL values.
Syntax:
Following is the full 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:
Following is the full join example:
id | name | city | did | name | sid ----+------+-----------+-----+---------+----- 3 | CCC | Bangalore | 103 | Chem | 3 1 | AAA | London | 101 | Maths | 1 1 | AAA | London | 100 | CS | 1 2 | BBB | Mumbai | 102 | Physics | 2 4 | DDD | Mumbai | | | 5 | EEE | Bangalore | | | (6 rows)
CROSS JOIN
The CROSS JOIN produces a result set with all combinations of rows from the left and right tables.
A CROSS JOIN, also known as a cartesian join, is a type of Redshift SQL join that returns the Cartesian product of two tables. It returns every possible combination of rows from the two tables, with each row from the first table paired with every row from the second table.
Syntax:
Following is the cross join syntax:
SELECT table1. col1, table1.col2, table2.col1, table2.col2 FROM table1 CROSS JOIN table2;
Results:
Following is the cross join output:
id | name | city | did | name | sid ----+------+-----------+-----+---------+----- 2 | BBB | Mumbai | 100 | CS | 1 2 | BBB | Mumbai | 101 | Maths | 1 2 | BBB | Mumbai | 102 | Physics | 2 2 | BBB | Mumbai | 103 | Chem | 3 3 | CCC | Bangalore | 100 | CS | 1 3 | CCC | Bangalore | 101 | Maths | 1 3 | CCC | Bangalore | 102 | Physics | 2 3 | CCC | Bangalore | 103 | Chem | 3 1 | AAA | London | 100 | CS | 1 1 | AAA | London | 101 | Maths | 1 1 | AAA | London | 102 | Physics | 2 1 | AAA | London | 103 | Chem | 3 .... (20 rows)
Read:
- Redshift NVL and NVL2 Functions with Examples
- Redshift String Functions and Examples
- Commonly used Amazon Redshift Date Functions and Examples
- How to Optimize Query Performance on Redshift?
Very nice write up, thanks!
Thank you 🙂