SQL vs Spark SQL – Intermediate Cheat Sheet (Enhanced)
String Functions
Purpose SQL Spark SQL Description
Join text CONCAT() concat() Combine multiple strings
Substring SUBSTRING() substring() Extract part of a string
Uppercase UPPER() upper() Convert to uppercase
Lowercase LOWER() lower() Convert to lowercase
Length LENGTH() length() Count characters
Trim TRIM() trim() Remove leading/trailing spaces
Replace REPLACE() regexp_replace() Replace substring
Split SPLIT_STR() split() Split string into array
Pad LPAD()/RPAD() lpad()/rpad() Pad string to certain length
Date & Time Functions
Purpose SQL Spark SQL Description
Current date GETDATE() current_date() Get today's date
Current time CURRENT_TIMESTAMP current_timestamp() Current date and time
Add days DATEADD() date_add() Add days to date
Date difference DATEDIFF() datediff() Difference in days
Month difference MONTHS_BETWEEN() months_between() Difference in months
Extract date
Extract parts YEAR()/MONTH() year()/month()
components
Format date FORMAT() date_format() Format date as string
Aggregate Functions
Purpose SQL Spark SQL Description
Sum SUM() sum() Total of values
Average AVG() avg() Mean of values
Count rows COUNT(*) count(*) Number of rows
Distinct
COUNT(DISTINCT) count(distinct) Count unique values
count
Collect list GROUP_CONCAT() collect_list() Collect values into array
Collect unique values into
Collect set N/A collect_set()
array
Max/Min MAX()/MIN() max()/min() Maximum/Minimum value
Conditional & Null Handling
Purpose SQL Spark SQL Description
If null ISNULL() ifnull() Replace null with value
Coalesce COALESCE() coalesce() First non-null value
Case
CASE WHEN CASE WHEN Conditional logic
when
Null if NULLIF() nullif() Null if values equal
Replace null (Oracle-
NVL NVL() nvl()
style)
Window Functions
Purpose SQL Spark SQL Description
Unique sequential
Row number ROW_NUMBER() row_number()
number
Rank RANK() rank() Rank with gaps
Dense rank DENSE_RANK() dense_rank() Rank without gaps
Lead/Lag LEAD()/LAG() lead()/lag() Next/previous row value
First/Last FIRST_VALUE() first_value() First value in window
Cumulative
SUM() OVER() sum() OVER() Running total
sum
Data Types Comparison
Category SQL Types Spark SQL Types Notes
Integer INT , BIGINT INT , BIGINT Same
Decimal DECIMAL(p,s) DECIMAL(p,s) Precision & scale
Spark uses STRING for all
String VARCHAR(n) , TEXT STRING
text
Date/Time DATE , TIMESTAMP DATE , TIMESTAMP Same
Boolean BOOLEAN BOOLEAN Same
Array Limited support ARRAY<type> Native in Spark
Struct N/A STRUCT<field:type> Complex type in Spark
Key Differences
Aspect SQL Spark SQL Note
Execution Single server Distributed Spark runs on clusters
Data size GBs TBs/PBs Spark for big data
Language Pure SQL SQL + DataFrame API More flexible
Lazy
Aspect SQL Spark Optimizes before
No Yes SQL Note
evaluation execution
CSV, JSON, Parquet,
File support Database only Multiple formats
etc.
UDF support DB-specific Scala/Java/Python Custom functions easier
Interview Tips
1. Transfer Knowledge: Spark SQL follows SQL:2003 standard
2. Focus Areas :
Window functions (ranking, partitioning)
Date/time manipulation
Null handling functions
Aggregate with collect_list/set
3. Practice Both : SQL syntax and DataFrame API
4. Remember: Spark uses lazy evaluation
5. Know Formats : Parquet, Avro, ORC advantages