SoftOve

Database Tools for SQL Server

Structured and recursive SQL using WITH

As until recently I had never seen or heard about the WITH clause. Since I believe there are many as me I will give some examples of how to use it. The WITH clause can be used for giving subqueries names and to reuse them later in your query. An easy example is given below where we have two subqueries. One for getting the average salary for each department and one for getting the average salary at the company.

/* Add some employees to use with the example. */
DECLARE @emps TABLE (emp_number INT, emp_name NVARCHAR(100), emp_department VARCHAR(50), emp_salary INT)
INSERT INTO @emps VALUES(1, 'Anders Andersson', 'Sales', 20000)
INSERT INTO @emps VALUES(2, 'Bengt Bengtsson', 'Sales', 20000)
INSERT INTO @emps VALUES(3, 'Claes Claesson', 'Sales', 21000)
INSERT INTO @emps VALUES(4, 'David Davidsson', 'Development', 21000)
INSERT INTO @emps VALUES(5, 'Erik Eriksson', 'Development', 23000)
INSERT INTO @emps VALUES(6, 'Fredrik Fredriksson', 'Development', 24000)
INSERT INTO @emps VALUES(7, 'Hans Hansson', 'Administration', 21000)
INSERT INTO @emps VALUES(8, 'Gustav Gustavsson', 'Administration', 22000)
INSERT INTO @emps VALUES(9, 'Ingvar Ingvarsson', 'Administration', 24000)

/* Define a subquery for getting salary average per department. */
;WITH department AS
(
	SELECT emp_department AS department_name, AVG(emp_salary) AS department_average 
	FROM @emps GROUP BY emp_department
)

/* Define a subquery for getting salary average in company. */
, company AS
(
	SELECT AVG(emp_salary) AS company_average 
	FROM @emps 
)		

/* Get each employee and their salaries compared to the averages. */
SELECT 
	emp_name
	,emp_department
	,emp_salary
	,emp_salary - department.department_average AS emp_salary_compared_to_department_average
	,emp_salary - company.company_average AS emp_salary_compared_to_company_average
FROM
	@emps

	INNER JOIN department ON emp_department = department_name
	CROSS JOIN company


Another usage of the WITH clause is to make recursive queries. In the first examples below we get each department above the department "London" in a company organization. In the second example we get each department below "London".

/* Add some department to use with the example. */
DECLARE @deps TABLE (dep_id INT, dep_name NVARCHAR(100), dep_parent INT)
INSERT INTO @deps VALUES(1, 'Europe', NULL)
INSERT INTO @deps VALUES(2, 'Asia', NULL)
INSERT INTO @deps VALUES(3, 'UK', 1)
INSERT INTO @deps VALUES(4, 'Germany', 1)
INSERT INTO @deps VALUES(5, 'China', 2)
INSERT INTO @deps VALUES(6, 'India', 2)
INSERT INTO @deps VALUES(7, 'London', 3)
INSERT INTO @deps VALUES(8, 'Newham', 7)
INSERT INTO @deps VALUES(9, 'Hackney', 7)
INSERT INTO @deps VALUES(10, 'Abbey Street', 8)

/* Define a recursive subquery to get all departments above London. */
;WITH above AS
(
	SELECT dep_id, dep_name, dep_parent 
	FROM @deps WHERE dep_name = 'London'
	
	UNION ALL
	
	SELECT p.dep_id, p.dep_name, p.dep_parent 
	FROM above AS c INNER JOIN @deps AS p ON c.dep_parent = p.dep_id
)
SELECT dep_id, dep_name, dep_parent FROM above

/* Define a recursive subquery to get all departments below London. */
;WITH below AS
(
	SELECT dep_id, dep_name, dep_parent 
	FROM @deps WHERE dep_name = 'London'
	
	UNION ALL
	
	SELECT c.dep_id, c.dep_name, c.dep_parent 
	FROM below AS p INNER JOIN @deps AS c ON c.dep_parent = p.dep_id
)
SELECT dep_id, dep_name, dep_parent FROM below

I should mention that there is an alternative, and maybe more recommended way of working with hierarchical structures in SQL Server. Read more about this in the article Model Your Data Hierarchies With SQL Server 2008.


Name Comment Send