I'm using Python and pandas to run a SQL query, then filter the returned data. For example:
query = "SELECT customer_id, amount FROM orders"
df = pd.read_sql(query, connection)
df = df[df["amount"] > 1000]
Alternatively, I can apply the filter in the database:
query = "SELECT customer_id, amount FROM orders WHERE amount > 1000"
df = pd.read_sql(query, connection)
These seem like they should return the same rows, but are there situations where they can differ? I'm particularly wondering about NULL values, data types, date/time conversion, floating-point precision, database-specific behavior, and result ordering. In general, which kinds of logic are better kept in SQL, and which are more appropriate for Python?
4 Answers
First determine what actually differs: the row count, column values, data types, or only the order. Neither query guarantees a particular order unless you add an `ORDER BY` clause. Even when both queries return the same rows, changing the execution plan can change the order in which those rows appear.
A useful debugging approach is to compare the two result sets directly: identify rows present in the SQL-filtered result but missing from the pandas result, and vice versa. Inspect the problematic `amount` values, their database types, pandas dtypes, missing-value representations, and exact floating-point values. That usually reveals whether the difference comes from conversion, precision, NULL handling, or ordering.
For a simple comparison like this, the results will usually match, but NULL handling and type conversion are worth checking. In SQL, `amount > 1000` evaluates to UNKNOWN for NULL and the row is excluded. In pandas, missing values generally also fail a numeric comparison, but behavior can vary depending on the column's dtype and how the database driver converted the values. Floating-point rounding can also matter if the database and Python represent the value differently.
I would push straightforward filtering into SQL whenever practical. The database can use indexes and process the rows close to where they are stored, so fewer records need to be transferred to Python. Python is a better place for complicated application logic, transformations that pandas handles more naturally, or operations involving multiple data sources.
That makes sense for filters that significantly reduce the result set. If the filter barely reduces the data or is easier to express and maintain in pandas, doing it after retrieval may be reasonable.