SQL SELECT Statement

In SQL, the SELECT statement is used to query or retrieve data from a table in the database. The returns data is stored in a table, and the result table is known as result-set.This statement is a very powerful tool and its syntax is complex due to the many ways that tables, columns, functions and operators can be combined into legal statements.

Syntax

In the above syntax, <select_list> is a set of columns and expressions from the table listed in the <table_list>. The <table_list> is the list of tables from which rows are retrieved. The <condition> is a valid Oracle SQL condition used to restrict the rows fetched and the <column1> ,<column2>, …… <columnN> are the columns contained in the <table_list>. The components in the square bracket are optional.

The GROUP BY Clause is used to group the rows based on the distinct values that exist for the specified columns. The HAVING clause is used in combination with the GROUP BY clause to further restrict the retrieved rows. The ORDER BY clause is also optional and is used to order the rows that are returned by the query.

Following are the examples of how to use a SELECT statement in a table.

Use of the SELECT statement is to retrieve all columns in a table: To retrieve all the columns of the table, we use asterisk (*) wildcard character in the SELECT statement. It is most widely used when we don't know the column names or whenever you don’t want to type all the column names.

For example: Use the following syntax to select all the fields available in the table:

Let us take an example of an Employee table to fetch records using select statements.

EMPLOYEE Relation

Output:

Use of SELECT statement to retrieve the selective columns in a table: If we want to retrieve the selected column from a given table then specify the names of the columns from the <table_list> in the select statement. The order of the specification of the column names in the select statement need not necessarily be the same as the order of their appearance in the table.

For example: Use the following syntax to select the selected fields available in the table:

Example 1: Let us take an example of an Employee table to fetch the EMP_ID records of all the employees.

Output

Example 2: To fetch the EMP_NAME and SALARY, use the following query:

Use of SELECT statement to retrieve the DISTINCT Rows from a table: Whenever we use the SELECt statement to retrieve columns, it may result in duplicate rows. To return only one copy of each set of duplicate rows selected, we use the DISTINCT or UNIQUE keyword along with the SELECT statement.

For example: Use the following syntax to select the DISTINCT rows from a table.

Example 1: Let us take an example of an Employee table to fetch the records from a table.

Output

Use of SELECT statements to restrict rows using the WHERE clause in a table: If the table contains many rows you usually don’t want to retrieve all the rows from the table SQL provides a WHERE clause in which you specify the criteria for restricting the rows. The WHERE clause consists of one or more conditions that must be satisfied before a row is retrieved by the query. Within these conditions a variety of operators can be used to compare values.

For example: Use the following syntax to select the restricting rows from a table

Example 1: Let us take the following example to retrieve the employees information whose emp_id is 1 in a table.

Output:

This shows that you can specify columns in the WHERE clause even if they are not selected from the table.

If you want to specify more than one condition, then you can do this by using the logical operators AND and OR.

Example 2: Let us take the following example to retrieve the EMP_ID EMP_NAME, CITY, SALARY from the EMPLOYEE table where SALARY is greater than 15000 and CITY is equal to “Lucknow”.

Output:

Explanation: On execution of the above query, it consists of those rows which satisfy both the conditions in the “WHERE” Clause.

Example 3: Let us take the following example to retrieve the EMP_ID, CITY, SALARY from the EMPLOYEE table where SALARY is greater than 15000 or CITY is equal to “Lucknow”.

Output:

Explanation: On execution of the above query, it consists of those rows of EMPLOYEE relation which satisfies either of the conditions in the “WHERE” Clause.

The following points must be considered while using the SELECT statement.

• Character strings are case-sensitive. Therefore, EMP_NAME’s value has been specified in uppercase letters so that it matches the value in the table.
• The character and date values are enclosed in single quotation marks.
• Date values are format sensitive. The default format is DD-MON-YY.
• Number, constant should not be enclosed in quotation marks.

Use of expressions in the SELECT statement: The <select_list> can consist of a number of columns. In addition to multiple columns, you can specify expressions in the <select_list> which may consist of constants, functions etc.

Example 1: Let us take the following example to retrieve the employee’s name, total salary(SAL+COMM) information in the employee table.

Output:

Explanation: On execution of the above query, the SQL considers each element in the <select_list> to be a separate column even if that expression references multiple columns.

For Example: “SAL+COMM” will be considered as a single column. References Columns with ALIAS: When you specify the complex expression in a <select_list>, you can document what the expression represents by assigning an alias to it. You can also give an alias to the column of the table if necessary.

Example 1: Let us take the following example to retrieve the employee’s name, total salary(SAL+COMM) information in the employee table.

Output:

Explanation: On execution of the above query, the keyword “AS” is optional.

BY default, column names in the SELECT clause are listed in the query results in UPPER CASE as heading to the columns. Placing double quotes ("") around the column alias ensures case sensitivity of the column alias and allows you to specify spaces and special characters in the alias name. Aliasing helps to give short names for complex expressions.

Example:

Output:

Explanation: In the execution of the above query. Aliasing provides two benefits:

• You can give the name that accurately describes the expressions.
• You can reference aliases in the ORDER BY clause.

Use of concatenation operator in the SELECT statement: The concatenation operator is used to concatenate two or more strings. It is one of the important string operators in SQL. It is used to embed strings in the values returned by the query, to combine strings and to create new values that can be assigned to a column.

Example:

Output:

Explanation: On execution of the above query, the concatenation operator will concatenate the string ‘MY NAME IS’ with the EMP_NAME column.

Use of SELECT Statement with ORDER BY clause: Order by clause is used for arranging the data either in ascending or descending order. By default data is ordered in ascending order. With the use of keyword DESC after the column name we can arrange data in descending order.

For example: Use the following syntax to arrange the fields available in the table:

Syntax:

Example 1: Let us take the following example to retrieve the employee’s name, city, salary information of the employee table and the employee name as shown in ascending order.

Output:

Explanation: On execution of the above query, while specifying the ORDER BY CLAUSE with EMP_NAME column we have not specified the ascending or descending order still the order is that of ascending. This shows that default order is ascending.

Example 2: Let us take the following example to retrieve the employee’s name, city, salary information of the employee table and the employee name as shown in descending order.

Output:

Explanation: On execution of the above query, the retrieved records will be sorted by EMP_NAME in a descending order.