MSSQL Select Queries

Select Format

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]

SELECT select_list 

[ INTO new_table ]

[ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ WINDOW window expression]

[ ORDER BY order_expression [ ASC | DESC ]]

these can be combined with UNION, EXCEPT, and INTERSECT

Logical Order Processing of Select Statement

FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP

A Simple Select from a Table

SELECT * FROM mytable;

Use Top and a Where Clause with Between

SELECT TOP (1000) id, name, location, age
FROM myfriends
WHERE age BETWEEN 31 AND 49;

Use Greatest and IsNull to Eliminate Null and Low Entries

SELECT id, name, GREATEST(ISNULL(age, 0), 18) AS age FROM mypeople;

Use Distinct to Select Unique Entries from a Union

WITH n AS(SELECT name FROM dogs UNION SELECT name FROM cats)
SELECT DISTINCT name from n;

A Select with Group By

SELECT location, AVG(age) AS averageage
FROM myfriends
GROUP BY location;

A Select with a Subquery

SELECT name, location, age, (SELECT AVG(age) FROM myfriends) AS averageage
FROM myfriends;

A Select with a Subquery in a Where Clause

SELECT name FROM employees WHERE id NOT IN
(SELECT DISTINCT employees.managerId AS id FROM employees WHERE employees.managerId IS NOT NULL);

A Select with a Window Function

SELECT name, location, age, AVG(age) OVER (PARTITION BY location) AS averagelocage
FROM myfriends;

A Select with a Cross Join

SELECT name, location, age, A.averageage
FROM myfriends
CROSS JOIN (SELECT AVG(age) AS averageage FROM myfriends) AS A;

Use a Single Simple Common Table Expression (CTE) in a Select

WITH mydata AS (
  SELECT id, name FROM mytable
)
SELECT AVG(id) AS average
  FROM mydata;

Use a Common Table Expression (CTE) in a Select

WITH c AS(SELECT sessions.userId, COUNT(*) AS ct FROM sessions GROUP BY sessions.userId)
SELECT DISTINCT sessions.userId AS UserId,
	AVG(sessions.duration) OVER (PARTITION BY sessions.userId) AS AverageDuration FROM sessions
INNER JOIN c ON c.userId = sessions.userId WHERE c.ct > 1;

Use Two Common Table Expressions (CTE) in a Select

WITH myids AS (
  SELECT id FROM mytable
),
mynames AS (
  SELECT name FROM mytable
)
SELECT AVG(id) AS average, MAX(name) AS aname
  FROM myids, mynames;

Select with an Inner Join

SELECT n.name, a.age
FROM mytable AS n
INNER JOIN mytableages AS a
ON n.id = a.id;

Select with an Inner Join on a Subquery

SELECT name, t1.location, age, t2.averagelocage
FROM myfriends AS t1
INNER JOIN
(SELECT location, AVG(age) AS averagelocage FROM myfriends GROUP BY location) AS t2
ON t2.location = t1.location;

Select with an Not In Subquery

SELECT name FROM employees 
WHERE id NOT IN (
SELECT DISTINCT employees.managerId AS id FROM employees
WHERE employees.managerId IS NOT NULL);

Select with a Complex Query

WITH e AS (
SELECT employees.id, employees.stateId AS sid, ISNULL(SUM(sales.amount), 0) AS sales FROM employees
LEFT JOIN sales ON employeeId = employees.id GROUP BY employees.id, employees.stateId
),
r AS (SELECT regions.name, SUM(e.sales) AS su, GREATEST(COUNT(e.id), 1) AS empct FROM regions
LEFT JOIN states ON states.regionId = regions.id
LEFT JOIN e ON e.sid = states.id GROUP BY regions.name
),
t AS (SELECT name, (su/empct) AS average FROM r
)
SELECT t.name, t.average AS average, (M.mx - t.average) AS difference FROM t 
CROSS JOIN (SELECT MAX(t.average) AS mx FROM t) AS M;

Select with Another Complex Query

WITH regionsales AS (
  SELECT regions.name, COUNT(employees.id) AS ct, SUM(ISNULL(sales.amount, 0)) AS s FROM regions
  LEFT JOIN states ON regions.id = regionId
  LEFT JOIN employees ON stateId = states.id
  LEFT JOIN sales ON employeeId = employees.id GROUP BY regions.name
),
ave AS (SELECT name, (s/GREATEST(ct, 1)) AS a FROM regionsales),
maxave AS (SELECT MAX(a) AS ma FROM ave)

SELECT regionsales.name AS name, ave.a AS average, (SELECT maxave.ma - ave.a FROM maxave) AS difference FROM regionsales
JOIN ave ON ave.name = regionsales.name;