Overview of Left Outer Joins

A Left Outer Join (LOJ) is one of the join operations that allows you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the second (right) table. This means all left rows that do not have a matching row in the right table will appear in the result, paired with a NULL value in place of a right row.

In an LOJ, the order of fields in the result-set is always in top-down order. That means the order of output in the result set is always from the ancestor table first and then the descendant table. This is true irrespective of the order of the joins.

Characteristics of LEFT OUTER JOIN:

Queries multiple tables in the same hierarchy
It is an ANSI-SQL Standard
It does not support sibling table joins

If you want to follow along with the examples, download the script parentchildtbls_loaddata.sql and run it as shown below. This script creates the table used in the example and loads data into the table.

Start your KVSTORE or KVLite and open the SQL.shell.

 java -jar lib/kvstore.jar kvlite -secure-config disable
 java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

The parentchildtbls_loaddata.sql contains the following:

### Begin Script ###
load -file parentchild.ddl
import -table ticket  -file ticket.json
import -table ticket.bagInfo  -file bagInfo.json
import -table ticket.passengerInfo  -file passengerInfo.json
import -table ticket.bagInfo.flightLegs  -file flightLegs.json
### End Script ###

Using the load command, run the script.

load -file parentchildtbls_loaddata.sql