Ways of using the SQL SELECT Statement

The data is stored in the tables in relational database management systems (RDBMS). In order to retrieve data from tables, we use SQL SELECT statement. For example,

SELECT * FROM table_name;

The data can be returned from single or multiple tables that can be filtered by using WHERE and HAVING clauses.

The data returned by the SELECT statement is stored in a resultant table called result-set.

In the following section, you can see a number of examples of using the SELECT statement to retrieve complete table data. I will also show how using WHERE clause, GROUP BY, HAVING, ORDER BY and others in the SELECT statement.

The example also covers using JOIN in the SELECT statement; so keep reading.

The example of fetching complete table data

For retrieving all records from the table, the simplest way is using the SELECT FROM statement with “*” as follows:

SELECT * FROM sto_employees;

SQL SELECT all

The above query retrieves all records from our example table, sto_employees.

Using specific columns in SELECT statement

Rather than fetching all columns, you may specify only the required column names in the SELECT command. Using the same table as in above example, the query retrieves only the employee name, age, and salary columns:

SQL SELECT columns

Filtering the records by WHERE clause in SELECT statement

You may require only a few records from the specified table rather than fetching all rows. Say, your table contains hundreds of thousands of rows then this is not wise retrieving all records while you need the data of few.

For filtering the rows, you may use the WHERE clause in SELECT statement with various operators. In this example, I will get the record of employee name ‘Michale’ and ‘Ben’ only from our example table.

The SELECT statement with WHERE clause:

The result-set:

SQL SELECT WHERE

In the above query, I used WHERE clause which is followed by column name and equal to operator. As emp_name contains VARCHAR values, so we enclosed the values in single quotes.

The example of using IN operator in SELECT statement

This example also searches the employee names by using the SQL IN operator in the WHERE clause. The IN operator is the short-cut for various OR operators in the WHERE clause.

The IN with WHERE clause in SELECT

The result-set:

SQL SELECT IN

The example of BETWEEN in SELECT statement

The BETWEEN operator allows specifying a range in the SELECT statement for filtering the records. The example below returns the records of employees for IDs between 3 to 7.

The SELECT query with BETWEEN:

The result-set:

SQL SELECT BETWEEN

The example of using LIKE operator in SELECT statement

The LIKE operator is used with the WHERE clause by specifying a wildcard. The wildcards can be an underscore (_) or modulus (%).

See an example of LIKE with SELECT statement:

The result:

SQL SELECT LIKE

SELECT statement with ORDER BY clause

The ORDER BY clause sorts the result-sets in ascending or descending order. This is used as the last clause where your may specify ASC or DESC keywords for sorting the results. The default is ascending order.

In this query, we will sort the results by employee’s salaries in descending order:

The records-set:

SQL SELECT ORDER-BY

Using COUNT function with SELECT statement demo

The SQL COUNT function is used to get the number of rows in a query. The COUNT is used with the SELECT statement with optionally WHERE clause, GROUP BY clause etc.

The following example shows a simple usage of COUNT function in the SELECT statement. We will get the total number of rows in our example table, sto_employees:

The output:

SQL SELECT COUNT

An example of COUNT and GROUP BY in SELECT statement

This query uses another table in our testing database. The table stores salaries paid to employees every month.

By using the COUNT function with GROUP BY clause, we will get the number of time salaries paid to each employee in the table:

The resultset:

SQL SELECT COUNT-2

Using SUM function example

This example uses SUM function in the SELECT SQL statement. The SUM gets the total for the specified numeric column.

The query:

The output:

SQL SELECT SUM

The SUM and GROUP BY in SELECT statement

By using GROUP BY with SUM function, we will get the total amount paid to each employee in our example table. See how these are used together:

The SELECT query with SUM and GROUP BY

The result-set:

SQL SELECT SUM 2

The example of SELECT statement with MAX function

The MAX function returns the maximum value in the specified column. The column can be numeric, date etc.

The MAX is an aggregated function that is used in the SELECT statement. An example query is shown below:

The result:

SQL SELECT MAX

The DISTINCT clause in SELECT statement example

The DISTINCT SQL clause is used to remove duplicate entries from the records returned by SELECT statement.

In our example table for employee’s salaries, you might notice the employee names have multiple entries.

By using the DISTINCT clause, see how it removes duplicates from the resultset. For seeing the difference, the first query returns complete data from the table and the second query uses SELECT/DISTINCT:

The results:

SQL SELECT DISTINCT

Using JOIN clause example

The JOIN clause enables combining the records from multiple tables by common keys in the SELECT statement. There are various types of JOINs available in SQL that you may learn in SQL JOIN tutorial.

The query below shows using an INNER JOIN which is the default JOIN type. It returns records from both tables that are matched:

JOIN & SELECT:

The resultset:

SQL SELECT JOIN

The LEFT OUTER JOIN query example

See another example of SELECT SQL statement with LEFT OUTER JOIN clause. The LEFT OUTER JOIN returns all records from the left table and only matched records from the right table.

The query: