Select Statement in SQL Server

Back to: SQL Server Tutorial

Select Statement in SQL Server with Examples

In this article, I am going to discuss how to use the Select Statement in SQL Server with syntax and examples. Please read our previous article where we discussed the Difference Between Sequence and Identity in SQL Server with examples. As part of this article, we are going to discuss the following pointers related to the Select Statement.

1. What is the Use of Select Statement in SQL Server?
2. Syntax and examples of Select Statement.
3. Parameters or Arguments used in Select Statement.
4. How to Select all fields from one table in SQL Server?
5. How to Select individual fields from one table in SQL Server?

What is the Use of Select Statement in SQL Server?

The Select Statement in SQL Server is used to returns records in the form of a result set from one or more tables or views. Generally, the Select Statement retrieves the data in the form of rows from one or more database tables or views.

The SQL Select Query does not store any data itself. It simply displays the data that is stored in database tables. The Select Statement can retrieve and shows the data from one or more database tables, from other queries, or from a combination of the above two.

Select Statement Syntax in SQL Server:

SELECT Column_List FROM Table_Name

If you want to select all the columns of a table or view then you can also use “*”, but for better performance use the column list instead of using “*”.

SELECT * FROM Table_Name 

You can even use where condition in the select statement as shown below

SELECT expressions FROM tables [WHERE conditions];

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

Parameters or Arguments used in Select Statement in SQL Server:

We can use the following parameters or arguments along with the Select Statement in SQL Server.

ALL: It is optional. when we use ALL then the Select query will return all the matching rows from the table.

DISTINCT: It is optional. If you want to remove the duplicate rows from the result set then you need to use the DISTINCT keyword in the Select query. We will discuss the DISTINCT keyword in detail in our upcoming articles

TOP (top_value): It is optional. If the TOP Clause is specified in the Select query, then it will return the top number of rows in the result set based on top_value. For example, if you specified as TOP (10) in the select query then it will return the top 10 rows from the full result set.

PERCENT: It is optional. If we specified the PERCENT Clause in the select query, then the top rows are retrieved based on the percentage of the total result set (as specified by the top_value). For example, if we specify TOP (10) PERCENT in the select query then it will return the top 10% of the full result set.

WITH TIES: It is optional. If WITH TIES specified in the select query, then the rows tied in the last place within the limited result set are returned.

Expressions: The columns or calculations that we wish to retrieve in the select list. Use * if you wish to select all columns.

Tables: The tables from which you want to retrieve the records. There should be at least one table in the FROM clause of the select query.

WHERE conditions: It is optional. The conditions that must be met for the records to be selected in the result set.

GROUP BY expressions: It is optional. It collects the records and then groups the records by one or more columns.

ORDER BY expression: It is optional and it is basically used to sort the records in ascending or descending order.

Example: Execute the below script to create the Gender and Person table

--Create Gender table
CREATE TABLE Gender
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Gender VARCHAR(50)
)
GO

-- Create Person table
CREATE TABLE Person
(
  ID INT PRIMARY KEY IDENTITY(1,1),
  Name VARCHAR(100),
  EmailID VARCHAR(100),
  GenderID INT,
  Age INT,
  CITY VARCHAR(100)
)
GO

--Add foreign key
ALTER TABLE Person 
ADD CONSTRAINT Person_GenderID_FK FOREIGN KEY (GenderID) 
REFERENCES Gender(ID)
GO

--Insert some test data to Gender table
INSERT INTO Gender VALUES('Male')
INSERT INTO Gender VALUES('Female')
INSERT INTO Gender VALUES('Unknown')
GO

--Insert some test data into Person table
INSERT INTO Person VALUES('PRANAYA','PRANAYA@G.COM',1,30,'MUMBAI')
INSERT INTO Person VALUES('TARUN','TARUN@G.COM',1,27,'ODISHA')
INSERT INTO Person VALUES('PRIYANKA','PRIYANKA@G.COM',2,25,'BANGALORE')
INSERT INTO Person VALUES('PREETY','PREETY@G.COM',2,26,'BANGALORE')
INSERT INTO Person VALUES('RAMESH','RAMESH@G.COM',3,27,'MUMBAI')
INSERT INTO Person VALUES('PRAMOD','PRAMOD@G.COM',1,28,'ODISHA')
GO

SELECT * FROM Gender

SELECT * FROM Person

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 Person WHERE Age > 26 ORDER BY ID ASC

In this SQL Server SELECT statement example, we’ve used * to signify that we wish to select all the columns from the Person table where the Age is greater than 26. The result set is sorted by ID in ascending order.

Example: Select individual fields from one table

You can also use the SQL Select statement to select individual fields of a table.

For example:

This SQL Server Select example would return only the ID, Name, EmailID, and CITY from the Person table where the ID is less than 5 and the Age is greater than 25. The results are sorted by GenderID in descending order and then ID in ascending order.

In the next article, I am going to discuss the Where clause in SQL Server with examples. Here, In this article, I try to explain the Select Statement in SQL Server step by step with a simple example. I hope now you got a better idea of how to use the Select Statement in SQL Server.