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.
In [3]:
%load_ext sql
In [4]:
%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@pg.itversity.com:5432/itversity_retail_db
In [5]:
%%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
10 rows affected.
Out[5]:
| 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 |
In [6]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[6]:
| count |
|---|
| 183650 |
In [7]:
%%sql
SELECT count(1)
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
* postgresql://itversity_retail_user:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[7]:
| count |
|---|
| 172198 |
In [8]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[8]:
| 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 |
In [9]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[9]:
| count |
|---|
| 11452 |
In [10]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[10]:
| count |
|---|
| 5189 |
In [11]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 10 rows affected.
Out[11]:
| 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 |
In [12]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 1 rows affected.
Out[12]:
| count |
|---|
| 172198 |
In [13]:
%%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:***@pg.itversity.com:5432/itversity_retail_db 0 rows affected.
Out[13]:
| order_id | order_date | order_status | order_item_order_id | order_item_subtotal |
|---|