Introduction

Subqueries in SQL are a powerful concept used in advanced data analysis. A subquery is a query inside another SQL query that helps you perform complex operations and extract deeper insights from data. In this lesson, you will learn how to use subqueries in SQL for data analysis with practical examples.

What is a Subquery in SQL

A subquery is a query nested inside another query. It is used to retrieve data that will be used by the main query. Subqueries are commonly used in SELECT, WHERE, and FROM clauses.

Basic syntax:
SELECT column_name
FROM table_name
WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

Example of Subquery

Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

This query returns employees whose salary is higher than the average salary.

Types of Subqueries

Single Row Subquery

Returns only one value.

Example:
SELECT name FROM employees
WHERE department = (SELECT department FROM employees WHERE id = 1);

Multiple Row Subquery

Returns multiple values and uses operators like IN.

Example:
SELECT name FROM employees
WHERE department IN (SELECT department FROM departments);

Subquery with SELECT

Subqueries can also be used inside SELECT to display calculated values.

Example:
SELECT name,
(SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

Subquery with FROM Clause

You can use a subquery as a temporary table.

Example:
SELECT *
FROM (SELECT name, salary FROM employees) AS temp_table;

Why Subqueries are Important in Data Analysis

Subqueries help you:

• Perform complex filtering
• Compare values with aggregated results
• Simplify complex SQL queries
• Extract meaningful insights

Best Practices

• Keep subqueries simple and readable
• Avoid deeply nested subqueries
• Use joins when performance is critical
• Test subqueries separately

Common Mistakes

• Using subqueries when joins are more efficient
• Writing complex nested queries without optimization
• Forgetting aliases in subqueries

Summary

In this lesson, you learned how to use subqueries in SQL to perform advanced data analysis. Subqueries allow you to write powerful and flexible queries. In the next lesson, you will learn about nested queries and how to structure them efficiently.

FAQs

1. What is a subquery in SQL?
A subquery is a query inside another SQL query used to perform complex operations.

2. Where can subqueries be used?
They can be used in SELECT, WHERE, and FROM clauses.

3. Are subqueries better than joins?
Not always. Joins are often more efficient for large datasets.

4. Can subqueries return multiple values?
Yes, depending on the type of subquery used.

Internal Link

Want to explore more courses?
Click here for more free courses