Select Statement SQL Server

Database tables are objects that stores all the data in a database. In a table, data is logically organized in a row-and-column format which is similar to a spreadsheet.
In a table, each row represents a unique record and each column represents a field in the record. For example, the  customers table contains customer data such as customer identification number, first name, last name, phone, email, and address information as shown below:
Customers table
SQL Server uses schemas to logically groups tables and other database objects. In our sample database, we have two schemas: sales and production. The sales schema groups all the sales related tables while the production schema groups all the production related tables.
To query data from a table, you use the SELECT statement. The following illustrates the most basic form of the SELECT statement:
In this syntax:
  • First, specify a list of comma-separated columns from which you want to query data in the SELECT clause.
  • Second, specify the source table and its schema name on the FROM clause.
When processing the SELECT statement, SQL Server processes the FROM clause first and then the SELECT clause even though the SELECT clause appears first in the query.
SQL Server SELECT - clause order evaluation

SQL Server SELECT statement examples

Let’s use the customers table in the sample database for the demonstration.

A) SQL Server SELECT – retrieve some columns of a table example

The following query finds the first name and last name of all customers:
Here is the result:
sql server select - some columns
The result of a query is called a result set.
The following statement returns the first names, last names, and emails of all customers:
sql server select - select three columns

B) SQL Server SELECT – retrieve all columns from a table example

To get data from all columns of a table, you can specify all the columns in the select list. You can also use SELECT * as a shorthand to save some typing:
sql server select - select all columns
The SELECT * is useful for examining the columns and data of a table that you are not familiar with. It is also helpful for ad-hoc queries.
However, you should not use the SELECT * for real production code due to the following main reasons:
  1. First, SELECT * often retrieves more data than your application needs to function. It causes unnecessary data to transfer from the SQL Server to the client application, taking more time for data to travel across the network and slowing down the application.
  2. Second, if the table is added one or more new columns, theSELECT * just retrieves all columns that include the newly added columns which were not intended for use in the application. This could make the application crash.

C) SQL Server SELECT – sort the result set

To filter rows based on one or more conditions, you use a where clause as shown in the following example:
sql server select - where clause
In this example, the query returns the customers who locate in California.
When the where clause is available, SQL Server processes the clauses of the query in the following sequence: FROMWHERE, and SELECT.
SQL Server SELECT - from where select
To sort the result set based on one or more columns, you use the  order by clause as shown in the following example:
sql server select - order by clause
In this example, the ORDER BY clause sorts the customers by their first names in ascending order.
In this case, SQL Server processes the clauses of the query in the following sequence: FROMWHERESELECT, and ORDER BY.
SQL Server SELECT - from where select order by

D) SQL Server SELECT – group rows into groups example

To group rows into groups, you use the gruup by clause. For example, the following statement returns all the cites of customers located in California and the number of customers in each city.
sql server select - group by clause
In this case, SQL Server processes the clauses in the following sequence: FROM, where , group by, SELECT, and order by.

E) SQL Server SELECT – filter groups example

To filter groups based on one or more conditions, you use the having  clause. The following example returns the city in California which has more than 10 customers:
sql server select - having clause
Notice that the WHERE clause filters rows while the HAVING clause filter groups.
In this tutorial, you have learned how to use the SQL Server SELECT statement to query data from a single table.






Post a Comment

0 Comments