PostgreSQL SQL Left Outer Join Example

Have a Database Problem? Speak with an Expert for Free
Get Started >>

What is Left Outer Join in SQL?

Today, we will discuss the Left Outer Join. Left Outer Join or Left Join is the same thing in SQL. In the left outer join, it returns all the data from the left table of the join. At the same point of time, it also matches the data from the table on the right side after the left join.

One More thing that you need to keep in mind. If there are rows available in the left side table and there is no matching data available in the right side table then the result set will contain only null values for them.

Left Outer Join Syntax

1
2
3
4
5
6
7
8
SELECT lefttable1.column1,lefttable1.column2,lefttable2.column1,....
FROM lefttable1
LEFT JOIN lefttable2
ON lefttable1.matching_column = lefttable2.matching_column;

lefttable1: FIRST TABLE.
lefttable2: SECOND TABLE
matching_column: COLUMN common TO BOTH the TABLES.

Above syntax is really easy to understand. Let me explain it to you with a real example. Now, I’m going to create two tables one for “BOOKCUSTOMERS” and the other one for “ORDERS”. How to Use Left Outer Join? We are going to show you a practical example of a left outer join follows along with me. I’m not going to talk about how to create a database or table. I assume that you can create two tables as we are showing below:

TABLE A: BOOKCUSTOMERS

1
2
3
4
5
6
7
8
9
+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | SAMUAL   |  32 | Austin    |  8000.00 |
|  2 | Johnson  |  25 | Ohio      |  9500.00 |
|  3 | Mathew   |  23 | NEW York  |  7000.00 |
|  4 | Eva      |  25 | Texas     |  6500.00 |
|  5 | April    |  27 | San Marcos|  8500.00 |
+----+----------+-----+-----------+----------+

TABLE B: ORDERS

1
2
3
4
5
6
7
8
+-----+---------------------+-----------------+--------+
| OID | DATE                | BOOKCUSTOMER_ID | AMOUNT |
+-----+---------------------+-----------------+--------+
| 102 | 2019-12-08 00:00:00 |           3     |   4000 |
| 100 | 2019-09-08 00:00:00 |           3     |   3500 |
| 101 | 2019-08-20 00:00:00 |           2     |   6560 |
| 103 | 2018-05-20 00:00:00 |           4     |   2060 |
+-----+---------------------+-----------------+--------+

So now, we are going to join these two tables with the help of the left outer join to access the data from the table.

SQL Query for Left Outer Join:

1
2
3
4
SQL> SELECT  ID, NAME, AMOUNT
   FROM BOOKCUSTOMERS
   LEFT JOIN ORDERS
   ON BOOKCUSTOMERS.ID = ORDERS.BOOKCUSTOMER_ID;

In line 1, we are selecting columns that we want to show in our final result from the table “BOOKCUSTOMERS”. Now in line 3, we use the keyword “Left Join” to use Left Join in our query.

In the last line, we are matching columns from table 1 with the column in table 2. In our cases, we are matching “BOOKCUSTOMERS.ID” with “ORDERS.BOOKCUSTOMER_ID”.

Result

Now you can see the final result below:

1
2
3
4
5
6
7
8
9
10
11
+----+----------+--------+
| ID | NAME     | AMOUNT |
+----+----------+--------+
|  1 | SAMUAL   |   NULL |
|  2 | Johnson  |   6560 |
|  3 | Mathew   |   4000 |
|  3 | Mathew   |   3500 |
|  2 | John     |   6560 |
|  4 | Eva      |   2060 |
|  5 | April    |   NULL |
+----+----------+--------+

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.