Joining Tables - Outer

Let us understand how to perform outer joins using SQL. There are 3 different types of outer joins.

  • LEFT OUTER JOIN (default) - Get all the records from both the datasets which satisfies JOIN condition along with those records which are in the left side table but not in the right side table.

  • RIGHT OUTER JOIN - Get all the records from both the datasets which satisfies JOIN condition along with those records which are in the right side table but not in the left side table.

  • FULL OUTER JOIN - left union right

  • When we perform the outer join (lets say left outer join), we will see this.

    • Get all the values from both the tables when join condition satisfies.

    • If there are rows on left side table for which there are no corresponding values in right side table, all the projected column values for right side table will be null.

  • Here are some of the examples for outer join.

    • Get all the orders where there are no corresponding order items.

    • Get all the order items where there are no corresponding orders.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%%sql

SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_order_id,
    oi.order_item_subtotal
FROM orders o LEFT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
ORDER BY o.order_id
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_status order_item_order_id order_item_subtotal
1 2013-07-25 00:00:00 CLOSED 1 299.98
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 129.99
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 250.0
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 199.99
3 2013-07-25 00:00:00 COMPLETE None None
4 2013-07-25 00:00:00 CLOSED 4 199.92
4 2013-07-25 00:00:00 CLOSED 4 150.0
4 2013-07-25 00:00:00 CLOSED 4 299.95
4 2013-07-25 00:00:00 CLOSED 4 49.98
5 2013-07-25 00:00:00 COMPLETE 5 299.98 
%%sql

SELECT count(1)
FROM orders o LEFT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
183650 
%%sql

SELECT count(1)
FROM orders o JOIN order_items oi
    ON o.order_id = oi.order_item_order_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
172198 
%%sql

SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_order_id,
    oi.order_item_subtotal
FROM orders o LEFT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
WHERE oi.order_item_order_id IS NULL
ORDER BY o.order_id
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_status order_item_order_id order_item_subtotal
3 2013-07-25 00:00:00 COMPLETE None None
6 2013-07-25 00:00:00 COMPLETE None None
22 2013-07-25 00:00:00 COMPLETE None None
26 2013-07-25 00:00:00 COMPLETE None None
32 2013-07-25 00:00:00 COMPLETE None None
40 2013-07-25 00:00:00 PENDING_PAYMENT None None
47 2013-07-25 00:00:00 PENDING_PAYMENT None None
53 2013-07-25 00:00:00 PROCESSING None None
54 2013-07-25 00:00:00 PENDING_PAYMENT None None
55 2013-07-25 00:00:00 PENDING None None 
%%sql

SELECT count(1)
FROM orders o LEFT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
WHERE oi.order_item_order_id IS NULL

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
11452 
%%sql

SELECT count(1)
FROM orders o LEFT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
WHERE oi.order_item_order_id IS NULL
    AND o.order_status IN ('COMPLETE', 'CLOSED')

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
5189 
%%sql

SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_order_id,
    oi.order_item_subtotal
FROM orders o RIGHT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
order_id order_date order_status order_item_order_id order_item_subtotal
1 2013-07-25 00:00:00 CLOSED 1 299.98
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 199.99
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 250.0
2 2013-07-25 00:00:00 PENDING_PAYMENT 2 129.99
4 2013-07-25 00:00:00 CLOSED 4 49.98
4 2013-07-25 00:00:00 CLOSED 4 299.95
4 2013-07-25 00:00:00 CLOSED 4 150.0
4 2013-07-25 00:00:00 CLOSED 4 199.92
5 2013-07-25 00:00:00 COMPLETE 5 299.98
5 2013-07-25 00:00:00 COMPLETE 5 299.95 
%%sql

SELECT count(1)
FROM orders o RIGHT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
count
172198 
%%sql

SELECT o.order_id,
    o.order_date,
    o.order_status,
    oi.order_item_order_id,
    oi.order_item_subtotal
FROM orders o RIGHT OUTER JOIN order_items oi
    ON o.order_id = oi.order_item_order_id
WHERE o.order_id IS NULL
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
0 rows affected.
order_id order_date order_status order_item_order_id order_item_subtotal
Joining Tables – Inner Performing Aggregations