{"id":93376,"date":"2022-02-22T18:08:53","date_gmt":"2022-02-22T18:08:53","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=93376"},"modified":"2022-02-22T18:08:53","modified_gmt":"2022-02-22T18:08:53","slug":"the-basic-t-sql-select-statement","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/the-basic-t-sql-select-statement\/","title":{"rendered":"The basic T-SQL SELECT statement"},"content":{"rendered":"

The SELECT<\/code> statement is the most used statement in the T-SQL language. It is executed to retrieve columns of data from one or more tables. The SELECT statement can constrain the data returned by using the WHERE<\/code> or HAVING<\/code> clause, and sort or group results using the ORDER<\/code> BY<\/code> and GROUP<\/code> BY<\/code> clauses, respectively. A SELECT<\/code> statement can also be made up of many different SELECT<\/code> statements, commonly called subqueries.<\/p>\n

There are many different aspects of the SELECT<\/code> statement, making it a very complex T-SQL statement. This article is the first in a series of articles that will cover the various nuances of the SELECT<\/code> statement. In this article, I will discuss only the basics of the SELECT<\/code> statement.<\/p>\n

The SELECT statement<\/h2>\n

The SELECT<\/code> statement has many different pieces, making it multifaceted. The main clauses supported by the SELECT<\/code> statement as found in the Microsoft Documentation<\/a> are shown in Figure 1.<\/p>\n

\"An<\/p>\n

Figure 1: Main clauses of the SELECT statement<\/strong><\/p>\n

The SELECT<\/code> statement supports many different options. To discuss each of these various options would require a very long article. For the purpose of this article, I will only be focusing on a few clauses, which I am presenting as the basic SELECT<\/code> statement syntax shown in Figure 2.<\/p>\n

\"An<\/p>\n

Figure 2: Basic SELECT statement<\/strong><\/p>\n

The basic SELECT<\/code> statement has three parts: the select list, the FROM<\/code> clause, and the WHERE<\/code> clause.<\/p>\n

The SELECT list<\/h2>\n

The select_list<\/code> component identifies the data that will be returned when the SELECT<\/code> statement is executed. This is the only part that is required in every SELECT<\/code> statement. All other attributes of a SELECT<\/code> statement are options.<\/p>\n

The select_list<\/code> identifies one or more items of data that will be returned when the SELECT<\/code> statement is executed. When multiple items are identified in the list, each item is separated by a comma. The arguments of a select list can take on many different forms, like a column name, a calculation, a function call, or a literal constant, to name a few.<\/p>\n

\"An<\/p>\n

Figure 3: Select_list<\/em> specifications<\/strong><\/p>\n

Listing 1 has an example of a SELECT<\/code> statement that contains only a selection list. There are two different columns of data in the list that will be returned.<\/p>\n

Listing 1: Returning a calculated field and literal value<\/strong><\/p>\n

USE tempdb;\r\nGO \r\nSELECT 1024*1024, \t\r\n       'The Basic Select Statement';<\/pre>\n
Report 1 shows the two column values returned when Listing 1 is executed.<\/p>\n
Report 1: Columns returned when Listing 1 is executed <\/strong><\/p>\n
<\/p>\n
The first column returned is the result of a calculation that multiplies 1024 by1024. The second column displayed is just the literal string The Basic Select Statement<\/em>. The columns returned were not given column names, hence why the column names say (No column name).<\/p>\n
Not having a column name for some columns in the result set is not really a problem, unless you need to reference the returned data by name. To assign names to these two columns, a column alias can be used.<\/p>\n
Assigning column aliases<\/h2>\n
A column alias is useful when you want to assign a specific name to a column of data returned from a SELECT<\/code> statement. If you refer to the select_list<\/code> specifications found in Figure 3 there are two ways to define a column alias. One way is to use the AS<\/code> keyword, followed by the name of the column alias. The AS<\/code> keyword is optional. The other way is to use the equal operator (=<\/code>), where the alias name is defined on the left side of the =<\/code> sign. The SELECT<\/code> statement in Listing 2 returns the same data as Listing 1, but each item identified in the selection list has now been given a column alias name.<\/p>\n
Listing 2: Defining column aliases<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSELECT 1024*1024 AS NumOfBytesInMB, \t\r\n       'The Basic Select Statement' BasicSelectStatement;<\/pre>\n
When Listing 2 is executed each column returned will have a column alias name assigned, as shown in Report 2.<\/p>\n
Report 2: Results from execution of Listing 2<\/strong><\/p>\n
\"An<\/p>\n
In Listing 2, both column alias names do not contain any spaces in the name. If you want to include spaces in an alias name, then the name needs to be enclosed in quotes. Quotes can either be a set of brackets or single or double quotation marks. Lisitng 3 shows how to use the different quoting options.<\/p>\n
Listing 3: Creating alias names with spaces<\/strong><\/p>\n
USE tempdb;\r\nGO \r\nSET QUOTED_IDENTIFIER ON\r\nSELECT 1024*1024 AS [Num of bytes in a MB], \t\r\n       \"Using Double Quotes\" = 'The Basic Select Statement',\r\n\t'ABC' AS 'String ABC';<\/pre>\n
The output in Report 3 is produced when Listing 3 is executed.<\/p>\n
Report 3: Aliases with spaces in name<\/strong><\/p>\n
<\/p>\n
The FROM clause<\/h2>\n
The FROM<\/code> clause in SQL Server is used to identify a table or tables in which to retrieve data. In this basic SELECT<\/code> clause article, I will only discuss retrieving data from a single SQL Server table. Future articles will talk about retrieving data from multiple tables.<\/p>\n
A table within SQL Server is stored in a database. A given SQL Server instance might have many databases, and there might be many tables within a database. Tables within a database are grouped and organized by a schema. The hierarchy of databases, schemas, and tables means there might be multiple tables within a SQL Server instance that have the same name. Because a given table name could reside in various schemas, databases, or even instances, the table name used must be uniquely identified in the FROM<\/code> clause. To uniquely identify a table in the FROM<\/code> clause, it can be named using a one, two, three, or four-part name, where each part is separated by a period (.<\/code>).<\/p>\n
A one-part table name is a table name that doesn\u2019t contain a period, like Orders<\/em> or Customers. <\/em>When a one-part name is used in the FROM<\/code> clause, the database engine needs to determine which schema owns the table. To identify the schema that owns a one-part table name SQL Server uses a two-step process. The first step is to see if the table resides in the default schema associated with the user submitting the SELECT<\/code> statement. If the table is in the default schema for the user, then that table is used, and the database engine doesn\u2019t need to process the second step. If the table is not found in the user\u2019s default schema, then the second step of identifying the table is performed. The second step looks into the dbo<\/em> schema to try and find the table. When a database contains only a single schema named dbo, <\/em>using one-part names makes sense. However, when there are multiple schemas in a database, it is best to use multiple part table names to clarify what table is used. It simplifies the amount of work SQL Server needs to perform to identify the table.<\/p>\n
A two-part name is made up of both the table name and schema that contains the table with a period (.<\/code>) in between, like Sales.Orders, <\/em>or Sales.Customer. <\/em> When writing SELECT<\/code> statements that only query data from tables in a single database with multiple schemas, the best practice is to use two-part table names.<\/p>\n
In most cases, when a SELECT<\/code> statement uses tables in a single database, one and two-part table names are used. Three-part table names are needed when code is run in the context of one database and needs to retrieve data from another database, or you are joining data from multiple tables that live in different databases. The third part of the name precedes the two-part table name and identifies the database in which the table lives, like AdventureWorks2019.Sales.Orders. <\/em><\/p>\n
The last way to uniquely identify a table in the FROM<\/code> clause is to include the instance name as the fourth part. The instance name is placed in front of the fully qualified three-part table name. Four-part names are used to query across instances using a linked server. Discussion of Linked servers is an entirely separate topic, which I will leave for a future article.<\/p>\n
The SELECT<\/code> statement example in Listing 4 runs in the context of the AdventureWorks2019 <\/em>database using a two-part table name to return all the data in the Territory <\/em>table, owned by the Sales <\/em>schema.<\/p>\n
Listing 4: Using two-part table name<\/strong><\/p>\n
USE AdventureWorks2019;\r\nGO\r\nSELECT * FROM Sales.SalesTerritory;<\/pre>\n
The statement in Listing 5 uses a three-part table name to return all the data from the Territory <\/em>table in the Sales <\/em>schema within the AdventureWorks2019<\/em> database, but it runs in the context of the tempdb <\/em>database.<\/p>\n
Listing 5: Using three-part table name<\/strong><\/p>\n
USE tempdb\r\nGO\r\nSELECT * FROM AdventureWorks2019.Sales.SalesTerritory;<\/pre>\n
The rows displayed in Report 4 are produced when either the code in Listing 4 or Listing 5 is executed.<\/p>\n
Report 4: Output when Listing 4 or 5 is run<\/strong><\/p>\n
\"An<\/p>\n
In Listing 4 and Listing 5, the *<\/code> wildcard character was used for the select_list <\/em>option. The *<\/code> tells SQL Server that all columns from the AdventureWorks2019.Sales.SalesTerritory <\/em>table are to be returned based on their ordinal positions within the table. Using the *<\/code> wildcard character in the selection list is a simple way to specify and select all the columns in a table. I wouldn\u2019t recommend using wildcard characters for production code because if columns are added or dropped from the table being queried, the number of columns returned will be based on the table definition at the time of the query. Best practice is not to use wild card characters and instead specifically identify column names for the data that needs to be returned. Note that there are some situations where wildcards do make sense, and I\u2019ll cover those in later articles. The SELECT<\/code> statement in Listing 6 returns the same results as Listings 4 and 5 but specifics the actual quoted column names instead of the *<\/code> wildcard character.<\/p>\n
Listing 6: Specify column names<\/strong><\/p>\n
USE AdventureWorks2019;\r\nGO\r\nSELECT [TerritoryID],\r\n       [Name], \r\n\t  [CountryRegionCode], \r\n\t  [Group], \r\n\t  [SalesYTD], \r\n\t  [SalesLastYear], \r\n\t  [CostYTD], \r\n\t  [CostLastYear], \r\n\t  [rowguid], \r\n\t  [ModifiedDate]\r\nFROM Sales.SalesTerritory;<\/pre>\n
The WHERE clause<\/h2>\n
The SELECT<\/code> statement examples I have shown so far returned all the rows in the AdventurewWorks2019.Sales.SalesTerritory <\/em>table. There might be times when you don\u2019t want to return all the rows in a table but instead only want to return a subset of rows. When this is the case, the WHERE<\/code> clause can be used to restrict the rows returned.<\/p>\n
When using the WHERE<\/code> clause, search criteria needs to be specified. The search criteria identifies one or more expressions, known as predicates, that each row must meet in order to be selected. The logical operators AND<\/code>, OR<\/code>, and NOT<\/code> can be used to string together multiple expressions for fine-tuning the exact rows that should be returned.<\/p>\n
Listing 7 has a SELECT<\/code> statement that contains a single expression in the WHERE<\/code> clause. In this case, only the Sales.SalesTerritory <\/em>records that have their SalesLastYear <\/em>value greater than 3,000,000 will be returned.<\/p>\n
Listing 7: Simple search condition in WHERE statement<\/strong><\/p>\n
USE AdventureWorks2019;\r\nGO\r\nSELECT * FROM Sales.SalesTerritory\r\nWHERE SalesLastYear > 3000000;<\/pre>\n
When the code in Listing 6 is executed the rows in Report 5 are returned.<\/p>\n
Report 5: Rows returned when Listing 7 is executed<\/strong><\/p>\n
\"An<\/p>\n
Sometimes more complex search conditions are needed to produce a specific subset of rows. The SELECT<\/code> statement in Listing 8 uses two different conditions in the WHERE<\/code> clause to narrow down the rows returned. The two different conditions use the logical AND<\/code> operator to create a complex compound search condition.<\/p>\n
Listing 8: Using two expressions with the AND operator<\/strong><\/p>\n
USE AdventureWorks2019;\r\nGO\r\nSELECT * FROM Sales.SalesTerritory\r\nWHERE SalesLastYear > 3000000\r\n  AND CountryRegionCode = 'CA';<\/pre>\n
Report 6 shows the output created when Listing 8 is run.<\/p>\n
Report 6: Results when Listing 8 is executed<\/strong><\/p>\n
\"An<\/em><\/p>\n
The basic T-SQL SELECT statement<\/h1>\n
The SELECT<\/code> statement is the most used statement in the T-SQL language. Understanding the basics of the SELECT<\/code> clause in the foundation of all other formats of the SELECT<\/code> statement. Identifying columns in the selection list, tables in the FROM<\/code>, and constraints in the WHERE<\/code> clause will allow you to return any subset of rows from a database table. I will discuss other more complex aspects of querying SQL Server data using the SELECT<\/code> statement in future articles.<\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
The SQL language is used across many relational database platforms. Greg Larsen explains the basics of the SELECT statement for SQL Server.…<\/p>\n","protected":false},"author":78478,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143525,143531],"tags":[5134],"coauthors":[11330],"class_list":["post-93376","post","type-post","status-publish","format-standard","hentry","category-featured","category-learn","category-t-sql-programming-sql-server","tag-sql-prompt"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93376","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/78478"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=93376"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93376\/revisions"}],"predecessor-version":[{"id":93387,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/93376\/revisions\/93387"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=93376"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=93376"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=93376"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=93376"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

The format of the select_list<\/code> as found in the SELECT clause<\/a> documentation is shown in Figure 3.<\/p>\n