Subqueries and Views

Subqueries and Views

Subqueries and Views are advanced SQL concepts that help Business Analysts, Data Analysts, Business Intelligence Professionals, and Data Scientists perform complex data analysis and simplify reporting. As organizations grow, databases become larger and more complex. Analysts often need to answer sophisticated business questions that cannot be solved using simple SQL queries alone.

Subqueries allow one query to be placed inside another query, enabling advanced filtering, calculations, and data retrieval. Views provide virtual tables that simplify reporting and improve query reusability. Together, Subqueries and Views make SQL more powerful, efficient, and suitable for enterprise-level Business Analytics.

In this lesson, you will learn the fundamentals of Subqueries and Views, types of subqueries, view creation, business applications, performance considerations, best practices, and real-world examples.

What are Subqueries?

Subqueries and Views begin with understanding subqueries.

A Subquery is a query nested inside another SQL query.

A subquery executes first, and its result is used by the outer query.

General Structure:

SELECT column_name
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM another_table
);

Subqueries help solve complex analytical problems efficiently.

Why Subqueries are Important

Organizations use subqueries because they:

• Simplify complex queries
• Support advanced filtering
• Improve analytical capabilities
• Enable dynamic calculations
• Reduce manual processing

Subqueries are frequently used in business reporting and analytics.

Example Dataset

Employees Table

Suppose we want to find employees earning more than the average salary.

This is an ideal use case for a subquery.

Simple Subquery Example

Query:

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Result:

The subquery calculates the average salary first.

The outer query retrieves employees earning above that value.

How Subqueries Work

Execution process:

Step 1

Execute the inner query.

Step 2

Return a result.

Step 3

Pass the result to the outer query.

Step 4

Execute the outer query.

This sequence allows dynamic analysis.

Types of Subqueries

Subqueries can be categorized based on the results they return.

Single Row Subquery

Returns one value.

Example:

SELECT *
FROM Employees
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
);

Single-row subqueries are very common in reporting.

Multiple Row Subquery

Returns multiple values.

Example:

SELECT *
FROM Customers
WHERE City IN
(
    SELECT City
    FROM Branches
);

The inner query returns multiple cities.

The outer query retrieves matching customers.

Correlated Subquery

A correlated subquery depends on the outer query.

Example:

SELECT EmployeeName, Salary
FROM Employees E
WHERE Salary >
(
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentID = E.DepartmentID
);

The inner query executes for each row processed by the outer query.

Correlated subqueries support advanced analysis.

Subqueries with WHERE Clause

Subqueries are commonly used inside WHERE conditions.

Example:

SELECT ProductName
FROM Products
WHERE ProductID IN
(
    SELECT ProductID
    FROM Sales
);

This retrieves products that have been sold.

Business analysts frequently use this approach.

Subqueries with FROM Clause

Subqueries can also be used as temporary tables.

Example:

SELECT *
FROM
(
    SELECT ProductID,
    SUM(Revenue) AS TotalRevenue
    FROM Sales
    GROUP BY ProductID
) AS SalesSummary;

Benefits include:

• Cleaner logic
• Better organization

This technique is useful for complex reporting.

Subqueries with SELECT Clause

Subqueries can generate calculated values.

Example:

SELECT EmployeeName,
Salary,
(
    SELECT AVG(Salary)
    FROM Employees
) AS AverageSalary
FROM Employees;

This displays employee salaries alongside the company average.

Such comparisons are common in HR Analytics.

EXISTS Operator

EXISTS checks whether a subquery returns records.

Example:

SELECT CustomerName
FROM Customers C
WHERE EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

This retrieves customers who have placed orders.

EXISTS is often used for customer analysis.

NOT EXISTS Operator

NOT EXISTS identifies missing relationships.

Example:

SELECT CustomerName
FROM Customers C
WHERE NOT EXISTS
(
    SELECT 1
    FROM Orders O
    WHERE O.CustomerID = C.CustomerID
);

This retrieves customers without orders.

Business applications include:

• Inactive customers
• Products without sales
• Employees without projects

IN vs EXISTS

Understanding both approaches improves query optimization.

Business Applications of Subqueries

Subqueries support many analytical tasks.

Sales Analytics

Identify:

• Top-selling products
• Above-average sales

Customer Analytics

Identify:

• High-value customers
• Inactive customers

Financial Analytics

Analyze:

• Above-average profits
• Expense comparisons

HR Analytics

Analyze:

• High-performing employees
• Salary benchmarks

Subqueries provide flexibility in business reporting.

What are Views?

A View is a virtual table created from a SQL query.

Unlike physical tables:

• Views do not store data permanently.
• Views display data dynamically.

Views simplify reporting and improve reusability.

Why Views are Important

Organizations use views because they:

• Simplify complex queries
• Improve report consistency
• Enhance security
• Promote reusability
• Reduce query duplication

Views are widely used in Business Intelligence systems.

Creating a View

Syntax:

CREATE VIEW SalesSummary AS
SELECT ProductID,
SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY ProductID;

The view can then be queried like a regular table.

Example:

SELECT *
FROM SalesSummary;

Views simplify reporting workflows.

Viewing Data from a View

Once created:

SELECT *
FROM SalesSummary;

Users do not need to rewrite the underlying query.

This improves efficiency.

Updating a View

Some views support updates.

Example:

CREATE OR REPLACE VIEW SalesSummary AS
SELECT ProductID,
SUM(Revenue) AS TotalRevenue,
COUNT(*) AS TotalOrders
FROM Sales
GROUP BY ProductID;

Organizations often update views as reporting requirements change.

Dropping a View

To remove a view:

DROP VIEW SalesSummary;

This deletes the virtual table definition.

Source data remains unaffected.

Advantages of Views

Simplified Queries

Reduce complexity.

Improved Security

Restrict access to sensitive data.

Reusability

Use the same logic repeatedly.

Consistent Reporting

Ensure standard calculations.

Views improve enterprise reporting environments.

Views in Business Intelligence

Business Intelligence platforms frequently use views.

Examples:

• Power BI
• Tableau
• Looker
• Qlik

Views provide clean and standardized datasets for dashboards.

Many organizations create reporting views specifically for analytics teams.

Subqueries vs Views

Both techniques are valuable for Business Analytics.

Best Practices for Subqueries and Views

Keep Queries Readable

Use proper formatting.

Use Views for Repeated Logic

Reduce duplication.

Avoid Excessive Nesting

Improve maintainability.

Document Complex Views

Support collaboration.

Validate Outputs

Ensure accurate reporting.

These practices improve SQL quality.

Common Mistakes

Overusing Correlated Subqueries

May reduce performance.

Creating Too Many Views

Can complicate database management.

Ignoring Query Optimization

May slow reporting.

Poor Naming Conventions

Reduce readability.

Analysts should follow structured design practices.

Real-World Example

A retail company wants to identify products performing above average.

The analyst:

• Uses a subquery to calculate average revenue.
• Retrieves products exceeding that average.
• Creates a view summarizing product performance.

Management uses the view in Power BI dashboards and monthly reports.

This demonstrates the practical value of Subqueries and Views in Business Analytics.

Learning Outcomes

After completing this lesson, you will be able to:

• Understand SQL subqueries.
• Differentiate between subquery types.
• Use EXISTS and NOT EXISTS.
• Create and manage SQL views.
• Simplify reporting through reusable views.
• Apply advanced SQL techniques in Business Analytics.

Frequently Asked Questions (FAQs)

What is a subquery?

A subquery is a query nested inside another SQL query.

Why are subqueries useful?

They help perform advanced filtering, calculations, and data analysis.

What is a correlated subquery?

A correlated subquery references values from the outer query.

What is a view?

A view is a virtual table created from a SQL query.

Why are views important?

Views simplify reporting, improve security, and support reusable business logic.

Can views store data?

No. Standard views display data dynamically from underlying tables.

Which is better for reporting: subqueries or views?

Views are generally better for repeated reporting, while subqueries are useful for one-time analytical tasks.

Continue Learning

Click here for more free courses