Introduction to LEFT JOIN in PostgreSQL
The join is where SQL statements work on multiple tables at a time and we have two or more tables joined to get results. As a result, the left join operation returns us all rows from the left-hand side table and only matched rows from the right-hand side table where the condition defined in ON clause satisfies. The LEFT JOIN and LEFT OUTER JOIN are similar terms. In this topic, we are going to learn about PostgreSQL LEFT JOIN.
Syntax
The PostgreSQL LEFT [OUTER] JOIN syntax:
SELECT column_name1, column_name2, column_name3....
FROM table_name1
LEFT [OUTER] JOIN table_name2
ON table_name1.column_name = table_name2.column_name;
Explanation:
- Define the list of column names from which you want to retrieve the data.
- Define the first (left) table from where you want to fetch all rows in the FROM clause like table_name1.
- Define the second (right) table in the LEFT JOIN clause like table_name2.
- Define the condition to join both tables.
Visualize the following diagram to understand the result set of the PostgreSQL LEFT [OUTER] JOIN.
It returns the highlighted area a result of join operation:
How LEFT JOIN works in PostgreSQL?
The PostgreSQL LEFT JOIN is used to join two tables. It returns the result set containing all rows from the first (left) table defined in the JOIN clause and all matched rows from second_table based on a condition.
Consider a syntax as
first_table LEFT JOIN second_table JOIN CONDITION
Understand table dependency.
- The second_table depends on first_table and all tables on which first_table is depended on.
- The first_table depends on all tables that are used in the LEFT JOIN condition except second_table.
Condition:
The condition defined in the LEFT JOIN clause is used to decide how to fetch rows from second_table.
If a row of the first_table which matches the condition defined in the WHERE clause, but there is no row in second_table which matches the condition defined in the ON condition then rows for second_table will get added with a NULL value for all columns.
The PostgreSQL LEFT JOIN or LEFT OUTER JOIN operation returns the result as:
- Fetches all values from the first (left) table.
- Combines them with the column names defined in the condition from the second (right) table
- Fetch the matched rows from both the first (left) table and second (right) table.
- If first (left) table rows are not matched with a row from the second (right) table then for each column of second (right) table it sets the value to NULL.
Examples of PostgreSQL LEFT JOIN
Let’s create two tables named’ transaction’ and ‘invoices’ in order to understand the PostgreSQL NATURAL JOIN examples.
The following CREATE TABLE statements will create the transaction and invoices table.
CREATE TABLE transaction (
transaction_id serial PRIMARY KEY,
transaction_data VARCHAR (256) NOT NULL
);
CREATE TABLE invoices (
invoice_id serial PRIMARY KEY,
transaction_id INT NOT NULL,
invoice_data VARCHAR (256) NOT NULL,
FOREIGN KEY (transaction_id) REFERENCES transaction (transaction_id)
);
The transaction_id is the primary key of the transaction table which is referred to as foreign key for the invoices table. So while performing natural join operation we will use the transaction_id column as it is the common column for both tables. The transaction may have zero or more invoices and the invoice will belong to one and only one transaction.
Now insert some data into the transaction and invoices tables using INSERT statement as follows:
INSERT INTO transaction (transaction_data)
VALUES
('Purchase of Mobile'),
('Purchase of PC'),
('Purchase of Headphone');
INSERT INTO invoices (invoice_data, transaction_id)
VALUES
('Purchase of Mobile', 1),
('Purchase of Mobile', 1),
('Purchase of PC', 2),
('Purchase of PC', 2),
('Purchase of Headphone', 3),
('Purchase of Headphone', 3);
Illustrate the content of the transaction table using the following statement and snapshot.
select * from transaction;
Illustrate the content of the invoices table using the following statement and snapshot.
select * from invoices;
Example of LEFT JOIN clause to join the transaction table to the invoices table as follows:
SELECT
transaction.transaction_id,
transaction.transaction_data,
invoice_data
FROM
transaction
LEFT JOIN invoices ON invoices.transaction_id = transaction.transaction_id;
Illustrate the result of the above statement using the following snapshot.
We can use the WHERE clause to select only invoices whose transaction_id is not (1 and 2) as follows:
SELECT
transaction.transaction_id,
transaction.transaction_data,
invoice_data
FROM
transaction
LEFT JOIN invoices ON invoices.transaction_id = transaction.transaction_id
where invoices.transaction_id not in ( 1,2);
Illustrate the result of the above statement using the following snapshot.
Using a where clause you can filter out data that you want to retrieve from one table which actually has a match in other tables.
Conclusion
We hope from the above article you have learned about the PostgreSQL LEFT JOIN and how the PostgreSQL LEFT JOIN works. Also, we have added some examples to demonstrate to you how to use the LEFT JOIN to query data from the tables mentioned.
Recommended Articles
This is a guide to PostgreSQL LEFT JOIN. Here we discuss how the PostgreSQL LEFT JOIN works along with the examples. You may also look at the following articles to learn more –
