SQL vs Pandas: A Comparative Guide for Data Analysts

If you came to Python from a SQL background — or the other way around — you’ve probably found yourself staring at a problem thinking, “I know exactly how to do this in SQL, but how do I translate it to pandas?” Or vice versa.

Both SQL and pandas are powerful data manipulation tools, but they think differently. SQL is declarative — you describe what you want. Pandas is imperative — you describe how to get it. Understanding the mapping between them makes you more effective in both.

This guide walks through common data operations side by side, from basic filtering to complex window functions.

Setup

All examples assume a table orders with columns: order_id, customer_id, order_date, amount, status, region.

In pandas, we load it as:

import pandas as pd

df = pd.read_csv("orders.csv")
# or from a database
# df = pd.read_sql("SELECT * FROM orders", engine)

Let’s also create a customers table: customer_id, name, signup_date, tier.

customers = pd.read_csv("customers.csv")

SELECT — Choosing Columns

SQL:

SELECT customer_id, amount FROM orders;

Pandas:

df[["customer_id", "amount"]]

SQL:

SELECT DISTINCT region FROM orders;

Pandas:

df["region"].unique()
# or as a DataFrame
df[["region"]].drop_duplicates()

WHERE — Filtering Rows

SQL:

SELECT * FROM orders WHERE status = 'shipped';

Pandas:

df[df["status"] == "shipped"]

SQL:

SELECT * FROM orders
WHERE amount > 100 AND status = 'pending';

Pandas:

df[(df["amount"] > 100) & (df["status"] == "pending")]

Key difference: SQL uses AND / OR keywords. Pandas uses & / | operators, and each condition must be wrapped in parentheses. The parentheses are not optional — operator precedence will break your query without them.

ORDER BY — Sorting

SQL:

SELECT * FROM orders ORDER BY amount DESC;

Pandas:

df.sort_values("amount", ascending=False)

SQL:

SELECT * FROM orders ORDER BY region, amount DESC;

Pandas:

df.sort_values(["region", "amount"], ascending=[True, False])

GROUP BY — Aggregation

SQL:

SELECT region, COUNT(*) as order_count, SUM(amount) as total_revenue
FROM orders
GROUP BY region;

Pandas:

df.groupby("region").agg(
    order_count=("order_id", "count"),
    total_revenue=("amount", "sum")
).reset_index()

SQL:

SELECT region, status, AVG(amount) as avg_amount
FROM orders
GROUP BY region, status;

Pandas:

df.groupby(["region", "status"])["amount"].mean().reset_index()

The .reset_index() at the end is important — without it, the grouped columns become the index instead of regular columns. This catches many newcomers off guard.

HAVING — Filtering Groups

SQL:

SELECT region, SUM(amount) as total
FROM orders
GROUP BY region
HAVING SUM(amount) > 10000;

Pandas:

result = df.groupby("region")["amount"].sum().reset_index()
result[result["amount"] > 10000]

Pandas has no direct HAVING equivalent. You filter the aggregated result afterward. This is actually more explicit — you can debug the intermediate aggregation before applying the filter.

JOIN — Combining Tables

SQL:

SELECT o.*, c.name, c.tier
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;

Pandas:

merged = df.merge(
    customers,
    on="customer_id",
    how="left"
)

Join types map directly:

SQL:

SELECT o.*, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.tier = 'gold';

Pandas:

merged = df.merge(customers, on="customer_id", how="left")
merged[merged["tier"] == "gold"]

Unlike SQL, pandas does not enforce a logical order of operations for WHERE after JOIN — you join first, then filter. This is actually more intuitive for many analysts.

Window Functions — Ranking and Partitioning

Window functions are where SQL and pandas diverge most significantly.

SQL:

SELECT *,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) as rank
FROM orders;

Pandas:

df["rank"] = df.groupby("region")["amount"].rank(
    method="first", ascending=False
)

SQL:

SELECT *,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_amount
FROM orders;

Pandas:

df["prev_amount"] = df.groupby("customer_id")["amount"].shift(1)

SQL:

SELECT *,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as running_total
FROM orders;

Pandas:

df["running_total"] = df.groupby("customer_id")["amount"].cumsum()

Pandas offers .rank(), .shift(), .cumsum(), .diff(), and .pct_change() as built-in window operations. For more complex windows, you can use .transform() with arbitrary functions.

CASE WHEN — Conditional Logic

SQL:

SELECT *,
    CASE
        WHEN amount > 500 THEN 'large'
        WHEN amount > 100 THEN 'medium'
        ELSE 'small'
    END as order_size
FROM orders;

Pandas:

conditions = [
    df["amount"] > 500,
    df["amount"] > 100,
]
choices = ["large", "medium"]
df["order_size"] = np.select(conditions, choices, default="small")

Or with pandas.cut() for numeric binning:

df["order_size"] = pd.cut(
    df["amount"],
    bins=[0, 100, 500, float("inf")],
    labels=["small", "medium", "large"]
)

UNION — Stacking Rows

SQL:

SELECT customer_id, amount FROM orders_2025
UNION ALL
SELECT customer_id, amount FROM orders_2026;

Pandas:

pd.concat([df_2025, df_2026], ignore_index=True)

Use pd.concat() with ignore_index=True for UNION ALL. For UNION (distinct), add .drop_duplicates().

The Thinking Difference

Beyond syntax, the mental model is different:

The most important mental shift: SQL thinks in sets, pandas thinks in columns and indices. When you master both, you can choose the right tool for the right problem.

When to Use Which

Use SQL when:

• The data lives in a database and is too large to fit in memory
• You need to join many large tables efficiently
• You’re working in an environment where SQL is the lingua franca (data warehouses, BI tools)
• Performance matters on the database side

Use pandas when:

• You need to clean, transform, or explore data interactively
• You’re integrating with Python’s ML ecosystem (scikit-learn, PyTorch, TensorFlow)
• You need complex logic that’s easier to express in Python than SQL
• You’re building automated data pipelines in Python
• You need to visualize data immediately with matplotlib or seaborn

Practical Tip: pandas Is Not a Database

A common mistake is treating pandas like a database. Pandas works in memory. If your dataset is 20 GB and your machine has 8 GB of RAM, you will crash. For large datasets, use SQL for the heavy lifting (aggregation, filtering, joining) and only bring the summarized result into pandas for analysis and visualization.

Conclusion

SQL and pandas are complementary, not competing. The best data analysts speak both fluently and know when to reach for each.

A few principles to carry forward:

• Learn the mapping — every SQL operation has a pandas equivalent. Knowing the translation table makes you productive in both.
• Think in steps — pandas is procedural. Break complex operations into small, testable steps.
• Respect the index — pandas’ index is powerful but confusing. Understand when to use .set_index(), .reset_index(), and how .loc[] vs .iloc[] differ.
• Prefer vectorized operations — pandas is optimized for column-wise operations. Loops are slow. If you’re writing a for loop over a DataFrame, there’s probably a better way.

The goal is not to replace SQL with pandas or vice versa. It’s to have both tools in your belt so you can reach for the right one when the problem demands it.

~ Kang Ifaz