Retrieving Data from Multiple Tables by Using SQL Join
PostgreSQL supports various kind of SQL Joins to allow you to retrieve data from multiples tables.
- Cross join
- Inner join
- Left outer join
- Right outer join
- Full outer join
In this tutorial we take a look at how to use Join and discuss join types in PostgreSQL with examples.
Cross Join
The syntax of cross join is as follows:
SELECT * FROM table_name1
CROSS JOIN table_name2
For each combination of rows from table_name1 and table_name2 , the query will return the result set which contain rows consisting of all columns in table table_name1 followed by all columns in table table_name2. If the table table_name1 has N rows and the table table_name2 has M rows, the result set will contains N * M rows.
Here is an example of using cross join to join table orderInfo and orderLine:
SELECT I.*, L.item_id,L.quantity
FROM OrderInfo I
CROSS JOIN OrderLine L
.gif)
PostgreSQL combines each rows in the orderline table with every row in orderinfo table to produce the result.
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
4 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
4 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
4 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
7 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
7 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
7 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
9 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
9 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
9 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
9 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
9 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
10 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
10 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
10 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
10 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
10 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
2 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
7 |
2 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
7 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
7 |
2 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
2 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
4 |
2 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
4 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
4 |
2 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
2 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
2 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
2 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
2 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
2 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
5 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
5 |
2 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
5 |
2 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
5 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
5 |
2 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
3 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
3 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
3 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
3 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
3 |
1 |
Inner Join
The syntax of using inner join is as follows:
SELECT column_list
FROM table_name1
INNER JOIN table_name2 ON conditions
For each row R1 in the table table_name1, the result set have a row for each row in the table table_name2 which satisfies the join condition with row R1. For example, we can use inner join to join find out the complete information about the order by executing the following query:
SELECT I.*, L.item_id,L.quantity
FROM OrderInfo I INNER JOIN OrderLine L
ON I.orderinfo_id = L.orderinfo_id
Here is the explaination in a visual way by PostgreSQL Admin:
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
9 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
10 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
2 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
2 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
5 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
3 |
1 |
Left outer join
The syntax of Left Outer Join is as follows:
SELECT column_list
FROM table_name1
LEFT OUTER JOIN table_name2 ON conditions
An inner join is peformed first and then for each row in the table table_name1 that does not satisfy the join conditions with any row in the table table_name2, a joined row is added with NULL in the column of the table table_name2. Therefore the result set returns from Left Outer Join has at least one row for each row in the table table_name1.
For example we can use left outer joni to join table orderInfo and orderline as follows:
SELECT I.*, L.item_id,L.quantity
FROM OrderInfo I LEFT OUTER JOIN OrderLine L
ON I.orderinfo_id = L.orderinfo_id
The result set will contains at least rows in the left table orderInfo
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
9 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
10 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
2 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
5 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
3 |
1 |
Right outer join
The syntax of right outer join is as follows:
SELECT column_list
FROM table_name1 RIGHT OUTER JOIN table_name2
ON conditions
Conversely of the left outer join, right outer join will returns result set which contains at least rows in the right table. An inner join is also performed first. And then, for each row in table table_name2 that does not satisfy the join condition with any row in table table_name1, a joined row is added with NULL values in columns of table table_name1.
For example we can use right outer join to join tables orderInfo and orderLine as follows:
SELECT I.*, L.item_id,L.quantity
FROM OrderInfo I RIGHT OUTER JOIN OrderLine L
ON I.orderinfo_id = L.orderinfo_id
Here is the visual explaination of right outer join:
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
9 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
10 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
2 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
2 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
5 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
3 |
1 |
Full outer join
The syntax of full outer join is as follows:
SELECT column_list
FROM table_name1 FULL OUTER JOIN table_name2
ON conditions
- An inner join is performed first.
- And then, for each row in table table_name1 that does not satisfy the join condition with any row in table table_name2, a joined row is added with null values in columns of table_name2.
- In addition, for each row of table table_name2 that does not satisfy the join condition with any row in table table_name1, a joined row with null values in the columns of table_name1 is added.
Therefore the result set will contains rows in both left ( table_name1 )and right table ( table_nam2), and fill in NULL values for missing matches on either table.
Here is an example of using full outer join to join table orderinfo and orderline:
SELECT I.*, L.item_id,L.quantity
FROM OrderInfo I FULL OUTER JOIN OrderLine L
ON I.orderinfo_id = L.orderinfo_id
Here is the visual explaination of full outer join
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
4 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
7 |
1 |
| 1 |
3 |
2004-03-13 |
2004-03-17 |
2.99 |
9 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
1 |
1 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
4 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
7 |
2 |
| 2 |
8 |
2004-06-23 |
2004-06-24 |
0.00 |
10 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
1 |
1 |
| 3 |
15 |
2004-09-02 |
2004-09-12 |
3.99 |
2 |
1 |
| 4 |
13 |
2004-09-03 |
2004-09-10 |
2.99 |
5 |
2 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
1 |
1 |
| 5 |
8 |
2004-07-21 |
2004-07-24 |
0.00 |
3 |
1 |