Full Join - (PostgreSQL Join)
In PostgreSQL, the FULL JOIN is used to combine the rows from two or more tables, including the unmatched rows from both tables. In this tutorial, we'll discuss the syntax, example, output, explanation, use, important points, and summary of the FULL JOIN in PostgreSQL.
Syntax
SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON condition;
Example
Let's take a look at an example to understand how FULL JOIN works.
SELECT *
FROM orders
FULL JOIN customers
ON orders.customer_id = customers.customer_id;
In this example, we are joining two tables: orders and customers. We are using a FULL JOIN to get all the rows from both tables, even if there are no matches in the other table.
Output
The output of the above query would look something like this:
| order_id | customer_id | order_date | customer_id | customer_name | contact_name | country |
|---|---|---|---|---|---|---|
| 1 | 1 | 2022-01-01 | 1 | John Doe | John | USA |
| 2 | 1 | 2022-02-01 | 1 | John Doe | John | USA |
| 3 | 2 | 2022-03-01 | 2 | Jane Smith | Jane | Canada |
| 4 | 3 | 2022-04-01 | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | 4 | Bob Johnson | Bob | UK |
Explanation
In the output of the above query, we can see that all the rows from both tables have been included in the result set, even if there are no matches in the other table. In the orders table, there is an order without a customer_id (order_id = 4). In the customers table, there is a customer without any orders (customer_id = 4). These unmatched rows from both tables are included in the result set.
Use
The FULL JOIN operation is used to combine rows from two or more tables, even if there are no matches in the other table. This can be useful for finding all the data in two or more tables, especially when working with large and complex databases.
Important Points
- When using
FULL JOIN, it's important to use theONkeyword to specify the join condition. - If there are no matching rows in the other table, the columns from that table will be NULL in the result set.
FULL JOINcan be used as an alternative toUNIONto combine the results of two or more tables.
Summary
In this tutorial, we discussed the FULL JOIN in PostgreSQL. We covered the syntax, example, output, explanation, use, and important points of using FULL JOIN. With this knowledge, you can now use FULL JOIN to combine rows from two or more tables, including the unmatched rows from both tables.