N Things Worth Knowing About SELECT

  SELECT is our bedrock, our foundation, our now-and-forever T-SQL multitasker…and it’s one of the most complicated structures available to us.  Here are a few things worth knowing about it, from the basic to the obscure.

More-or-Less Basic Structure.
  There’s tons more that can go into the SELECT statement, of course, but the basic structure is:

WITH <CTE>
SELECT <columns>
FROM <table
INNER | OUTER | FULL JOIN <table> ON <criteria>
WHERE <criteria>
GROUP BY <column(s)>
HAVING <criteria>
ORDER BY <columns>

Interesting side note: Did you know that the HAVING clause does not require a GROUP BY clause? Of course then, the HAVING just behaves like a WHERE. Still, that could help you out on Quiz Bowl night at PASS.

And a more useful side note: In 2005 and later versions, you can ORDER BY columns that aren’t in your SELECT list. So I can SELECT FirstName, MiddleName FROM Table1 ORDER BY LastName, if I so choose.

Data Sources
You can SELECT data from a constant (such as “SELECT 100″), a variable, a table, temporary table, table variable, view, or table-valued function.

How SQL Server processes the SELECT statement
The BOL article on SELECT gives us the processing order of the select statement:

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

In other words, when SQL Server first looks at your select statement, it first wants to know where the data is coming from (FROM and ON). Then, if there are joins, it wants to know how to join them together…this limits or expands the resultset.  And so on.

Read more: SQLServerPedia

Posted via email from .NET Info