SQL Server SELECT

SQL Server SELECT Statement is used to fetch the data from any table.

It allows conditions to filter the records during fetch.

SYNTAX

SELECT column1, column2, ... FROM table_name WHERE Condition ..
OR
SELECT * FROM table_name WHERE condition..

column1, column2,.. is a list of column that you want to shown in your result. It may be one column or multiple columns.

table_name is a source table name.

Condition is a condition to filter table records. It is an optional.

asterisk (*) if it is used in selecte statement, then it returns all the column present in table_name, no need to specify column’s name in select statement .

Note that using asterisk(*) may cause slowness during data fetch from table, beacuse sometime you do not need to see data for all columns so it is recommended that select only those columns from table that you want.

Lets look at an example of Select statement in SQL .

Before this, we create a sample tables and Insert some records into table.

CREATE TABLE [dbo].[Department]
([Dept_ID] [int] NOT NULL PRIMARY KEY, 
[Dept_Name] [varchar](100) NOT NULL, 
[Dept_Code] [varchar](10) NULL )

INSERT INTO dbo.Department (Dept_ID, Dept_Name, Dept_Code) 
VALUES(501, 'IT', '001'), 
(502, 'FINANCE', '002'), 
(503, 'HR', '003'), 
(504, 'MIS', '004'), 
(505, 'CALL CENTER', '005'), 
(506, 'SALES', '006')


CREATE TABLE [dbo].[Employee]
( [Emp_Id] [int] NOT NULL PRIMARY KEY, 
[First_Name] [varchar](250) NOT NULL, 
[Last_Name] [varchar](250) NOT NULL, 
[Dept_Id] [int] REFERENCES [dbo].[Department] ([Dept_ID]) )

INSERT INTO dbo.Employee 
VALUES(1, 'Rajesh', 'Singh', 501), 
(2, 'Amit','Agarwal', 502), 
(3, 'Sanjay', 'Moishra', 501), 
(4, 'Sujoy', 'Gosh', 504), 
(5, 'karthik','Chauhan', 505), 
(6, 'Chand', 'Kaur', 504), 
(7, 'Lovnish', 'Gupta', 504), 
(8, 'Munish', 'Rawat', 502)

Now, we have a Deptament and Employee table present in SQL Server as shown in below screenshot.

SELECT * FROM dbo.Department
SELECT * FROM dbo.Employee

Select data from table for all the columns

Following statements selects records for all the columns present in the Employee’s table using an asterisk (*).

SELECT * FROM dbo.Employee

As you can see, it returns all records for the columns present in table.

Select records from table based on a specific conditions

Following statement selects records for all the columns present in table Employee for conidition for Emp_Id is greater than 5.

Where clause is used to filter the records from table for Emp_Id >5. 

SELECT * FROM dbo.Employee WHERE Emp_Id > 5 

You can see the output of statement, It returns all the column’s data from table for condition Emp_Id greater than 5.

Select only specific columns from table

Following SQL statement selects only three columns that is Emp_ID , First_Name, and Last_Name from Employee table.

SELECT Emp_Id, First_Name, Last_Name 
FROM dbo.Employee 

You can see the output of statement, It returns all the records from table for Columns StudId, and StudName.

As you can see, it returns all the records from Employee table for only specific columns which are mentioned in select statement.

 

SELECT data from Multiple Tables 

You can also select data from multiple table using SQL Joins.

Following statement select data for all the columns from both Employee and Department tables.

SELECT * FROM dbo.Employee Emp 
               INNER JOIN Department Dpt
               ON Emp.Dept_Id = Dpt.Dept_ID

 

You can see the output, It returns data from tables for all the columns of  both tables. 

Lets say you do not want all the columns from Employee table but want all columns from depatment table.

In this case you can mention specific column’s name for table Employee while to fetch all columns for Department table using table_aliasname.*, or table_name.* as shown below for fetching all columns from Department table Dpt.* is used.

SELECT Emp.Emp_Id, Emp.First_Name, Dpt.* FROM dbo.Employee Emp 
INNER JOIN Department Dpt
ON Emp.Dept_Id = Dpt.Dept_ID

You can see the output, only two columns that you specifed for employee’s table are fetched while all the columns from Department table are fetched.

So you can use an asterick * to fetch all the columns from table, and table_aliasname.*, table_name.*  in case of mutlple tables is being used in select statemnt.

SELECT data from table using SQL Server Management Studio

You can also select records from table using SQL Server Managment Studio. Following are the steps.

Go to object explorer, Select table from database as shown below.

 

Then Right click on table, then click on Select Top 1000 Rows from context menu.

Once click, SQL Server immediately generates a select scripts for you in query editor window.

Also Read..

SQL SELECT INTO

SQL SELECT DISTINCT