Free SQL Help for Devs, DBAs, and the Curious

Understanding and navigating hierarchical relationships in data is a common challenge for many database professionals. Whether you're dealing with organizational structures, product categories, or nested comments, hierarchical data can be difficult to wrangle without the right tools. Thankfully, SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1 and Azure SQL all support the use of Common Table Expressions (CTEs) to simplify the querying of hierarchical data.

A CTE is a named temporary result set that is only available within the scope of a single statement and can be self-referencing. This makes it a perfect tool for handling hierarchical data, where each row might reference another row within the same table. To illustrate, let's consider a simple employee hierarchy stored in an SQL Server database.

-- SQL code goes here

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    ManagerID INT
);

In this example, each employee can have a manager who is also an employee. To retrieve a report of all employees and who they report to, we could use a recursive CTE.

-- SQL code goes here

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, EmployeeName, ManagerID
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT E.EmployeeID, E.EmployeeName, E.ManagerID
    FROM Employees E
    INNER JOIN EmployeeHierarchy EH ON E.ManagerID = EH.EmployeeID
)


SELECT * FROM EmployeeHierarchy;

The CTE starts by selecting the top-level employees (those with no manager) and then recursively adds employees who report to those already in the result set. This continues until all employees have been added to the hierarchy.

Circling back to the technology front, MySQL 5.7 and DB2 11.1 also support recursive CTEs, though the syntax may vary slightly. In Azure SQL, CTEs are fully supported as well, providing a consistent experience across both on-premises and cloud-based SQL Server deployments.

In conclusion, dealing with hierarchical data need not be a complex task. By leveraging the power of Common Table Expressions, you can easily query and manipulate hierarchical data across a wide range of database systems including SQL Server 2016, SQL Server 2017, MySQL 5.7, DB2 11.1 and Azure SQL. Remember to always test your queries thoroughly to ensure accuracy and performance.