SELECT Statement

The select statement is used to fetch data from the database tables. Once the table is created in the database and data is inserted into that, the next step is to check whether the data is properly inserted. To check data in a table we have to use a SELECT statement.

The SELECT statement is used to retrieve or fetch data from tables the result set is returned in the form of tables or you can use available options to view the data in the table.
Clause and operators are used along with the SELECT statement in order to retrieve the data from the table or to filter data from the table.

SELECT statement

 

Applications of the SELECT Statement

  • Data Retrieval: Data retrieval from one or more database tables is the main goal of the SELECT statement.
  • Data Filtering: Use the WHERE clause to filter data based on specified conditions.
  • Data Aggregation: Employ aggregate functions like SUM, COUNT, AVG, etc., to perform calculations on data sets.
  • Joining Tables: Combine data from multiple tables using JOIN operations.
  • Data Sorting: Utilize the ORDER BY clause to sort data in ascending or descending order.

Best Practices for Using the SELECT Statement

  • Column Aliases: Use column aliases for readability in the output. For example, SELECT column1 AS ‘Alias’.
  • Explicit Column Names: Specify column names explicitly to avoid unexpected issues.
  • **Avoid SELECT ***: Instead of selecting all columns (SELECT *), explicitly list the columns you need. This enhances performance and avoids unnecessary data transfer.
  • Use WHERE Judiciously: Apply WHERE conditions thoughtfully to retrieve relevant data.
  • ORDER BY Wisely: If sorting is needed, use ORDER BY judiciously and consider indexing columns used in sorting.

Optimizing SELECT Statement Performance

  • Indexes: Properly indexed columns can significantly boost SELECT statement performance.
  • Limit Result Size: Retrieve only the necessary rows and columns to minimize data transfer.
  • Use EXPLAIN: Understand the execution plan using the EXPLAIN statement to optimize query performance.

Syntax and Example

In the below syntax, 1st syntax contains list columns with select statements to fetch the data from the columns which are in the select list for them only the data will be retrieved.
In the 2nd syntax, the * symbol is used along with a select statement indicating all column values must be fetched from the table.
The syntax of the SELECT statement is as follows −

1) SELECT column11, column12, columnN FROM tablename where condition; 
2) select * from tablename

 

Breaking it down:

column1, column2, …: Columns you want to retrieve data from.
tablename: The name of the table containing the data.
condition: Optional condition to filter the data.

Below is the Employeedtbl table created in hrs schema which holds the data about the employees. This example shows how to retrieve all records from the table. The select statement along with symbol * from and tablename is used to retrieve the data from the table.

Query:- 
select * from hrs.Employeedtbl

QueryOutput:-
 
empid           roletitle                                          dob
--------------- -------------------------------------------------- ----------
295847284       Chief Executive Officer                            1969-01-29
245797967       Vice President of Engineering                      1971-08-01
509647174       Engineering Manager                                1974-11-12
112457891       Senior Tool Designer                               1974-12-23
695256908       Design Engineer                                    1952-09-27
998320692       Design Engineer                                    1959-03-11
134969118       Research and Development Manager                   1987-02-24
811994146       Research and Development Engineer                  1986-06-05
658797903       Research and Development Engineer                  1979-01-21
879342154       Research and Development Manager                   1984-11-30

 

Syntax and Example

The below example contains data for the column empid, roletitle of Employeedtbl tables.

Query:- 
 select  empid, roletitle from hrs.Employeedtbl

QueryOutput:-
empid           roletitle
--------------- --------------------------------------------------
295847284       Chief Executive Officer
245797967       Vice President of Engineering
509647174       Engineering Manager
112457891       Senior Tool Designer
695256908       Design Engineer
998320692       Design Engineer
134969118       Research and Development Manager
811994146       Research and Development Engineer
658797903       Research and Development Engineer
879342154       Research and Development Manager
974026903       Senior Tool Designer
480168528       Tool Designer
486228782       Tool Designer
42487730        Senior Design Engineer
56920285        Design Engineer


Using Alias in Select statement

 

The Alias is used in the select statement on the columns which are in the select list. The alias is used to represent the column name for the end user’s better understanding.

 

Syntax and Example

The below example, shows empid column is represented as employeeID, and dob column as DateofBirth of Employeedtbl table. whenever the end user uses this query, they will come to know which employeeid , their  date of birth.

Query:- 

select empid as employeeID, dob as DateofBirth from hrs.Employeedtbl

QueryOutput:-

employeeID      DateofBirth
--------------- -----------
295847284       1969-01-29
245797967       1971-08-01
509647174       1974-11-12
112457891       1974-12-23
695256908       1952-09-27
998320692       1959-03-11
134969118       1987-02-24
811994146       1986-06-05
658797903       1979-01-21
879342154       1984-11-30
974026903       1978-01-17
480168528       1959-07-29
486228782       1989-05-28
42487730        1979-06-16
56920285        1961-05-02
24756624        1975-03-19
253022876       1987-05-03
222969461       1978-03-06
52541318        1978-01-29
323403273       1975-03-17

 

Using TOP with SQL Statement

 

The TOP Keyword is used along with select statement to fetch a couple of records from the database. Usually, the TOP keyword is used for sample data checks. while testing and when the table has huge data in that case by applying a filter user fetch the data from the table.

 

Syntax and Example

The below example fetches only 10 records from the table. If we define 15,20 instead of it it will return those number of records from the table. We have fetched 10 records for all the columns of Employeedtbl table.

Query:- 

 select top 10 *  from hrs.Employeedtbl

QueryOutput:-

empid           roletitle                                          dob
--------------- -------------------------------------------------- ----------
295847284       Chief Executive Officer                            1969-01-29
245797967       Vice President of Engineering                      1971-08-01
509647174       Engineering Manager                                1974-11-12
112457891       Senior Tool Designer                               1974-12-23
695256908       Design Engineer                                    1952-09-27
998320692       Design Engineer                                    1959-03-11
134969118       Research and Development Manager                   1987-02-24
811994146       Research and Development Engineer                  1986-06-05
658797903       Research and Development Engineer                  1979-01-21
879342154       Research and Development Manager                   1984-11-30

We can use the top keyword along with a list of columns. In the below example, top 10 records fetched for the columns empid, roletitle, dob, and MaritalStatus from Employeedtbl tables.

Syntax and Example

Query:- 

 select top 10 empid, dob    from hrs.Employeedtbl

 select top 10 *  from hrs.Employeedtbl

QueryOutput:-

empid           dob
--------------- ----------
295847284       1969-01-29
245797967       1971-08-01
509647174       1974-11-12
112457891       1974-12-23
695256908       1952-09-27
998320692       1959-03-11
134969118       1987-02-24
811994146       1986-06-05
658797903       1979-01-21
879342154       1984-11-30

 

Similar Reads

What is join?

What are commands in SQL?

What is index?

What is subquery?

What is Select into and How to Use It?

1 thought on “SELECT Statement”

Comments are closed.