In this post we will learn how to retrieve data from one or more tables with select statement in T-SQL. The select query is one of the most important queries in SQL because apart from its simple usage, it is used as sub-query in update, insert and delete queries. We will also learn about sub-query and join operations to retrieve data or result set by querying one or more tables in later posts.
We will use SSMS (SQL Server Management Studio) client tool to query database. There are also third party tools available to query database but in most situations SSMS is perfect. First of all we look at basic syntax of select query. The basic syntax of select statement includes three clauses- select, from and where.
Select Statement: Select statement has a number of clauses which are written in a specific order without which the statement will be wrong in syntax and will throw error if statement will be executed. Note that all the clauses available in select statement are not mandatory; most of them are optional except select and from clauses. We will see each of these clauses one by one. Even from clause is optional if you are not using a table with select query. If you write an expression after select then it will return the value of the expression. The expression can be a literal, function etc.
SELECT clause: The select clause is the first clause in select statement and is mandatory. It includes different columns of source table or expressions. The columns or the expressions are written after select keyword and they are separated by comma delimiter. Select clause is used for projection operation on table or result set. Projection on a table may reduce the number of columns in the table or result set.
INTO clause: The next clause used in the select statement is into clause which is used to insert records from other table or record set. The INTO clause is optional and it is required only when you are going to copying into another table or record set from source table.
FROM clause: The FROM clause is used after select clause if into clause is not used. It is mandatory and it includes source table or record set. All or a few columns of the source table can be projected as columns of the final result set.
WHERE clause: The where clause is the next clause after from clause in select statement which is optional and is used to filter the result set. The where clause is row sub setting clause because the conditional expression used in where clause filters the rows of table or result set given in from clause. Suppose we are given employees table and we want to get only male employees then in this case we will use where clause to filter rows. We will use conditional expression after where keyword to get only male employees.
GROUP BY clause: The group by clause is the fifth clause in the select statement. This clause includes grouping expression after GROUP BY keyword. In most cases grouping is based on some class variable or field given in select clause. In simple cases, we have just single column grouping.
For example, we can group employees based on their gender only. But we can group based on more than one column also. For example employees can be grouped first on the basis of gender and then on the basis of location of residence. This will be the case of multi column grouping. Note that the order in which grouping fields are specified in the group by clause is going to matter in the final result set. For example employees are group first on the basis of gender and then on the basis of their residence then the result set will be different than if employees are grouped first on the basis of residence and then on the basis of gender.
WITH clause: Next to the group by clause is with clause which uses rollup and cube options. Rollup and cube are used for dicing and sliding of record set and they are useful in OLAP data analysis. OLAP stands for online analytical data processing.
HAVING clause: Having clause in the select statement is used after with clause. If with clause is missing then it will be after the group by clause. The having clause is used to filter records when records have already been grouped on the basis of group by clause. It means that having clause is used together with group by clause. Without group by clause, having clause is not used. The group by clause sets the stage so that having clause can be used. It means that what the result set is returned by the group by clause, on that result set having clause operation is applied. In the having clause we use a search condition to filter already grouped rows done on the basis of group by clause.
For example if you want to get the average salary of male and female employees then you will have to group the employees on the basis of gender using group by clause. But to find maximum average salary between male and female you will have to set conditional expression on the having clause after group by clause.
ORDER BY clause: The order by clause is usually the last clause in the select statement which is used to sort the result set based on some columns. The columns name or their ordinal positional values are given after the order by keyword. We can sort the records in ascending or descending order by specifying ASC and DESC keywords after the columns expressions respectively. For example we can sort an employee on the basis of salary.
COMPUTE clause: We can also use compute clause after order by clause. The compute clause is used to create an additional row in result set to get total value, average value, maximum value or minimum value based on some columns or expressions.
In this post we have acquainted ourselves with different clauses used in the select statement. In the next post we will learn about these different clauses of SELECT statement with practical examples in detail one by one.
No comments:
Post a Comment