SQL SELECT Statement

An infographic guide to mastering data retrieval with SELECT statements, filtering, sorting, grouping and more.

Why do we use 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.

--Filter records by WHERE-clause
SELECT * FROM sto_employees
WHERE emp_name = 'Michale' OR emp_name = 'Ben';

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.

We will also show how to use the WHERE clause, GROUP BY, HAVING, ORDER BY, and others in the SELECT statement.

SELECT emp_name, COUNT(*) AS "Numbers Salary Paid"
FROM sto_emp_salary_paid
GROUP BY emp_name;

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 the 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 the above example, the query retrieves only the employee name, age, and salary columns:

SELECT emp_name, emp_age, emp_salary

FROM sto_employees;

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.

If your table contains hundreds of thousands of rows then this is not wise to retrieve all records while you need the data of few.

For filtering the rows, you may use the WHERE clause in the SELECT statement with various operators.

In this example, we will get the record of employee names ‘Michale’ and ‘Ben’ only from our example table.

The SELECT statement with WHERE clause:

SELECT * FROM sto_employees

WHERE emp_name = 'Michale' OR emp_name = 'Ben';

The result-set:

SQL SELECT WHERE

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

The example of using the IN operator in the SELECT statement

This example also searches the employee names by using the SQL IN operator in the WHERE clause.

The IN operator is the shortcut for various OR operators in the WHERE clause.

The IN with WHERE clause in SELECT

SELECT * FROM sto_employees

WHERE emp_name IN ('Ben', 'Mike', 'Jimmy', 'Jay');

The result-set:

SQL SELECT IN

The example of BETWEEN in SELECT statement

The example below returns the records of employees for IDs between 3 to 7.

The SELECT query with BETWEEN:

SELECT emp_name, emp_salary, join_date

FROM sto_employees

WHERE id BETWEEN 3 AND 7;

The result-set:

SQL SELECT BETWEEN

The BETWEEN operator allows specifying a range in the SELECT statement for filtering the records.

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 (_)
  • modulus (%)

See an example of LIKE with a SELECT statement:

SELECT emp_name, emp_salary, join_date

FROM sto_employees

WHERE emp_name LIKE 'J%';

The result:

SQL SELECT LIKE

SELECT statement with ORDER BY clause

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

SELECT emp_name, emp_salary, join_date, emp_age

FROM sto_employees

ORDER BY emp_salary DESC;

The records-set:

SQL SELECT ORDER-BY

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

Using COUNT function with SELECT statement demo

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:

SELECT COUNT(*) AS "Total Records in Employees Table"

FROM sto_employees;

The output:

SQL SELECT COUNT

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.

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 times salaries paid to each employee in the table:

SELECT emp_name, COUNT(*) AS "Numbers Salary Paid"

FROM sto_emp_salary_paid

GROUP BY emp_name;

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:

SELECT SUM(emp_sal_paid) As "Total Amount Paid - All time"

FROM sto_emp_salary_paid;

The output:

SQL SELECT SUM

The SUM and GROUP BY in the SELECT statement

By using GROUP BY with the 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

SELECT emp_name, SUM(emp_sal_paid) As "Total Amount Paid - Emp wise"

FROM sto_emp_salary_paid

GROUP BY emp_name

ORDER BY emp_name DESC;

The result-set:

SQL SELECT SUM 2

The example of a 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:
SELECT MAX(emp_age) As "Company's Oldest Employee Age"

FROM sto_employees;

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 the SELECT statement.

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

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

SELECT * FROM sto_emp_salary_paid;



SELECT DISTINCT(emp_name)

FROM sto_emp_salary_paid;

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:

SELECT * FROM sto_employees

JOIN sto_orders  ON sto_employees.id=sto_orders.emp_id;

The resultset:

SQL SELECT JOIN

The LEFT OUTER JOIN query example

See another example of a SQL SELECT statement with a 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:

SELECT emp_name, emp_age, emp_salary, order_date FROM sto_employees SE

LEFT OUTER JOIN sto_orders SO  ON SE.id=SO.emp_id;

 

Author - Atiq Zia

Atiq is the writer at jquery-az.com, an online tutorial website started in 2014. With a passion for coding and solutions, I navigate through various languages and frameworks. Follow along as we solve the mysteries of coding together!