Detailed description of select statements and their clauses

Select
Retrieves rows from a database and allows you to select one or more rows or columns from one or more tables. Although the complete syntax of the SELECT statement is complex, its main clauses can be summarized as follows:

Select select_list
[Into new_table]
From table_source
[Where search_condition]
[Group by group_by_expression]
[Having search_condition]
[Order by order_expression [ASC | DESC]

you can use the Union operator between queries to combine the query results into a single result set.
syntax
SELECT statement :: =

[order by {order_by_expression | column_position [ASC | DESC]}
[,... n]
[compute
{{ AVG | count | max | min | sum} (expression )}[,... n]
[by expression [,... n]
[For {browse | XML {raw | auto | explicit}
[, xmldata]
[, elements]
[, binary base64]
}< br>]
[Option ( [,... n])]

<Query expression >::=
{<Query specification >|( <query expression> )}
[Union [all] <query specification | (<query expression>) [... n]

<Query specification >::=
Select [All | distinct]
[{Top integer | top integer percent} [with ties]
<Select_list>
[Into new_table]
[From {<table_source>} [,... n]
[Where <search_condition>]
[Group by [all] group_by_expression [,... n]
[With {cube | rollup}]
]
[Having <search_condition>]
Due to the complexity of the SELECT statement, the following statements describe the detailed syntax elements and parameters:

Select clause
Specifies the columns returned by the query.
Syntax
Select [All | distinct]
[Top N [percent] [with ties]
<Select_list>

<Select_list >::=

{*
| {Table_name | view_name | table_alias }.*
| {Column_name | expression | identitycol | rowguidcol}
[[As] column_alias]
| Column_alias = expression
} [,... N]

Parameters
All
Indicates that duplicate rows can be displayed in the result set. All is the default setting.
Distinct
Only unique rows can be displayed in the result set. For the purpose of the distinct keyword, null values are considered equal.
Top N [percent]
Specify to output only the first n rows from the query result set. N is an integer between 0 and 4294967295. If percent is also specified, only the first n rows are output from the result set. When percent is specified, N must be an integer between 0 and 100.
If the query contains the order by clause, the first n rows (or the first n %) sorted by the order by clause are output ). If the query does not have an order by clause, the order of rows is arbitrary.
With ties
Specify to return the additional rows from the basic result set. These rows contain the same value as the value in the order by column at the end of the Top N (percent) Row. If the order by clause is specified, only top... with ties can be specified.
<Select_list>
The column selected for the result set. The selection list is a series of expressions separated by commas.
*
Returns all columns in all tables and views in the from clause. Columns are returned by the table or view specified by the from clause and in the order they are returned in the table or view.
Table_name | view_name | table_alias .*
Restrict * to a specified table or view.
Column_name
Is the name of the column to be returned. Limit column_name to avoid binary reference. This occurs when two tables in the from clause contain columns with duplicate names. For example, columns named columnid in the customers and orders tables of the northwind database. If the two tables are joined in the query, you can specify the customer ID as MERS MERs. customerid in the selection list.
Expression
It is an arbitrary combination of column names, constants, functions, column names connected by operators, constants, and functions, or subqueries.
Identitycol
Returns the ID column. For more information, see identity (attributes), alter table, and create table.
If multiple tables in the from clause have columns containing the identity attribute, you must use a specific table name (such as t1.identitycol) to limit identitycol.
Rowguidcol
Returns the Globally Unique Identifier column of a row.
If multiple tables in the from clause have the rowguidcol attribute, you must use a specific table name (such as t1.rowguidcol) to limit rowguidcol.
Column_alias
Is an optional name to replace column names in the query result set. For example, you can specify an alias for a column named quantity, such as "quantity", "quantity to date", or "QTY ".
An alias can also be used to specify a name for the expression result, for example:
Use northwind
Select AVG (unitprice) as 'average price'
From [Order Details]
Column_alias can be used in the order by clause. However, it cannot be used in where, group by, or having clauses. If the query expression is part of the declare cursor statement, column_alias cannot be used in the for update clause.

Into clause
Create a new table and insert the result row from the query to the new table.
To execute a SELECT statement with an into clause, you must have the create table permission in the target database. Select... into cannot be used with the compute clause. For more information, see transactions and explicit transactions.
By including the false condition in the WHERE clause, you can use Select... into to create the same table definition without data (different table names ).
Syntax
[Into new_table]
Parameters
New_table
Specify the name of the table to be created based on the row selected by the column in the selection list and the row selected by the WHERE clause. The format of new_table is determined by the value of the expression in the selection list. The columns in new_table are created in the order specified in the selection list. Each column in new_table has the same name, data type, and value as the corresponding expression in the selection list.
When the selected list contains a computed column, the corresponding column in the new table is not a computed column. The values in the new column are calculated when Select... into is executed.
In the SQL Server version released this time, the Select into/bulkcopy database option does not affect whether select into can be used to create permanent tables. The number of records for some large-capacity operations, including select into, depends on the effective recovery mode for the database. For more information, see use recovery model.
In earlier versions, if select into/bulk copy is set, select into can be used to create a permanent table.
Select into/bulk copy is used for backward compatibility, but this option may not be supported in future versions. For more information, see the recovery mode and backward compatibility topics and the alter database topic.

From clause
Specifies the table from which the row is retrieved. The from clause is required unless the selection list contains only constants, variables, and arithmetic expressions (no column name ). For more information, see from.
Syntax
[From {<table_source>} [,... n]

<Table_source >::=
Table_name [[as] table_alias] [With (<table_hint> [,... n])]
| View_name [[as] table_alias]
| Rowset_function [[as] table_alias]
| Openxml
| Derived_table [as] table_alias [(column_alias [,... n])]
| <Joined_table>

<Joined_table >::=
<Table_source> <join_type> <table_source> On <search_condition>
| <Table_source> cross join <table_source>
| <Joined_table>

<Join_type >::=
[Inner | {left | right | full} [outer]}]
[<Join_hint>]
Join
Parameters
<Table_source>
Specifies the table, view, derived table, and join table used for the SELECT statement.
Table_name [[as] table_alias]
Specifies the table name and optional alias.
View_name [[as] table_alias]
Specifies the name, view, and optional alias.
Rowset_function [[as] table_alias]
Is the name and optional alias of a function in a dataset. For more information about the list of function sets, see function sets.
Openxml
Provides the row set view on the XML document. For more information, see openxml.
With (<table_hint> [,... n])
Specify one or more table prompts. For more information about table prompts, see from.
Derived_table [[as] table_alias]
Is a nested SELECT statement that retrieves rows from a specified database or table.
Column_alias
Replace the optional alias of the column name in the result set.
<Joined_table>
Is the result set of the product of two or more tables. For example:
Select *
From tab1 left Outer Join tab2 on tab1.c3 = tab2.c3
Right Outer Join tab3 left Outer Join tab4
On tab3.c1 = tab4.c1
On tab2.c3 = tab4.c3
For multiple cross joins, use parentheses to change the natural order of the joins.
<Join_type>
Specifies the type of the join operation.
Inner
Returns all matched row pairs. Discard the rows that do not match the two tables. If no join type is specified, this is the default setting.
Left [outer]
Specify that, except all rows returned by the inner join, all rows from the left table that do not meet the specified conditions are included in the result set. The output column from the left table is set to null.
Right [outer]
Specify that, except all rows returned by the inner join, all rows from the right table that do not meet the specified conditions are included in the result set. The output column from the right table is set to null.

Full [outer]
If a row from the left or right table does not match the selection criterion, the row is included in the result set and the output column corresponding to the other table is set to null. In addition, the result set contains all rows normally returned by the inner join.
<Join_hint>
Connection prompt or executionAlgorithm. If <join_hint> is specified, you must specify inner, left, right, or full. For more information about the connection prompt, see from.
Join
Join the specified table or view.
On <search_condition>
Specify the condition on which the join is based. This condition specifies any predicate, but usually uses columns and comparison operators. For example:
Select productid, suppliers. supplierid
From suppliers join Products
On (suppliers. supplierid = products. supplierid)
When the condition specifies a column, the column does not have to have the same name or data type. However, if the data types are different, they must be compatible or? SQL Server? Implicit conversion type. If the data type cannot be implicitly converted, the cast function must be used to convert the Data Type explicitly.
For more information about search criteria and predicates, see search criteria.
Cross join
Specify the product of the two tables. Returns the same row, just as the table to be joined is only listed in the from clause, and the where clause is not specified. For example, the result sets returned by the two queries are completely connected by all rows in T1 and T2:
Select * from T1, T2
Select * from T1 cross join T2

Where clause
Specify the search conditions used to restrict the returned rows.
Syntax
[Where <search_condition> | <old_outer_join>]

<Old_outer_join >::=
Column_name {* = | = *} column_name

Parameters
<Search_condition>
Use a predicate to limit the rows returned in the result set. There is no limit on the number of predicates that a search condition can contain. For more information about search criteria and predicates, see search criteria.
<Old_outer_join>
Use the non-standard product-specific syntax and the where clause to specify the outer join. * = Operator is used to specify the left Outer Join, and = * operator is used to specify the right outer join.
In the following example, a left Outer Join is specified and the result set contains rows in tab1 that do not meet the specified conditions:
Select tab1.name, tab2.id
From tab1, tab2
Where tab1.id * = tab2.id
This syntax has potential ambiguity and is not standard, so it is not recommended to use this syntax for external join. Instead, specify the join in the from clause.
You can specify an external join by using the join operator in the from clause or by using an unstandard * = and = * operator in the WHERE clause. You cannot use both methods in the same statement.

Group by clause
Specifies the group to place the output rows. If the select clause <select list> contains an aggregate function, the aggregate value of each group is calculated. When group by is specified, all columns in any non-aggregate expression in the selection list should be included in the group by list, or the group by expression must exactly match the expression in the selection list.
If the order by clause is not specified, the group by clause is not returned in any specific order. We recommend that you always use the order by clause to specify the specific data order.
Syntax
[Group by [all] group_by_expression [,... n]
[With {cube | rollup}]
]
Parameters
All
Contains all groups and result sets, and even groups and result sets that do not meet the search criteria specified by the WHERE clause. If all is specified, a null value is returned for the summary column that does not meet the search conditions in the group. You cannot use the cube or rollup operator to specify all.
If a where clause exists in the query that accesses a remote table, the group by all operation is not supported.
Group_by_expression
Is the expression for grouping execution. Group_by_expression is also called a group column. Group_by expression can be a non-aggregate expression of a column or referenced column. The column alias defined in the selected list cannot be used to specify the group column.
It indicates that columns of the text, ntext, and image types cannot be used in group_by_expression.
For group by clauses that do not contain cube or rollup, the number of group_by_expression items is limited by the size of the group by column, the aggregate column, and the aggregate value involved in the query. This limit starts from the limit of 8,060 bytes. It is limited to 8,060 bytes for the intermediate worksheet required to save the intermediate query results. If cube or rollup is specified, a maximum of 10 grouping expressions are allowed.
Cube
Specify that the result set contains not only normal rows provided by group by, but also summary rows. Return the group by summary rows of each possible group and sub-group combination in the result set. The group by summary row is displayed as null in the result, but can be used to indicate all values. Use the grouping function to determine whether the null value in the result set is a group by summary value.
The total number of rows in the result set depends on the number of columns contained in the group by clause. Each operand (column) in the group by clause is bound to null in the group, and the group applies to all other operands (columns ). Because cube returns a combination of each possible group and sub-group, the number of rows is the same no matter what sequence is used when the group column is specified.
Rollup
Specify that the result set contains not only normal rows provided by group by, but also summary rows. In hierarchical order, from the lowest level to the highest level of the group. The hierarchy of a group depends on the sequence used when the group column is specified. Changing the order of grouping columns affects the number of rows generated in the result set.
Important when cube or rollup is used, distinguishing aggregation is not supported, such as AVG (distinct column_name), count (distinct column_name), and sum (distinct column_name ). If this type of aggregation is used, SQL Server Returns an error message and cancels the query.

Having clause
Specify search criteria for a group or aggregation. Having is usually used with the group by clause. If the group by clause is not used, having acts the same way as where clause.
Syntax
[Having <search_condition>]
Parameters
<Search_condition>
The search conditions that the specified group or aggregation should meet. When having and group by all are used together, the having clause replaces all. For more information, see search criteria.
The text, image, and ntext data types cannot be used in the having clause.
The having clause in the SELECT statement does not affect the way in which the cube operator grouping result set and aggregate rows are returned.

Union operator
Combine the results of two or more queries into a single result set that contains all rows of all queries in the Union query. This is different from using join to combine columns in two tables.
The two basic rules for combining two query result sets using union are:
The columns and columns in all queries must be in the same order.
The data type must be compatible.
Syntax
{<Query specification >|( <query expression> )}
Union [all]
<Query specification | (<query expression>)
[Union [all] <query specification | (<query expression>)
[... N]
Parameters
<Query_specification> | (<query_expression>)
It is a query criterion or query expression used to return data that is combined with the data returned by another query criterion or query expression. Column definitions that are part of the union operation can be different, but they must be compatible through implicit conversion.
The following table describes the comparison rules between the data type and the options in the corresponding (ith) column.
Data Type of the ith column in The result table
Non-data compatible type (data conversion is not performed by Microsoft? SQL Server? Implicit processing ). Error returned by SQL Server.
The Fixed Length of char is L1 and L2. The same fixed length as the long char in L1 and L2.
The Fixed Length of binary is L1 and L2. The length is equivalent to the long binary in L1 and L2.
Any or both columns are variable-length char. A variable-length char with the same length as the maximum length specified for the ith column.
Either or both columns are variable-length binary. The variable length is equivalent to the maximum length specified for the ith column, binary.
Both columns are numeric data types (such as smallint, Int, float, and money ). The data type that is equal to the maximum precision of the two columns. For example, if the ith column of Table A is of the int type and the ith column of Table B is of the float type, the Data Type of the ith column in The result table is float, because float is more accurate than Int.
The description of both columns is not null. Specify not null.
Union
Specify a combination of multiple result sets and return them as a single result set.
All
Contains all rows in the result, including duplicate rows. If this parameter is not specified, duplicate rows are deleted.

Order by clause
Specify the sorting of result sets. The order by clause is invalid in views, nested functions, derived tables, and subqueries unless top is specified at the same time.
Syntax
[Order by {order_by_expression [ASC | DESC]} [,... n]
Parameters
Order_by_expression
Specifies the column to be sorted. You can specify the sorting column as the column name or column alias (which can be limited by the table name or view name) and expression, or it is specified as a negative integer representing the position of the name, alias, or expression in the selection list.
You can specify multiple sorting columns. The sequence of sorting columns in the order by clause defines the structure of the sorting result set.
The order by clause can include items not listed in this selection list. However, if select distinct is specified, or if the SELECT statement contains the Union operator, the sorting column must appear in the selection list.
In addition, when a SELECT statement contains the Union operator, the column name or column alias must be the column name or column alias specified in the first selection list.
Note that ntext, text, and image Columns cannot be used in the order by clause.
ASC
Specify to sort the values in the specified column from the minimum value to the maximum value in ascending order.
Desc
Specify to sort the values in the specified column from the highest value to the lowest value in descending order.
NULL values are considered as the lowest possible values.
There is no limit on the number of items in the order by clause. However, the size of the intermediate worksheet required for sorting operations is limited to 8,060 bytes. This limits the total size of the columns specified in the order by clause.
Compute clause
Generate a total as an additional summary to list the final result set. When used together with by, the compute clause generates a summary of control interruptions and categories in the result set. You can specify compute by and compute in the same query.
Syntax
[Compute
{AVG | count | max | min | STDev | stdevp
| Var | varp | sum}
(Expression)} [,... n]
[By expression [,... n]

Parameters
AVG | count | max | min | STDev | stdevp | Var | varp | sum
Specify the aggregation to be executed. The following is the row aggregate function used by the compute clause:
Result of the row Aggregate Function
Average value of all values in an AVG numeric expression
Count the number of selected rows
Maximum Value in the max expression
Minimum value in Min expression
Statistical standard deviation of all values in the STDev expression
Stdevp expression standard deviation for filling all values
Sum of all values in the sum numeric expression
Statistical variance of all values in Var expressions
Fill statistical variance of all values in the varp expression
There is no function equivalent to count. To find the summary information generated by group by and count (*), use the compute clause without.
These functions ignore null values.
If you use the row aggregate function specified by the copput clause, you are not allowed to use the distinct keyword.
When you add or average integer data, SQL Server regards the result as an int even if the column data type is smallint or tinyint. For more information about the types of the added or average data returned, see sum and AVG.
To reduce the number Program For the possibility of overflow errors, please declare all the variable declarations of the average or summary results as the data type Int.
(Expression)
Expression, such as the column name for which the computation is performed. Expression must appear in the selection list and be specified to be exactly the same as an expression in the selection list. The column alias specified in the selection list cannot be used in expression.
The ntext, text, and image data types cannot be specified in the compute or compute by clause.
By expression
Generate a summary of control interruptions and categories in the result set. Expression is an exact duplicate of order_by_expression in the related order by clause. Generally, this is a column name or column alias. Multiple Expressions can be specified. Multiple expressions listed after 'by' can be grouped into subgroups and Aggregate functions can be applied at each grouping level.
If compute by is used, the order by clause must also be used. The expression must be the same as the clause listed after qrder by or its subset, and must follow the same sequence. For example, if the order by clause is:
Order by a, B, c
The compute clause can be any (or all) of the following ):
Compute by a, B, c
Compute by a, B
Compute by
In the SELECT statement with the compute clause, select the column sequence in the list to replace the aggregate function sequence in the compute clause. ODBC and DB-library programmers must know this sequence requirement to place the aggregate function results in the correct position.
Because tables generated by statements containing compute are not stored in the database, compute cannot be used in select into statements. Therefore, any compute-generated computing results cannot be found in a new table created using the select into statement.
When a SELECT statement is part of a declare cursor statement, the compute clause cannot be used.
For clause
The for clause is used to specify browse or XML options (browse and XML are irrelevant options ).
Syntax
[For {browse | XML {raw | auto | explicit}
[, Xmldata]
[, Elements]
[, Binary base64]
}
]
Parameters
Browse
Specifies that data in the DB-library browser mode cursor can be updated. If the table contains a timestamp column (a column defined by the timestamp data type), the table has a unique index and the for browse option is sent to SQL server at the end of the SELECT statement, you can browse the table in the application. For more information, see browse mode.
<Lock_hint> holdlock cannot be used in select statements containing the for browse option.
The for browse option cannot appear in the SELECT statement joined by the Union operator.
XML
The specified query result is returned as an XML document. You must specify one of the following XML modes: raw, auto, and explicit. For more information about XML data and SQL Server, see retrieve XML documents using for XML.
Raw
Obtain the query results and convert the rows in the result set to XML elements. Use the General identifier <row/> as the element tag. For more information, see use raw mode.
Auto
Return the query result using a simple Nested XML tree. In the from clause, each table listed in at least one column in the select clause is represented as an XML element. Columns listed in the select clause map to appropriate element features. For more information, see use auto mode.
Explicit
Specify the shape of the XML tree explicitly defined. In this mode, you need to write a query in a specific way to explicitly specify additional information about the expected nesting. For more information, see use the explicit mode.
Xmldata
Returns the schema, but does not add the root element to the result. If xmldata is specified, it is appended to the document.
Elements
The specified column is returned as a child element. Otherwise, columns are mapped to the XML feature.
Binary base64
Returns binary base64 encoded binary data. This option must be specified when binary data is retrieved in raw and explicit modes. This is the default value in auto mode.

Option clause
Specify that the specified query prompt should be used throughout the query. Each query prompt can be specified only once, but multiple query prompts can be specified. This statement can only specify one option clause. The query prompt affects all operators in the statement. If the main query involves Union, only the last query involving the Union operator can have an option clause. If one or more query prompts make the query optimizer fail to generate a valid plan, error 8622 is generated.
Note that the query optimizer usually selects the best execution plan for the query. Therefore, it is recommended that experienced database administrators use <join_hint>, <query_hint>, and <table_hint> as the final means.
Syntax
[Option (<query_hint> [,... n)]
<Query_hint >::=
{Hash | order} Group
| {Concat | hash | merge} Union
| {Loop | merge | hash} join
| Fast number_rows
| Force Order
| Maxdop number
| Robust plan
| Keep plan
| Keepfixed plan
| Expand views
}
Parameters
{Hash | order} Group
Specify that the aggregation described in the group by, distinct, or compute query clause should be hashed or arranged.
{Merge | hash | Concat} Union
All Union operations are performed by merging, hashing, or concatenating Union sets. If more than one union prompt is specified, the query optimizer selects the policy with the least overhead from these specified prompts.
{Loop | merge | hash} join
Specify that all join operations in the entire query are completed by loop join, merge join, or hash join. If multiple join prompts are specified, the optimizer selects the cheapest join policy from the allowed join policy.
If a join prompt is specified for a specific table in the same query, the join prompt will give priority to the two tables although the query prompt still needs to be followed. Therefore, the join prompt specified for this table may only limit the selection of join methods allowed in the query prompt. For more information, see the prompt.
Fast number_rows
Optimize the query to quickly retrieve the first number_rows (non-negative integer ). After the first number_rows returns, the query continues and the complete result set is generated.
Force Order
Specify the order of join represented by the query syntax during query optimization.
Maxdop number
This option is used only for queries that specify the max degree of parallelism configuration option of sp_configure. When the maxdop query prompt is used, all Semantic Rules used with the Max degree of parallelism configuration options apply. For more information, see the max degree of parallelism options.
Robust plan
The Force query optimizer uses a plan that is effective for the maximum possible row size at the performance cost. During query processing, intermediate tables and operators may need to store and process rows that are wider than input rows. In some cases, the row may be wide, so that an operator cannot process the row. If this happens, SQL Server generates an error during query execution. By using the robust plan, you can instruct the query optimizer to ignore the query plan that may encounter this problem.
Keep plan
Force the query optimizer to relax the estimated re-compilation threshold for the query. The estimated re-compilation threshold is a point. Based on this, the query is automatically re-compiled when the index column changes (Update, delete, or insert) to the table to reach the estimated number. Specifying the keep plan ensures that the query is not frequently re-compiled when multiple updates exist in the table.
Keepfixed plan
The Force query optimizer does not recompile the query because of changes in statistics or index columns (Update, delete, or insert. Specifying the keepfixed plan ensures that the query is re-compiled only when the schema of the base table is changed or when sp_recompile is executed on those tables.
Expand views
Specify to expand the index view, and the query optimizer does not regard any index view as an alternative to any part of the query. (When the view name is replaced by the view definition in the query text, the view is expanded .) In fact, this query prompt does not allow direct use of the index view in the query plan or direct use of the index view.
The view can only be directly referenced in the select section of the query, and the with (noexpand), with (noexpand, index (index_val [,... n]) to expand the index view. For more information about the query prompt with (noexpand), see from.
Only the views in the Select part of the statement (including the views in the insert, update, and delete statements) are affected by the prompt.
Note
The order of clauses in the SELECT statement is very important. Optional clauses can be omitted, but these clauses must appear in proper order during use.
Only when the selection list of these statements contains the expression that assigns values to the local variables of the function, select statements are allowed in user-defined functions.
Within the scope of the table variable, you can access the table variable as you access the regular table. Therefore, you can use the table variable as the table source in the SELECT statement.
For a name composed of four parts, if the server name in the name uses the OpenDataSource function, the name can be used as the table source at any location where the table name can appear in the SELECT statement.
Some syntax restrictions apply to select statements involving remote tables. For more information, see external data and transact-SQL.
The return length of the text or ntext column in the list is the minimum actual text size by default, the default textsize session setting, or the hard-coding application restriction. To change the length of the returned text of a session, use the set statement. By default, the length of the text data returned by the SELECT statement is limited to 4,000 bytes.
If either of the following conditions occurs, SQL Server generates an exception error code 511 and rolls back the currently executed statement:
The SELECT statement generates a result row or intermediate worksheet of more than 8,060 bytes.
An attempt is made to execute Delete, insert, or update statements on rows exceeding 8,060 bytes.
In SQL Server, if the column name is not provided for the columns created by the Select into or create view statement, an error occurs.
Select ID column
When an existing identity column is selected into a new table, the new column inherits the identity attribute, unless one of the following conditions is true:
Select statements include joins, group by clauses, or aggregate functions.
Multiple select statements are connected by union.
The ID column is listed multiple times in the selection list.
An identifier column is part of an expression.
If one of these conditions is true, the column is created as not null without inheriting the identity attribute. All rules and restrictions used to identify columns apply to the new table.
Old Outer Join
Early versions of SQL server support using the * = and = * operators in the WHERE clause to define outer joins. SQL Server 7.0 supports the SQL-92 standard, which provides join operators in the from clause. We recommend that you rewrite the query using the SQL-92 syntax.
Processing sequence of where, group by, and having clauses
The following table shows the processing sequence of select statements with WHERE clause, group by clause, and having clause:
The where clause is used to exclude rows that do not meet the search conditions.
The group by clause collects selected rows into the group with unique values in the group by clause.
The aggregate function specified in the selection list calculates the aggregate values of each group.
The having clause further removes rows that do not meet the search criteria.
Permission
By default, select permissions are granted to SysAdmin fixed server role members, db_owner, db_datareader fixed database role members, and table owners. Members and table owners of SysAdmin, db_owner, and db_securityadmin roles can transfer permissions to other users.
If you use the into clause to create a permanent table, you must have the create table permission in the target database.