top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

SQL Server: SELECT Statement

0 votes
492 views

This SQL Server tutorial explains how to use the SELECT statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT statement is used to retrieve records from one or more tables in a SQL Server database.

Syntax

In its simplest form, the syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the SELECT statement in SQL Server (Transact-SQL) is:

SELECT [ ALL | DISTINCT ]
[ TOP (top_value) [ PERCENT ] [ WITH TIES ] ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

ALL

Optional. Returns all matching rows.

DISTINCT

Optional. Removes duplicates from the result set. Learn more about the DISTINCT clause

TOP (top_value)

Optional. If specified, it will return the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

GROUP BY expressions

Optional. It collects data across multiple records and groups the results by one or more columns.

HAVING condition

Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE.

ORDER BY expression

Optional. It is used to sort the records in your result set. ASC sorts in ascending order and DESC sorts in descending order.

Example - Select all fields from one table

Let's look at how to use a SQL Server SELECT query to select all fields from a table.

SELECT *
FROM inventory
WHERE quantity > 5 
ORDER BY inventory_id ASC;

In this SQL Server SELECT statement example, we've used * to signify that we wish to select all fields from the inventory table where the quantity is greater than 5. The result set is sorted by inventory_id in ascending order.

Example - Select individual fields from one table

You can also use the SQL Server SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_id >= 555
AND inventory_type = 'Software'
ORDER BY quantity DESC, inventory_id ASC;

This SQL Server SELECT example would return only the inventory_id, inventory_type, and quantity fields from the inventory table where the inventory_id is greater than or equal to 555 and the inventory_type is 'Software'. The results are sorted by quantity in descending order and then inventory_id in ascending order.

Example - Select fields from multiple tables

You can also use the SQL Server SELECT statement to retrieve fields from multiple tables by using a join.

For example:

SELECT inventory.inventory_id, products.product_name, inventory.quantity
FROM inventory
INNER JOIN products
ON inventory.product_id = products.product_id
ORDER BY inventory_id;

This SQL Server SELECT example joins two tables together to gives us a result set that displays the inventory_id, product_name, and quantity fields where the product_id value matches in both the inventory and products table. The results are sorted by inventory_id in ascending order.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(3)
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 3 records from the inventory table where the inventory_type is 'Software'. If there are other records in the inventory table that have a inventory_type value of 'Software', they will not be returned by the SELECT statement.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
inventory_id, inventory_type, quantity
FROM inventory
WHERE inventory_type = 'Software'
ORDER BY inventory_id ASC;

This SQL Server SELECT example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the inventory table where the inventory_type is 'Software'. The other 90% of the result set would not be returned by the SELECT statement.

posted Mar 25, 2017 by Shivaranjini

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: SELECT TOP Statement

This SQL Server tutorial explains how to use the SELECT TOP statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) SELECT TOP statement is used to retrieve records from one or more tables in SQL Server and limit the number of records returned based on a fixed value or percentage.

Syntax

The syntax for the SELECT TOP statement in SQL Server (Transact-SQL) is:

SELECT TOP (top_value) [ PERCENT ] [ WITH TIES ]
expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Parameters or Arguments

TOP (top_value)

Returns the top number of rows in the result set based on top_value. For example, TOP(10) would return the top 10 rows from the full result set.

PERCENT

Optional. If PERCENT is specified, then the top rows are based on a percentage of the total result set (as specfied by the top_value). For example, TOP(10) PERCENT would return the top 10% of the full result set.

WITH TIES

Optional. If the WITH TIES clause is specified, then rows tied in last place within the limited result set are returned. This may result in more rows be returned than the TOP parameter permits.

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT TOP statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

Example - Using TOP keyword

Let's look at a SQL Server example, where we use the TOP keyword in the SELECT statement.

For example:

SELECT TOP(5)
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 5 records from the employees table where the last_name is 'Anderson'. If there are other records in the employees table that have a last_name of 'Anderson', they will not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(5) WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the 5th top record is a tie, then all records in this tie position would be returned by the SELECT TOP statement. This will result in more than 5 records being returned.

Example - Using TOP PERCENT keyword

Let's look at a SQL Server example, where we use the TOP PERCENT keyword in the SELECT statement.

For example:

SELECT TOP(10) PERCENT
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

This SQL Server SELECT TOP example would select the first 10% of the records from the full result set. So in this example, the SELECT statement would return the top 10% of records from the employees table where the last_name is 'Anderson'. The other 90% of the result set would not be returned by the SELECT statement.

You could modify this example to include the WITH TIES clause as follows:

SELECT TOP(10) PERCENT WITH TIES
employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Anderson'
ORDER BY employee_id;

The WITH TIES clause would include rows that may be tied in last place within the limited result set. So if the last position in the SELECT TOP(10) PERCENT record set is a tie, then these tied records would be returned by the SELECT TOP statement. This will result in more than 10% of the full record set being returned.

 READ MORE
Displaying the Row Number in a SELECT Query with SQL Server 2005

Displaying the Row Number in a SELECT Query was not easy with SQL 2000 or less where you need to create a temp table to display the row numbers for a returned result set. 

Now SQL Server 2005 ROW_NUMBEWR() that is very handy in scenarios where you want the result set to have row numbers assigned to each returned row. 

For example:

Select empname 
from employees 
where city = "toronto"

If you want the row_numbers to be displayed after this query is run, then use the ROW_NUMBER()function as below:

SELECT ROW_NUMBER() OVER(ORDER BY empname) AS 'row number', empname FROM employees WHERE city = "toronto"

When you run the above query, you will see results like those below. This avoids creating temp tables.

  1 Fred
  2 Bill
  3 Jeff
  4 June

What really happens here is that the ROW_NUMBER()assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

 READ MORE
SQL Server: GOTO Statement

This SQL Server tutorial explains how to use the GOTO statement in SQL Server (Transact-SQL) with syntax and examples.

Description

The GOTO statement causes the code to branch to the label after the GOTO statement.

Syntax

The syntax for the GOTO statement in SQL Server (Transact-SQL) consists of two parts - the GOTO statement and the Label Declaration:

GOTO statement

The GOTO statement consists of the GOTO keyword, followed by a label_name.

GOTO label_name;

Label Declaration

The Label Declaration consists of the label_name, followed by at least one statement to execute.

label_name:
 {...statements...}

Note

  • label_name must be unique within the scope of the code.
  • There must be at least one statement to execute after the Label Declaration.

Example

Let's look at how to use the GOTO statement in SQL Server (Transact-SQL).

For example:

DECLARE @site_value INT;
SET @site_value = 0;

WHILE @site_value <= 10
BEGIN

   IF @site_value = 2
      GOTO TechOnTheNet;

   SET @site_value = @site_value + 1;

END;

TechOnTheNet:
   PRINT 'TechOnTheNet.com'; 

GO

In this GOTO example, we have created one GOTO statements called TechOnTheNet. If @site_value equals 2, then the code will branch to the label called TechOnTheNet.

 READ MORE
...