Joins

Left Join Missing Flag

LEFT JOIN keeps every order row, then a CASE expression turns unmatched right-side rows into an exact true/false flag.

highlighted = computed this step

Orders input

Start with orders, including one missing customer key and one NULL key.

Orders\text{Orders}
Ordersorder_idcustomer_idamounto1c1120o2c280o3c_missing50o4NULL40

Customers input

Customers only has known customer keys.

Customers\text{Customers}
Customerscustomer_idsegmentc1retailc2wholesale

Join query

LEFT JOIN keeps every order row, then CASE checks whether the right key is NULL.

LEFT JOIN then CASE flag\text{LEFT JOIN then CASE flag}
LEFT JOIN with customer_missing flagselect order_id,customer_id,amount,segment,customer_missingarity 5 rows 4left join customer_id=customer_idarity 5 rows 4Ordersarity 3 rows 4Customersarity 2 rows 2

Joined output

Unmatched right-side rows get customer_missing true; matched rows get false.

left rows plus missing flag\text{left rows plus missing flag}
Joined outputorder_idcustomer_idamountsegmentcustomer_missingo1c1120retailfalseo2c280wholesalefalseo3c_missing50NULLtrueo4NULL40NULLtrue