SQL Select Statement in SQL Server
In this article, I am going to discuss how to use the SQL Select Statement in SQL Server with syntax and examples. The SQL Select Statement in SQL Server returns records in the form of a result set from one or more tables or views. Generally, a SQL Select Statement retrieves the data in the form of rows from one or more database tables or views. In most of the applications, the SQL Select Statement is the most commonly used Data Query Language (DQL) or you can say Data Retrieval Language command.
The SQL Select query does not store any data itself. It simply displays the data that is stored in database tables. The SQL 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.
In its simplest form, the syntax for the SELECT statement in SQL Server (Transact-SQL) is:
Basic SQL Select Statement syntax
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 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:
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: 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.
Execute the below script to create 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.
SELECT ID, Name, EmailID, CITY FROM Person WHERE ID < 5 AND Age > 25 ORDER BY GenderID DESC, ID ASC;
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 will discuss Where clause in SQL Server with examples.
In this article, I try to explain the SQL Select Statement in SQL Server step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.