SQL SELECT Statement

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

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

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:

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;

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:

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 with WHERE clause in SELECT

SELECT * FROM sto_employees

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

The result-set:

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:

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:

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:

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:

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:

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:

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:

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.

SELECT MAX(emp_age) As "Company's Oldest Employee Age"

FROM sto_employees;

The result:

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:

Using JOIN clause example

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:

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;