Joining Tables – Outer

Private: Mastering SQL using Postgresql Writing Basic SQL Queries 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

Previous Topic

Back to Lesson

Next Topic