MySQL Database

  1. Advanced SQL Queries
    1. Subqueries (Nested Queries)
      1. Subquery Fundamentals
        1. Query Nesting Concepts
          1. Inner vs. Outer Queries
            1. Execution Order
            2. Subqueries in WHERE Clauses
              1. Scalar Subqueries
                1. Single Value Returns
                  1. Comparison Operations
                  2. Subqueries with IN/NOT IN
                    1. List-Based Filtering
                      1. Multiple Value Matching
                        1. Null Value Considerations
                        2. Subqueries in FROM Clauses
                          1. Derived Tables
                            1. Temporary Result Sets
                              1. Alias Requirements
                              2. Subqueries in SELECT Clauses
                                1. Calculated Columns
                                  1. Conditional Value Selection
                                    1. Performance Implications
                                    2. Correlated Subqueries
                                      1. Outer Query References
                                        1. Row-by-Row Evaluation
                                          1. EXISTS Operator Usage
                                          2. Subquery Performance
                                            1. Execution Plan Analysis
                                              1. Join Alternative Comparisons
                                                1. Optimization Strategies
                                              2. Set Operations
                                                1. UNION Operations
                                                  1. Result Set Combination
                                                    1. Column Compatibility
                                                      1. Duplicate Handling
                                                      2. UNION ALL
                                                        1. All Rows Inclusion
                                                          1. Performance Benefits
                                                            1. Duplicate Preservation
                                                            2. UNION vs. UNION ALL
                                                              1. Duplicate Elimination
                                                                1. Performance Comparison
                                                                  1. Use Case Selection
                                                                  2. Set Operation Requirements
                                                                    1. Column Count Matching
                                                                      1. Data Type Compatibility
                                                                        1. ORDER BY Placement
                                                                      2. Common Table Expressions (CTEs)
                                                                        1. CTE Fundamentals
                                                                          1. WITH Clause Syntax
                                                                            1. Named Result Sets
                                                                              1. Query Readability
                                                                              2. Simple CTEs
                                                                                1. Single CTE Definition
                                                                                  1. CTE Usage in Main Query
                                                                                    1. Alias and Column Naming
                                                                                    2. Multiple CTEs
                                                                                      1. Sequential CTE Definitions
                                                                                        1. CTE Dependencies
                                                                                          1. Complex Query Structuring
                                                                                          2. Recursive CTEs
                                                                                            1. Recursive Query Structure
                                                                                              1. Base Case Definition
                                                                                                1. Recursive Case Logic
                                                                                                  1. Hierarchical Data Processing
                                                                                                2. Window Functions
                                                                                                  1. Window Function Concepts
                                                                                                    1. Analytical Functions
                                                                                                      1. Partition-Based Calculations
                                                                                                        1. Row-Level Analysis
                                                                                                        2. OVER Clause
                                                                                                          1. Window Specification
                                                                                                            1. Partition Definition
                                                                                                              1. Ordering Specification
                                                                                                              2. Partitioning
                                                                                                                1. PARTITION BY Clause
                                                                                                                  1. Group-Based Calculations
                                                                                                                    1. Partition Boundaries
                                                                                                                    2. Ordering Within Windows
                                                                                                                      1. ORDER BY in OVER
                                                                                                                        1. Sort-Based Functions
                                                                                                                          1. Frame Specifications
                                                                                                                          2. Ranking Functions
                                                                                                                            1. ROW_NUMBER Function
                                                                                                                              1. Sequential Numbering
                                                                                                                                1. Unique Row Identification
                                                                                                                                2. RANK Function
                                                                                                                                  1. Tied Value Handling
                                                                                                                                    1. Gap-Based Ranking
                                                                                                                                    2. DENSE_RANK Function
                                                                                                                                      1. Consecutive Ranking
                                                                                                                                        1. No Gap Ranking
                                                                                                                                      2. Aggregate Window Functions
                                                                                                                                        1. Running Totals
                                                                                                                                          1. Cumulative SUM
                                                                                                                                            1. Progressive Calculations
                                                                                                                                            2. Moving Averages
                                                                                                                                              1. Window Frame Definitions
                                                                                                                                                1. Sliding Calculations
                                                                                                                                                2. Window Aggregate Functions
                                                                                                                                                  1. COUNT OVER
                                                                                                                                                    1. AVG OVER
                                                                                                                                                      1. MIN/MAX OVER
                                                                                                                                                    2. Frame Specifications
                                                                                                                                                      1. ROWS vs. RANGE
                                                                                                                                                        1. Frame Boundaries
                                                                                                                                                          1. PRECEDING and FOLLOWING

                                                                                                                                                      Previous

                                                                                                                                                      8. Joining Tables

                                                                                                                                                      Go to top

                                                                                                                                                      Next

                                                                                                                                                      10. Indexing for Performance