Joining Tables – Inner¶
Let us understand how to join data from multiple tables.
We will primarily focus on ANSI style join (JOIN with ON).
There are different types of joins.
INNER JOIN - Get all the records from both the datasets which satisfies JOIN condition.
OUTER JOIN - We will get into the details as part of the next topic
Example for INNER JOIN
SELECT o.order_id,
o.order_date,
o.order_status,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
LIMIT 10
We can join more than 2 tables in one query. Here is how it will look like.
SELECT o.order_id,
o.order_date,
o.order_status,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
JOIN products p
ON p.product_id = oi.order_item_product_id
LIMIT 10
If we have to apply additional filters, it is recommended to use WHERE clause. ON clause should only have join conditions.
We can have non equal join conditions as well, but they are not used that often.
Here are some of the examples for INNER JOIN:
Get order id, date, status and item revenue for all order items.
Get order id, date, status and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED.
Get order id, date, status and item revenue for all order items for all orders where order status is either COMPLETE or CLOSED for the orders that are placed in the month of 2014 January.
%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_subtotal
FROM orders o 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_subtotal |
|---|---|---|---|
| 1 | 2013-07-25 00:00:00 | CLOSED | 299.98 |
| 2 | 2013-07-25 00:00:00 | PENDING_PAYMENT | 199.99 |
| 2 | 2013-07-25 00:00:00 | PENDING_PAYMENT | 250.0 |
| 2 | 2013-07-25 00:00:00 | PENDING_PAYMENT | 129.99 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 49.98 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 299.95 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 150.0 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 199.92 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 299.98 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 299.95 |
%sql SELECT count(1) FROM orders
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| count |
|---|
| 68883 |
%sql SELECT count(1) FROM order_items
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| count |
|---|
| 172198 |
%%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_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
LIMIT 10
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
| order_id | order_date | order_status | order_item_subtotal |
|---|---|---|---|
| 1 | 2013-07-25 00:00:00 | CLOSED | 299.98 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 49.98 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 299.95 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 150.0 |
| 4 | 2013-07-25 00:00:00 | CLOSED | 199.92 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 299.98 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 299.95 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 99.96 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 299.98 |
| 5 | 2013-07-25 00:00:00 | COMPLETE | 129.99 |
%%sql
SELECT count(1)
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
LIMIT 10
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| count |
|---|
| 75408 |
%%sql
SELECT o.order_id,
o.order_date,
o.order_status,
oi.order_item_subtotal
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
AND to_char(order_date, 'yyyy-MM') = '2014-01'
LIMIT 10
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.
| order_id | order_date | order_status | order_item_subtotal |
|---|---|---|---|
| 25882 | 2014-01-01 00:00:00 | COMPLETE | 299.97 |
| 25882 | 2014-01-01 00:00:00 | COMPLETE | 100.0 |
| 25882 | 2014-01-01 00:00:00 | COMPLETE | 79.98 |
| 25882 | 2014-01-01 00:00:00 | COMPLETE | 399.98 |
| 25888 | 2014-01-01 00:00:00 | COMPLETE | 299.98 |
| 25889 | 2014-01-01 00:00:00 | COMPLETE | 99.96 |
| 25889 | 2014-01-01 00:00:00 | COMPLETE | 19.99 |
| 25891 | 2014-01-01 00:00:00 | CLOSED | 150.0 |
| 25891 | 2014-01-01 00:00:00 | CLOSED | 50.0 |
| 25891 | 2014-01-01 00:00:00 | CLOSED | 119.97 |
%%sql
SELECT count(1)
FROM orders o JOIN order_items oi
ON o.order_id = oi.order_item_order_id
WHERE o.order_status IN ('COMPLETE', 'CLOSED')
AND to_char(order_date, 'yyyy-MM') = '2014-01'
LIMIT 10
* postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.
| count |
|---|
| 6198 |