#122 SQL and analytics 6.6 min
SQL Joins: Inner, Left and Anti, With Real Output
Three queries asked which customers have no orders; NOT IN returned 0 rows because of one NULL.
Run the four groups
Copy the code folder first. Run python run.py inner, python run.py left, python run.py anti and python run.py onwhere. For each, write down how many rows came back and which customers are missing, then say which row in the data is the reason for the biggest surprise.
A hint
Look at the comments in shop.py: a guest order with no customer id, and an order for a customer that does not exist.
Give Dina an order
In shop.py, add a new order to the ORDERS list for customer 4, for example (107, 4, 50). Before running, predict what python run.py left and python run.py anti will print. On this machine the left join shows Dina with order 107 and amount 50, and the three queries that look for customers with no orders all return 0 rows.
A hint
Dina was the only customer with no orders.
Make NOT IN work
In shop.py, delete the guest order (105, None, 40) from ORDERS. Predict what the not in query returns in python run.py anti, then run it. On this machine not in now returns Dina, the same as the other two, and the list of orders with no customer shrinks to 1 row, which is order 106.
A hint
Not in fails when the list it checks against holds a null.
Move the amount limit
In joins.py, the onwhere group uses o.amount >= 20 twice. Change both to 26 and predict how many rows each of the two queries returns. On this machine the filter in ON gives 4 rows with only Asha having an order, and the filter in WHERE gives 1 row.
A hint
Among orders that belong to a customer, only order 101 (30) is 26 or more; the guest order 105 (40) matches no customer. The ON version keeps every customer, the WHERE version keeps only rows with a matching order.
These tasks are for you to try on your own machine. Nothing is checked or saved.
What this does not show
The two tables are tiny and made up, and the join rules are quoted from the PostgreSQL docs and checked only in SQLite.
