SELECT command in SQL Server

This tutorial will help you use SQL Server SELECT statements (T-SQL) with syntax and examples.

SELECT is the command used to retrieve results from one or more tables in a SQL Server database.

Syntax SELECT statement

In simple form, the syntax of SELECT statement is as follows:

 SELECT 'biểu thức' 
 FROM 'bảng' 
 [WHERE 'điều kiện']; 

In the full form, the syntax of the SELECT command in SQL Server will be:

 SELECT [ALL | DISTINCT] 
 [TOP (gia_tri_dau) [PERCENT] [WITH TIES]] 
 'Expression' 
 FROM 'table' 
 [WHERE 'condition'] 
 [GROUP BY 'expression'] 
 [HAVING 'condition'] 
 [ORDER BY 'expression' [ASC | DESC]]; 

Variable name or variable value

ALL - Option, return all matching items.

DISTINCT - Optional. Remove all duplicate values ​​from the result set.

TOP (gia_tri_dau) - Optional. If specified, it will return the first values ​​in the result set based on the selected gia_tri_dau . For example, TOP (10) will return the first 10 rows in the result set.

PERCENT - Optional. If specified, the first rows are based on percentages on the result set (indicated by gia_tri_dau ). For example TOP (10) PERCENT will return the top 10% of the first result in the result set.

WITH TIES - Options. If specified, the fixed rows at the end of the limited results will be returned. This may cause more rows to return than TOP allows.

Expressions - The column or calculated value you want to retrieve. Use * if you want to get all columns.

Table - The table you want to get the result from. There must be at least 1 table listed in the FROM command.

WHERE 'conditions' - Options. The condition that the result returned must meet.

GROUP BY 'expression' - Optional. Collect data from multiple records and result groups in one or more columns.

HAVING 'conditions' - Options. Used in conjunction with GROUP BY to limit the group of rows returned when the condition is met as TRUE.

ORDER BY 'expression' - Optional. Used to filter the result set. ASC will filter in ascending order and DESC will filter in descending order.

For example - select all fields in a table

 SELECT * 
 FROM hangtonkho 
 WHERE soluong> 5 
 ORDER BY hangtonkho_id ASC; 

In this example, * is used to indicate that all hangtonkho fields will be larger than 5 in the hangtonkho table. The result set is sorted in ascending order of hangtonkho_id.

Example - Select some fields in the table

 SELECT hangtonkho_id, hangtonkho_type, soluong 
 FROM hangtonkho 
 WHERE hangtonkho_id> = 555 
 AND hangtonkho_type = 'phanmem' 
 ORDER BY soluong DESC, hangtonkho_id ASC; 

In the above example, the returned result will only include the ID, type and quantity (hangtonkho_id, hangtonkho_type and soluong) of the inventory from the hangtonkho table provided that the ID is greater than or equal to 555, the type of inventory is 'phanmem'. Results are sorted by descending number and increasing inventory ID.

Example - Choose from multiple tables

 SELECT hangtonkhi.hangtonkho_id, sanpham.sanpham_ten, hangtonkho.soluong 
 FROM hangtonkho 
 INNER JOIN sanpham 
 ON hangtonkho.sanpham_id = sanpham.sanpham_id 
 ORDER BY hangtonkho_id; 

In this example, the result set is taken from two tables to produce hangtonkho_id, sanpham_ten and soluong, in the case of sanpham_id in the hangtonkho table and the panel must be the same. The results are arranged in ascending order of hangtonkho_id.

Example - Use the keyword TOP

 SELECT TOP (3) 
 hangtonkho_id, hangtonkho_type, soluong 
 FROM hangtonkho 
 WHERE hangtonkho_type = 'phanmem' 
 ORDER BY hangtonkho_id ASC; 

In this example, the result will return the first 3 values ​​from the hangtonkho table with the inventory type 'phanmem'. If there are other values ​​that meet the requirements, they will not be returned.

Example - Use keyword TOP PERCENT

 SELECT TOP (10) PERCENT 
 hangtonkho_id, hangtonkho_type, soluong 
 FROM hangtonkho 
 WHERE hangtonkho_type = 'phanmem' 
 ORDER BY hangtonkho_id ASC; 

The returned result will consist of 10% of the first value in the inventory of 'phanmem' in the hangtonkho table. 90% of the remaining results will not be returned .

Previous article: Analysis services in MS SQL Server

Next article: FROM clause in SQL Server