Combining Tables with JOIN

from google.cloud import bigquery
import pandas as pd
 
# Authentication setup (when using service account key)
# import os
# os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = "path/to/your-key.json"
 
# Create BigQuery client
client = bigquery.Client(project='your-project-id')
 
# Query execution function
def run_query(query):
    return client.query(query).to_dataframe()

SELECT columns
FROM table_a
[INNER | LEFT | RIGHT | FULL OUTER | CROSS] JOIN table_b
ON table_a.key = table_b.key

Table A           Table B           INNER JOIN Result
+----+----+      +----+----+      +----+----+----+
| id | a  |      | id | b  |      | id | a  | b  |
+----+----+      +----+----+      +----+----+----+
| 1  | X  |      | 1  | P  |  →   | 1  | X  | P  |
| 2  | Y  |      | 3  | Q  |      | 3  | Z  | Q  |
| 3  | Z  |      | 4  | R  |      +----+----+----+
+----+----+      +----+----+

SELECT
    o.order_id,
    o.user_id,
    o.status,
    o.created_at,
    oi.product_id,
    oi.sale_price
FROM `your-project-id.thelook_ecommerce.orders` o
INNER JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
WHERE o.status = 'Complete'
LIMIT 10

Table A           Table B           LEFT JOIN Result
+----+----+      +----+----+      +----+----+------+
| id | a  |      | id | b  |      | id | a  | b    |
+----+----+      +----+----+      +----+----+------+
| 1  | X  |      | 1  | P  |  →   | 1  | X  | P    |
| 2  | Y  |      | 3  | Q  |      | 2  | Y  | NULL |
| 3  | Z  |      | 4  | R  |      | 3  | Z  | Q    |
+----+----+      +----+----+      +----+----+------+

SELECT
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.email,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(oi.sale_price), 0) AS total_spent
FROM `your-project-id.thelook_ecommerce.users` u
LEFT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
LEFT JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
GROUP BY u.id, u.first_name, u.last_name, u.email
ORDER BY total_spent DESC
LIMIT 15

SELECT
    u.id AS user_id,
    u.first_name,
    u.last_name,
    u.email,
    u.created_at AS signup_date
FROM `your-project-id.thelook_ecommerce.users` u
LEFT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
WHERE o.order_id IS NULL
ORDER BY u.created_at DESC
LIMIT 10

-- RIGHT JOIN (LEFT JOIN is more commonly used in practice)
SELECT
    o.order_id,
    o.status,
    u.first_name,
    u.last_name
FROM `your-project-id.thelook_ecommerce.users` u
RIGHT JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
LIMIT 10

-- All users and all orders (regardless of matching)
SELECT
    u.id AS user_id,
    u.first_name,
    o.order_id,
    o.status
FROM `your-project-id.thelook_ecommerce.users` u
FULL OUTER JOIN `your-project-id.thelook_ecommerce.orders` o
    ON u.id = o.user_id
WHERE u.id IS NULL OR o.order_id IS NULL  -- Only unmatched ones
LIMIT 10

-- All combinations of promotions and categories
WITH promotions AS (
    SELECT promotion_name FROM UNNEST(['Black Friday', 'Summer Sale', 'New Year']) AS promotion_name
),
categories AS (
    SELECT DISTINCT category
    FROM `your-project-id.thelook_ecommerce.products`
    LIMIT 5
)
SELECT
    p.promotion_name,
    c.category,
    CONCAT(p.promotion_name, ' - ', c.category) AS campaign_name
FROM promotions p
CROSS JOIN categories c
ORDER BY p.promotion_name, c.category

SELECT
    o.order_id,
    o.created_at AS order_date,
    o.status,
    u.first_name || ' ' || u.last_name AS customer_name,
    u.country,
    p.name AS product_name,
    p.category,
    p.brand,
    oi.sale_price,
    p.retail_price,
    ROUND(oi.sale_price - p.cost, 2) AS profit
FROM `your-project-id.thelook_ecommerce.orders` o
INNER JOIN `your-project-id.thelook_ecommerce.users` u
    ON o.user_id = u.id
INNER JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
INNER JOIN `your-project-id.thelook_ecommerce.products` p
    ON oi.product_id = p.id
WHERE o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 15

-- ❌ Bad example: Filtering after JOIN
SELECT *
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.created_at >= '2024-01-01'
 
-- ✅ Good example: Filter first with subquery
SELECT o.order_id, o.status, oi.sale_price
FROM (
    SELECT order_id, status
    FROM orders
    WHERE created_at >= '2024-01-01'
) o
JOIN order_items oi ON o.order_id = oi.order_id

-- Add EXPLAIN before the query
EXPLAIN
SELECT o.order_id, COUNT(oi.id)
FROM `your-project-id.thelook_ecommerce.orders` o
JOIN `your-project-id.thelook_ecommerce.order_items` oi
    ON o.order_id = oi.order_id
GROUP BY o.order_id

What data do you need?
├─ Must exist in both → INNER JOIN
├─ All of one side, attach other if exists → LEFT/RIGHT JOIN
├─ Need all of both sides → FULL OUTER JOIN
└─ Need all combinations → CROSS JOIN