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.
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.
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
|% (Percent)||Replaces any number of characters|
|_ (Underscore)||Replaces exactly one character|
| Square Brackets||Replaces one or a range of characters within brackets|
|^ Caret||Caret 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
Few more SELECT Statement Code Examples
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