SQL SERVER

Select Statement in SQL Server

In SQL Server by Baqir AliLeave a Comment

Select Statement in SQL Server

The SELECT statement is used to retrieve data from the database. We use a SELECT statement with ‘SELECT’ keywords followed by column names and the table name.

Syntax:

SELECT column1, column2 FROM <table name>

SELECT * From Employee

This will return all columns and all rows from table Employee. (*) is a wildcard which means anything and everything. But we can narrow our search by mentioning the column names.

SELECT Employee_No, Employee_Name, Employee_Type  FROM Employee

This query will return all rows but specific and mentioned columns.

Select Statement

DISTINCT Keyword in Select Statement

The DISTINCT keyword shows unique values if the result set contains duplicate values.

SELECT DISTINCT Employee_No FROM Employee

Where Clause in SELECT Statement

We use the WHERE Clause Whenever we wanted to limit our search result to a specific row or record such as show me an only permanent employee.

We use the WHERE Clause after From Clause.

SELECT Employee_No,Employee_Name,Employee_Type FROM Employee
where Employee_Type='Permanent'

The above code will return only Permanent Employees.

WHERE Clause can also include different types of search conditions such as Like, Between, EXISTS, IS NULL, IS NOT NULL, and CONTAINS. We can also use Different Boolean Operators.

SELECT * from Employee
Where
Employee_Type='Daily Wages' AND Department_No=4

Use of Between Keyword in Select Statement

SELECT * from Employee
Where Department_No BETWEEN 3 AND 10

LIKE Clause in Select Statement

LIKE Clause enables you to use pattern matching in a statement such as searching a list of items in the restaurant menu that contains the word ‘Chicken’.

LIKE Clause has its own wild cards

Wild CardDescription
% (Percent)Replaces any number of characters
_ (Underscore)Replaces exactly one character
[] Square BracketsReplaces one or a range of characters within brackets
^ CaretCaret sign declares a not signal
SELECT * FROM Product
where ProductName LIKE 'Chicken%'

The above code searches for product names containing Chicken at the beginning.

SELECT * FROM Product
where ProductName LIKE '_i%'

It searches for names having their second character as ‘i’.

The output is as follow

SELECT Statement in SQL

Few more SELECT Statement Code Examples

Example 1:

SELECT e.Employee_No,e.Employee_Name,dep.Department_Name,eTyp.Employee_Typee_Name 
FROM dbo.Employee e
INNER JOIN dbo.department dep ON e.Department_No=dep.Department_No
INNER JOIN EmployeeTypes eTyp ON e.Employee_Type_ID=eTyp.Employee_Type_ID

Example 2: Use of DISTINCT Keyword

SELECT distinct Color from Production.Product where Color is not null

Example 3: Use of WHERE Clause with AND

SELECT * FROM Production.Product
WHERE Color = 'Silver' AND ListPrice > 200

Example 4: Use of LIKE Clause Wild Card

SELECT * FROM Production.Product where Name like '_ha%'
--Range Words 
SELECT * FROM Production.Product where Name like '[a-d]%'
-- Range Words with Caret or Not in List
SELECT * FROM Production.Product where Name like '[^a-d]%'

Example 5: Use of Between in WHERE Clause

SELECT ProductNumber, Name, Color
FROM Production.Product
WHERE ListPrice BETWEEN 50 AND 80

Example 6: Manipulating Result Sets

SELECT PP.FirstName AS 'First Name', PP.LastName AS 'Last Name'
, PP.PersonType AS 'Person Category'
FROM Person.Person AS PP
ORDER BY PersonType

Example 7: Concatenation and Ordering By PersonType

SELECT PP.FirstName + ' ' + PP.LastName AS 'Name', PP.PersonType AS 'Category'
FROM Person.Person AS PP
ORDER BY PersonType

Support us by sharing this post

About the Author

Baqir Ali

Facebook Twitter

Hi, I am Baqir Ali ---- Blogger, Programmer, and a Teacher. I am currently working as a Programmer for Softnat Technologies. I have tons of ideas and data to share with you all via blogging.

Leave a Comment