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

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;

{"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}

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;

{"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}}

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 ;

{"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}}