Joining Tables – Inner

Let us understand how to join data from multiple tables.

  • We will primarily focus on ASCII 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

Copy to clipboard

  • 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

Copy to clipboard

  • 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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

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

Copy to clipboard

%%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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

count
68883
%sql SELECT count(1) FROM order_items

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

count
172198
%%sql

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

Copy to clipboard

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

Copy to clipboard

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
1 rows affected.

Copy to clipboard

count
6198