Advanced SQL Topics

Common Table Expressions (CTEs)

Common Table Expressions, or CTEs, are temporary named result sets that you can reference within a single SQL statement (SELECT, INSERT, UPDATE, or DELETE). They enhance readability and manageability of complex queries.

Syntax

WITH cte_name (column1, column2, ...) AS (
    -- SELECT statement that defines the CTE
    SELECT column1, column2, ...
    FROM your_table
    WHERE condition
)
-- Main query that uses the CTE
SELECT *
FROM cte_name
WHERE other_condition;

Benefits

• Improves query readability by breaking down complex logic.
• Enables recursive queries.
• Can be used to simplify subqueries.

Learn more about CTEs in detail.

Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions, they do not collapse rows into a single output row. Instead, they return a value for each row based on a group of rows defined by an "over" clause.

Types of Window Functions

• Aggregate Functions: SUM(), AVG(), COUNT(), MIN(), MAX() used with an OVER() clause.
• Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().
• Analytic Functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

Syntax Example (Ranking)

SELECT
    ProductName,
    Category,
    Price,
    RANK() OVER (PARTITION BY Category ORDER BY Price DESC) AS RankInCategory
FROM
    Products;

Explore all about Window Functions.

Recursive Queries

Recursive queries allow you to traverse hierarchical data structures, such as organizational charts or bill of materials. They are typically implemented using CTEs.

Example Structure

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: The starting point of the recursion
    SELECT EmployeeID, EmployeeName, ManagerID, 0 AS Level
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive Member: Joins back to the CTE
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeName, Level
FROM EmployeeHierarchy
ORDER BY Level, EmployeeName;

Understand how to write Recursive Queries.

Advanced Data Types and Features

Beyond standard numeric and string types, SQL databases support specialized data types and features for handling complex data.

JSON Support

Many modern SQL databases offer robust support for storing, querying, and manipulating JSON data natively.

• Functions like JSON_VALUE, JSON_QUERY, and OPENJSON.
• Indexing JSON columns for performance.

Spatial Data Types

For applications dealing with geographical information, spatial data types (e.g., GEOMETRY, GEOGRAPHY) are crucial.

• Storing points, lines, polygons.
• Performing spatial operations (e.g., distance, intersection).

Full-Text Search

Enhance your application's search capabilities with full-text indexing and querying.

Discover more about Advanced Data Types and Features.

Programmability and Extensibility

SQL databases are not just for querying; they offer powerful tools for application logic and extensibility.

Stored Procedures

Precompiled SQL code that can be executed on the database server, improving performance and encapsulating business logic.

Triggers

Special types of stored procedures that automatically execute in response to certain events on a table or view.

User-Defined Functions (UDFs)

Custom functions that can be written in SQL or other procedural languages to perform specific tasks.

Learn about Database Programmability.