SQL vs DataFrame API

Apache Spark · Spark SQL

Theory

Spark SQL strings and DataFrame method chains are two front-ends to the same engine. Both are parsed into a logical plan, optimized by Catalyst and executed by the same physical operators, so a query written either way has the same performance. spark.sql("SELECT region, SUM(amount) FROM orders GROUP BY region") and orders.groupBy('region').agg(F.sum('amount')) produce equivalent plans.

The difference is in how you write and maintain the code. SQL is familiar, concise for joins and aggregations, and easy for analysts to read. The DataFrame API is plain Python: you can build queries in functions and loops, reuse column expressions, pass DataFrames between functions, unit-test steps, and get some errors earlier, such as calling a method that does not exist. Most teams mix them, for example using SQL for a complex reporting query and DataFrame code for a dynamic pipeline that loops over 20 column names.

Why we use it

Interviewers often ask which is faster; the answer is neither, because both compile to the same optimized plan. Knowing this lets you choose by readability and maintainability instead of myths about performance.

When to use it

Use SQL for set-based queries, reports and code shared with SQL users. Use the DataFrame API when logic is dynamic (generated columns, loops, parameters), when you want reusable Python functions and tests, or when calling DataFrame-only features. Mixing both in one pipeline is normal.

How it works

  1. Need a query
  2. Is the logic static and set-based? Yes: Write it in SQL. No: Write it with the DataFrame API.
  3. Both are parsed into a logical plan
  4. Catalyst optimizes the plan
  5. Same physical execution

Syntax

spark.sql("SELECT region, SUM(amount) AS total FROM orders GROUP BY region")
orders.groupBy("region").agg(F.sum("amount").alias("total"))
F.expr("amount * qty")   /   df.selectExpr("...")   # SQL snippets in DataFrame code

Example

from pyspark.sql import SparkSession, functions as F

spark = (SparkSession.builder.master("local[1]")
         .config("spark.ui.enabled", "false")
         .getOrCreate())

orders = spark.createDataFrame([
    (101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
    (102, 2, "West",  "Phone",  2, 30000.0, "2026-01-06"),
    (103, 1, "North", "Mouse",  3,  1500.0, "2026-01-06"),
    (104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
    (105, 4, "West",  "Tablet", 1, 25000.0, "2026-01-08"),
    (106, 2, "East",  "Phone",  1, 15000.0, "2026-01-08"),
    (107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
    (108, 3, "South", "Mouse",  4,  2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
   "qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))

orders.createOrReplaceTempView("orders")

sql_df = spark.sql("""
    SELECT region, SUM(amount) AS total
    FROM orders WHERE qty >= 1
    GROUP BY region ORDER BY total DESC""")

api_df = (orders.filter(F.col("qty") >= 1)
                .groupBy("region").agg(F.sum("amount").alias("total"))
                .orderBy(F.desc("total")))

print("same result:", sql_df.collect() == api_df.collect())
api_df.show()

# Dynamic logic is easier in Python: one total per product, built in a loop
products = ["Laptop", "Phone", "Tablet"]
exprs = [F.sum(F.when(F.col("product") == p, F.col("amount")).otherwise(0)).alias(p) for p in products]
orders.agg(*exprs).show()

# Output:
# same result: True
# +------+--------+
# |region|   total|
# +------+--------+
# | North|104500.0|
# |  West| 55000.0|
# | South| 54000.0|
# |  East| 15000.0|
# +------+--------+
#
# +--------+-------+-------+
# |  Laptop|  Phone| Tablet|
# +--------+-------+-------+
# |107000.0|45000.0|73000.0|
# +--------+-------+-------+

Key points

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic