MySQL Database

9.

Advanced SQL Queries

9.1.

Subqueries (Nested Queries)

9.1.1.

Subquery Fundamentals

9.1.1.1.

Query Nesting Concepts

9.1.1.2.

Inner vs. Outer Queries

9.1.1.3.

Execution Order

9.1.2.

Subqueries in WHERE Clauses

9.1.2.1.

Scalar Subqueries

9.1.2.2.

Single Value Returns

9.1.2.3.

Comparison Operations

9.1.3.

Subqueries with IN/NOT IN

9.1.3.1.

List-Based Filtering

9.1.3.2.

Multiple Value Matching

9.1.3.3.

Null Value Considerations

9.1.4.

Subqueries in FROM Clauses

9.1.4.1.

9.1.4.2.

Temporary Result Sets

9.1.4.3.

Alias Requirements

9.1.5.

Subqueries in SELECT Clauses

9.1.5.1.

Calculated Columns

9.1.5.2.

Conditional Value Selection

9.1.5.3.

Performance Implications

9.1.6.

Correlated Subqueries

9.1.6.1.

Outer Query References

9.1.6.2.

Row-by-Row Evaluation

9.1.6.3.

EXISTS Operator Usage

9.1.7.

Subquery Performance

9.1.7.1.

Execution Plan Analysis

9.1.7.2.

Join Alternative Comparisons

9.1.7.3.

Optimization Strategies

9.2.

9.2.1.

UNION Operations

9.2.1.1.

Result Set Combination

9.2.1.2.

Column Compatibility

9.2.1.3.

Duplicate Handling

9.2.2.

9.2.2.1.

All Rows Inclusion

9.2.2.2.

Performance Benefits

9.2.2.3.

Duplicate Preservation

9.2.3.

UNION vs. UNION ALL

9.2.3.1.

Duplicate Elimination

9.2.3.2.

Performance Comparison

9.2.3.3.

Use Case Selection

9.2.4.

Set Operation Requirements

9.2.4.1.

Column Count Matching

9.2.4.2.

Data Type Compatibility

9.2.4.3.

ORDER BY Placement

9.3.

Common Table Expressions (CTEs)

9.3.1.

CTE Fundamentals

9.3.1.1.

WITH Clause Syntax

9.3.1.2.

Named Result Sets

9.3.1.3.

Query Readability

9.3.2.

9.3.2.1.

Single CTE Definition

9.3.2.2.

CTE Usage in Main Query

9.3.2.3.

Alias and Column Naming

9.3.3.

9.3.3.1.

Sequential CTE Definitions

9.3.3.2.

CTE Dependencies

9.3.3.3.

Complex Query Structuring

9.3.4.

9.3.4.1.

Recursive Query Structure

9.3.4.2.

Base Case Definition

9.3.4.3.

Recursive Case Logic

9.3.4.4.

Hierarchical Data Processing

9.4.

Window Functions

9.4.1.

Window Function Concepts

9.4.1.1.

Analytical Functions

9.4.1.2.

Partition-Based Calculations

9.4.1.3.

Row-Level Analysis

9.4.2.

9.4.2.1.

Window Specification

9.4.2.2.

Partition Definition

9.4.2.3.

Ordering Specification

9.4.3.

9.4.3.1.

PARTITION BY Clause

9.4.3.2.

Group-Based Calculations

9.4.3.3.

Partition Boundaries

9.4.4.

Ordering Within Windows

9.4.4.1.

ORDER BY in OVER

9.4.4.2.

Sort-Based Functions

9.4.4.3.

Frame Specifications

9.4.5.

Ranking Functions

9.4.5.1.

ROW_NUMBER Function

9.4.5.1.1.

Sequential Numbering

9.4.5.1.2.

Unique Row Identification

9.4.5.2.

9.4.5.2.1.

Tied Value Handling

9.4.5.2.2.

Gap-Based Ranking

9.4.5.3.

DENSE_RANK Function

9.4.5.3.1.

Consecutive Ranking

9.4.5.3.2.

9.4.6.

Aggregate Window Functions

9.4.6.1.

9.4.6.1.1.

9.4.6.1.2.

Progressive Calculations

9.4.6.2.

Moving Averages

9.4.6.2.1.

Window Frame Definitions

9.4.6.2.2.

Sliding Calculations

9.4.6.3.

Window Aggregate Functions

9.4.6.3.1.

9.4.6.3.2.

9.4.6.3.3.

9.4.7.

Frame Specifications

9.4.7.1.

9.4.7.2.

Frame Boundaries

9.4.7.3.

PRECEDING and FOLLOWING

Previous

8. Joining Tables

Go to top

Next

10. Indexing for Performance