Previous Next JavaScript must be enabled to correctly display this content

## Example: Using Left Outer Joins

Let us take an example of an online shopping portal that uses the Oracle NoSQL Database. This application has millions of customers who do online shopping and add things they want to buy in a shopping cart. Finally, the order is processed and billed. The shopping pattern of customers can be analyzed using this application.

Here there are three tables in a parent-child relationship. The `customerprofile` table with all the personal information of customers is the top-level table. `Shoppingcart` which lists the items picked by the customer, their quantity, and the individual price of each item is the descendant table of `customerprofile` . `Order` which has the total order quantity and the total order value. is the descendant table of `shoppingcart`.

Let us create these tables and populate some data into them.

    CREATE TABLE customerprofile (
         customer_id INTEGER,
         customer_name STRING,
         customer_address STRING,
         customer_phone INTEGER,
         PRIMARY KEY(customer_id))

    CREATE TABLE customerprofile.shoppingcart (
         cart_id STRING,
         product_name STRING,
         product_quantity INTEGER,
         product_price INTEGER,
         PRIMARY KEY(cart_id))

    CREATE TABLE customerprofile.shoppingcart.order (
         order_id INTEGER,
         order_quantity INTEGER,
         order_total INTEGER,
         PRIMARY KEY(order_id))

    INSERT INTO customerprofile VALUES (1,"Aana","Blr",111111111)
    INSERT INTO customerprofile VALUES (2,"Bobby","Chn",22222222)
    INSERT INTO customerprofile VALUES (3,"Celin","Del",33333333)
    INSERT INTO customerprofile VALUES (4,"Diana","Blr",44444444)
    INSERT INTO customerprofile VALUES (5,"Elizabeth","Mum",55555555)

    INSERT INTO customerprofile.shoppingcart VALUES(1,'c1',"Shampoo",2,300)
    INSERT INTO customerprofile.shoppingcart VALUES(1,'c2',"Soap",3,80)
    INSERT INTO customerprofile.shoppingcart VALUES(2,'c3',"Milk",5,100)
    INSERT INTO customerprofile.shoppingcart VALUES(3,'c4',"Chips",2,50)
    INSERT INTO customerprofile.shoppingcart VALUES(4,'c5',"Bread",1,40)

    INSERT INTO customerprofile.shoppingcart.order VALUES(1,'c1',100,2,600)
    INSERT INTO customerprofile.shoppingcart.order VALUES(1,'c2',101,3,240)
    INSERT INTO customerprofile.shoppingcart.order VALUES(2,'c3',102,5,500)
    INSERT INTO customerprofile.shoppingcart.order VALUES(3,'c4',103,2,100)

Examples using LOJ

Example 6-120 Use case 1: Fetch all customer details and their order history

    SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart.order c
    ON a.customer_id = c.customer_id ORDER BY a.customer_id

Output:

    {"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"c":{"customer_id":1,"cart_id":"c1","order_id":100,"order_quantity":2,"order_total":600}}
    {"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"c":{"customer_id":1,"cart_id":"c2","order_id":101,"order_quantity":3,"order_total":240}}
    {"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},"c":{"customer_id":2,"cart_id":"c3","order_id":102,"order_quantity":5,"order_total":500}}
    {"a":{"customer_id":3,"customer_name":"Celin","customer_address":"Del","customer_phone":33333333},"c":{"customer_id":3,"cart_id":"c4","order_id":103,"order_quantity":2,"order_total":100}}
    {"a":{"customer_id":4,"customer_name":"Diana","customer_address":"Blr","customer_phone":44444444},"c":null}
    {"a":{"customer_id":5,"customer_name":"Elizabeth","customer_address":"Mum","customer_phone":55555555},"c":null}

Example 6-121 Use case 2: Fetch all customers who have shopped for at least 3 pieces of the same item

    SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart b
      ON a.customer_id = b.customer_id WHERE product_quantity >2

Output:

    {"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},"b":{"customer_id":1,"cart_id":"c2","product_name":"Soap","product_quantity":3,"product_price":80}}
    {"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},"b":{"customer_id":2,"cart_id":"c3","product_name":"Milk","product_quantity":5,"product_price":100}}

Example 6-122 Use case 3: Fetch the details of only those customers who have at least shopped for a value of 500

    SELECT * FROM customerprofile a LEFT OUTER JOIN customerprofile.shoppingcart.order b
    ON a.customer_id = b.customer_id WHERE order_total >=500

Output:

    {"a":{"customer_id":1,"customer_name":"Aana","customer_address":"Blr","customer_phone":111111111},
     "b":{"customer_id":1,"cart_id":"c1","order_id":100,"order_quantity":2,"order_total":600}}
    {"a":{"customer_id":2,"customer_name":"Bobby","customer_address":"Chn","customer_phone":22222222},
     "b":{"customer_id":2,"cart_id":"c3","order_id":102,"order_quantity":5,"order_total":500}}