SQL CTE vs Subqueries

Common Table Expression(CTE):

A CTE is temporary result set that you define within a WITH clause.

It can be referenced multiple times within the main Query

Syntax:

WITH cte_name As ( 

Select col1, col2 from table_name where <Condition>

)

Select col1, col2 from cte_name where <condition>

SubQueries:

Subquery is nested query within the main query

Syntax:

Select col1, col2 from table_name where col3 in (select col3 from other_table where condition)

 

🔑 Key Differences:

🔹 Readability: CTEs are cleaner and easier to maintain in complex queries.
🔹 Re-usability: CTEs can be referenced multiple times, while subqueries are one-time use.
🔹 Recursion: CTEs shine in recursive queries, which subqueries don’t support.

💼 When to Use:

🔹 Use CTEs when you need to reuse logic, improve readability, or perform recursive operations.
🔹 Use Subqueries for simpler tasks where reuse isn’t needed.



Comments

Post a Comment

Popular posts from this blog

Databricks Auto Loader

Databricks Auto Loader Explained Databricks Auto Loader: Complete Explanation In Databricks, Auto Loader is a data ingestion tool designed to handle continuous and scalable data loading from cloud storage (such as AWS S3, Azure Blob Storage, or Google Cloud Storage) directly into Databricks’ Delta Lake. It’s particularly useful in data engineering and ETL (Extract, Transform, Load) pipelines for automatically managing new data as it arrives. Key Features of Databricks Auto Loader Incremental Data Ingestion : Auto Loader can incrementally ingest new files from a specified source location. It only processes files that have been added or updated, reducing processing time and costs. Schema Inference and Evolution : Auto Loader can automatically infer the schema of your data and evolve it over time as the structure of the data changes, reducing manual intervention. Highly Scalable and Optimized : Built for high scalability, Auto Lo...
Read more

Optimizing Spark memory - Resolving OutOfMemory (OOM)

Resolving OutOfMemory (OOM) Errors in PySpark: Best Practices Managing memory in PySpark is crucial for efficient data processing. Here are some best practices to avoid OOM errors: Adjust Spark Configuration (Memory Management) Increase Executor Memory: spark.conf.set("spark.executor.memory", "8g") Increase Driver Memory: spark.conf.set("spark.driver.memory", "4g") Set Executor Cores: spark.conf.set("spark.executor.cores", "2") Use Disk Persistence: df.persist(StorageLevel.DISK_ONLY) Enable Dynamic Allocation Allow Spark to adjust executors dynamically: spark.conf.set("spark.dynamicAllocation.enabled", "true") spark.conf.set("spark.dynamicAllocation.minExecutors", "1") Enable Adaptive Query Execution (AQE) Enable AQE to optimize query plans: spar...
Read more

PySpark tips for day-to-day use

  PySpark tips for day-to-day use: 1. Checkpointing 📝: Use `df.checkpoint()` to break the lineage and avoid long dependency chains in iterative jobs. 2. Partition Pruning 🌳: Leverage `df.filter("date = '2024-01-01'")` on partitioned data to skip unnecessary data scans. 3. Z-Ordering for Performance ⚡: In Delta tables, `OPTIMIZE` with `ZORDER BY` to speed up queries on frequently filtered columns. 4. Dynamic Partition Overwrite 🔄: Enable `spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")` for efficient partition updates. 5. Avoid Wide Transformations 📉: Minimize shuffles by reducing operations like `groupBy()` and `join()` when possible. 6. Use `mapPartitions` for Efficiency 🚀: When heavy computation is needed, process data in batches with `df.rdd.mapPartitions()`. 7. Memory-Efficient Joins 🧠: If data fits in memory, convert to RDD and use `rdd.collectAsMap()` for smaller joins. 8. Use `selectExpr()` for SQL E...
Read more