Joins

Inner Join Customers

If you want the same idea in a DataFrame API, read pandas merge inner next. If you want the plain Python index-building mechanic first, read inner join by key.

An inner equi-join matches rows by key under SQL bag semantics: duplicate matches fan out and nonmatching keys disappear.

highlighted = computed this step

Orders input

Start with order rows. Two orders share the same customer key; one order has no matching customer.

Orders\text{Orders}
Ordersorder_idcustomer_idamounto1c1120o2c180o3c250

Customers input

Customers is also a bag: one key appears twice, so each matching order pairs with both rows.

Customers\text{Customers}
Customerscustomer_idnamec1Avac1Ava-Duplicatec3Cy

Inner join

INNER JOIN keeps only TRUE key matches: Orders.customer_id = Customers.customer_id.

Orders ⋈customer_id Customers\text{Orders}\ \bowtie_{\text{customer\_id}}\ \text{Customers}
Inner equi-joinjoin customer_id=customer_idarity 5 rows 4Ordersarity 3 rows 3Customersarity 2 rows 3

Joined output

The result has four rows: two matching orders times two customer rows; the unmatched order is dropped.

joined output\text{joined output}
Joined outputorder_idcustomer_idamountCustomers.customer_idnameo1c1120c1Avao1c1120c1Ava-Duplicateo2c180c1Avao2c180c1Ava-Duplicate