• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server SELECT

SQL Server SELECT

In this tutorial, you will learn about the most basic and most commonly used keyword and statement i.e. the SELECT keyword and the SELECT statement in SQL Server.

What is SELECT statement in SQL Server?

The SQL Server SELECT statement is used to fetch records from one or more SQL Server tables or views from the database. The records fetched are known as resultset which is shown in tabular format.

SQL or Structured Query Language is used for storing, retrieving, and manipulating data in the database. One of the most important aspects of SQL is to retrieve data from the database. SQL has different commands or statements to deals with these different aspects. SQL select is used to retrieve data from the tables or views.

SQL Server SELECT statement is used to –
      1. fetch data from all or one or more columns in a table.
      2. fetch data from multiple columns in multiple tables.

Syntax

The basic syntax of SQL Server SELECT is as follows.

Select expressions
FROM tables
[WHERE conditions]

However, if we consider all the scenarios, the full syntax of SQL Server is as follows.

Select expressions 
FROM tables
[WHERE conditions]
[GROUP BY expressions] [HAVING condition] [ORDER BY expression [ ASC | DESC ]];

In this syntax,

  • expressions – expressions defined here the column(s) or calculation you want to retrieve. If you want to retrieve all the columns simply use * in the place of expressions.
  • tables – one or more than one table from where you want to retrieve data.
  • WHERE conditions – Optional. This is used to specify some conditions while selecting data. In case you are not using WHERE clause all the rows available will be selected.
  • GROUP BY expressions – Optional. It performs group operation on the resultset by one or more columns.
  • Having condition – Optional. This is used along with the GROUP BY expression to filter out the data. Having condition used to select only those records which met specific condition on GROUP BY.
  • ORDER BY – Optional. Sometimes we want to see the resultset in sorted manner. This argument is used to sort the resultset. If you want to sort on more than one column, you need to provide them in comma-separated.
  • ASC – Optional. ASC sort the resultset in ascending order. This is the default behaviour if no modifier is mentioned.
  • DESC – Optional. DESC sorts resultset in descending order by expression.

How SQL Server SELECT statement works?

In SQL Server the FROM clause is executed first and followed by SELECT although SELECT is written first.

SQL SERVER Select Statement

SQL Server SELECT statement examples

Let us see below how it works starting from the simplest (i.e. single table) and gradually moving up in complexity (i.e. multiple tables).

NOTE: Columns are also referred to as fields and the terms are used interchangeably.

1) SQL Server SELECT – Select All the Column form a Table

Suppose, we have an employee table with the following data.

empnoname city salary
1SagarKolkata85000
2ShankarNew Delhi80000
3KushalNoida70000
4RanjitAhmedabad60000
Employee Table

Now, let’s check how the SELECT statement works by selecting all columns from the employee table.

SELECT * FROM employee;

Once you execute the above SELECT statement, you will see all the records in the resultset.

Alternatively, you can filter out columns using where clause as below.

select *
from employee
where salary > 75000
order by name;

Output

In this example, we have used * to signify that we want to view all the columns from the table employee where salary is more than 75000. The resultset is sorted by name in ascending order.

2) SQL Server SELECT – Select Individual Fields from a Table

Sometime we may need to view only specific information from a table as compared to the all information available in the table. Suppose, we want to know only Name and the Salary of employees from the above employee table.

Now let’s demonstrate how to use the SQL SELECT statement to select specific rows from a table.

You can use the following SQL statement.

sql> select name,salary from employee;
+---------+--------+
| name | salary |
+---------+--------+
| Sagar | 85000 |
| Shankar | 80000 |
| Kushal | 70000 |
| Ranjit | 60000 |
+---------+--------+
4 rows in set (0.06 sec)

In the above example, all the rows have been returned as we haven’t used any filter condition here. But notice that all the columns haven’t returned this time, only specific columns have been returned. We have selected only name and salary from the employee table which has been shown above.

3) SQL Server SELECT – Select Individual Columns From Multiple Table

We can retrieve data specific columns from multiple tables using the SQL SELECT statement.

In this example, we have an employee table with the following data.

emp_noname city salary
1SagarKolkata85000
2ShankarNew Delhi80000
3KushalNoida70000
4RanjitAhmedabad60000
Employee Table (Containing data of Employees)

And the department table with the following data.

dept_nodept_name emp_no
1Production4
2Sales3
3Marketing1
4Marketing2
Department Table (Containing data of Department)

Now let’s select columns from these tables using SQL SELECT statement. You can follow the below statement.

sql> select employee.empno, employee.name , department.dept_name
-> from employee
-> inner join department
-> on employee.empno = department.emp_no
-> order by employee.name ASC;
+-------+---------+------------+
| empno | name | dept_name |
+-------+---------+------------+
| 3 | Kushal | Sales |
| 4 | Ranjit | Production |
| 1 | Sagar | Maketing |
| 2 | Shankar | Maketing |
+-------+---------+------------+
4 rows in set (0.04 sec)

In this example, we have used inner join to get data from multiple tables. Columns empno and name have been selected from the employee table and dept_name has been selected from the department table. Also, the resultset has been sorted on name in ascending order.

If you want to select all the fields from the employee table and dept_name from the department table, you can use the following SQL statement.

sql> select employee.*, department.dept_name
-> from employee
-> inner join department
-> on employee.empno = department.emp_no
-> order by employee.name ASC;
+-------+---------+-----------+--------+------------+
| empno | name | city | salary | dept_name |
+-------+---------+-----------+--------+------------+
| 3 | Kushal | Noida | 70000 | Sales |
| 4 | Ranjit | Ahmedabad | 60000 | Production |
| 1 | Sagar | Kolkata | 85000 | Maketing |
| 2 | Shankar | New Delhi | 80000 | Maketing |
+-------+---------+-----------+--------+------------+
4 rows in set (0.00 sec)

In this example, employee.* denotes that we want to select all the columns from the employee table and dept_name from the department table.

Was this tutorial helpful?
YesNo
SQL Server SELECT TOP :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.