PostgreSQL SQL Left Outer Join Example
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