Advanced Analytics

Relational Logic: Joining Datasets

In global organizations, data is rarely in one place. You might have a Users table in a European server and a Transactions table in an Asian server. To build a global report, you must master the “Key-Value” relationship.

Inner Join Logic

This is the “Strict” filter. It only returns records where the ID exists in both datasets. If a user exists but has never made a purchase, they vanish from the result.

Left Join Logic

This is the “Inclusive” filter. It keeps every record from your primary table. If there is no matching record in the secondary table, SQL simply fills the gap with NULL.

Table A
(Offices)

INNER JOIN

Table B
(Leads)

Left Join = Table A + The Overlap

Global Challenge

Prompt: We need a list of all Global Project Leads and the Regional Offices they manage. Important: We must include offices that are currently vacant (have no lead assigned).

QUERY_V2.SQL

SELECT r.office_location, p.lead_name
FROM regional_offices r
LEFT JOIN project_leads p ON r.office_id = p.office_id;

VIRTUAL TABLE OUTPUT SUCCESS: 3 ROWS RETURNED

office_location	lead_name
Tokyo, JP	Hiroshi Tanaka
Nairobi, KE	Amara Okafor
Mexico City, MX	NULL (Vacant)

Analysis: By using a LEFT JOIN, we ensured that the Mexico City office appeared in our report even though Mateo (the previous lead) has left. An INNER JOIN would have completely hidden this location from the management team!