# SQL - Subqueries and CTEs (Data Analytics)

> Scalar subqueries, correlated subqueries, EXISTS, WITH (CTE), recursive CTEs, query refactoring

- 20 interview questions
- Mid-Level
- [Interview Questions: Data Analytics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions.md)

## 1. What is a subquery in SQL?

**Answer**

A subquery is a query nested inside another SQL query. It can appear in SELECT, FROM, WHERE, or HAVING clauses. The subquery executes first and its result is used by the outer query. This mechanism allows breaking down complex problems into successive logical steps.

## 2. In which SQL clause can a scalar subquery be used?

**Answer**

A scalar subquery returns exactly one value (one row, one column). Thanks to this property, it can be used in the SELECT clause as a computed column. It can also appear in WHERE for direct comparisons. Subqueries returning multiple rows cannot be used where a single value is expected.

## 3. What is the difference between a subquery and a join?

**Answer**

A subquery is an independent nested query that produces an intermediate result used by the outer query, while a join directly combines columns from multiple tables into a single result set. Joins are generally more performant for combining data, but subqueries are sometimes more readable for complex filters or intermediate calculations.

## 17 more questions available

- How to use the IN keyword with a subquery to filter results?
- What is a correlated subquery?

Sign up for free: https://sharpskill.dev/en/login

## Other Data Analytics interview topics

- [Google Sheets - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-basics.md): 20 questions, Junior
- [Google Sheets - Advanced Formulas](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-advanced.md): 20 questions, Junior
- [SQL - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-basics.md): 25 questions, Junior
- [SQL - Aggregations and Grouping](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-aggregations.md): 20 questions, Junior
- [SQL - Joins](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-joins.md): 20 questions, Junior
- [BigQuery - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/bigquery-fundamentals.md): 20 questions, Junior
- [Data Cleaning](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-cleaning.md): 20 questions, Junior
- [KPIs and Business Metrics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/kpis-metrics.md): 20 questions, Junior
- [Descriptive Statistics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/descriptive-statistics.md): 20 questions, Junior
- [Zapier and No-Code Automation](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/zapier-automation.md): 20 questions, Junior
- [Data Visualization Principles](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-viz-principles.md): 20 questions, Junior
- [Python & Pandas - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/python-pandas-basics.md): 20 questions, Junior
- [Google Sheets - Automated Dashboards](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/google-sheets-dashboards.md): 20 questions, Mid-Level
- [SQL - Window Functions](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-window-functions.md): 20 questions, Mid-Level
- [BigQuery - Advanced Features](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/bigquery-advanced.md): 20 questions, Mid-Level
- [Data Modeling](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/data-modeling.md): 20 questions, Mid-Level
- [Funnel and Conversion Analysis](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/funnel-analysis.md): 20 questions, Mid-Level
- [Cohort and Retention Analysis](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/cohort-retention.md): 20 questions, Mid-Level
- [Google Tag Manager and Tracking](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/gtm-tracking.md): 20 questions, Mid-Level
- [APIs and Webhooks](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/apis-webhooks.md): 20 questions, Mid-Level
- [dbt - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/dbt-fundamentals.md): 20 questions, Mid-Level
- [AB Testing and Applied Statistics](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/ab-testing.md): 20 questions, Mid-Level
- [Looker Studio (Google Data Studio)](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/looker-studio.md): 20 questions, Mid-Level
- [Power BI - Fundamentals](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/power-bi-fundamentals.md): 20 questions, Mid-Level
- [SQL - Advanced Analytical Queries](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-advanced-analytics.md): 20 questions, Senior
- [dbt - Advanced Features](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/dbt-advanced.md): 20 questions, Senior
- [Power BI - DAX and Advanced Dashboards](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/power-bi-dax.md): 20 questions, Senior
- [Python Analytics - Advanced Analysis and ML](https://sharpskill.dev/en/technologies/data-analytics/interview-questions/python-analytics-advanced.md): 20 questions, Senior

---

Source: SharpSkill (https://sharpskill.dev), tech interview preparation for your real stack.
HTML version of this page: https://sharpskill.dev/en/technologies/data-analytics/interview-questions/sql-subqueries-ctes