sql

SQL SELECT Statement Example

1. Introduction

In this article, we will look at one of the most used clauses used in SQL known as SELECT.

1.1 What is SQL?

In 1974, Donald Chamberlin and Robert Boyce designed a programming language called SQL or “sequel”. It is based on relational algebra and tuple-relational calculus. So, this is the programming language for designing and maintaining a Relational database management system (RDBMS). Depending upon the RDBMS used, the SQL can have dialects. For example, Oracle uses PL/SQL and MS SQL Server uses T-SQL and MySQL uses SQL.

1.2 Setup

This is the setup for running queries –

  • MySQL Community Server version 8.0.22. To install, please visit the MySQL Community Downloads page. Its documentation is available here.
  • For running queries, we use the MySQL 8.0 Workbench which is both a part of the community package and a separate download.
  • SAKILA Database is provided by MySQL itself. The documentation provides details about the structure, installation steps(if any), Schema details, etc.

2. SQL SELECT Clause

In this article, we are going to look at one of the most used clauses known as the SELECT Clause which extracts data from a database.

2.1 Select in Relational Algebra

As per Relational Algebra, the select operator selects a subset of tuples(rows) according to the given condition. The symbol to denote Select is σp(r)

where,

σ = predicate
p = prepositional logic i.e. conditions put on it
r = relation i.e table_name

example

σ amount < 2.99(Payment)

this will return all the records from the Payment table where amount > 2.99

2.2 SELECT CLAUSE

  • The Select clause makes up an integral part of queries in the database. Like in Relational Algebra the select statement retrieves one or more records from one or more tables. The result set is tuples(rows).
  • Select clauses are a part of the Data Manipulation Language (DML) commands.
  • It is one of the most complex statements in SQL which has many optional clauses attached to it.
  • The clauses are as follows:
    • FROM: The FROM Clause retrieves data from the Tables mentioned. We also use an optional JOIN clause.
    • Where: Where Clause is the conditions or restrictions put on the records. The conditions are the “predicate”.
    • GROUP BY: Group by clause is to club rows having similar values into smaller groups.
    • Having: The Having clause filters rows from the Group by and aggregate functions.
    • Order by: This clause sorts the result set based on some column. By default, Order by sorts columns in the Ascending order.
    • Distinct: The Distinct keyword eliminates duplicate rows or tuples.
    • The asterisk (*) is a special column name we use to get all the columns from the table or tables used in the from clause.

Because it is just retrieval of records, the select statement does not alter the database. Besides this, the “SELECT INTO” clause modifies variables, files, and sometimes tables. More details are available on the SELECT INTO Documentation.

As per MySQL Documentation the syntax of the Select clause with all its optional clauses is

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr] ...
    [into_option]
    [FROM table_references
      [PARTITION partition_list]]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [into_option]
    [FOR {UPDATE | SHARE}
        [OF tbl_name [, tbl_name] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [into_option]

into_option: {
    INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name] ...
}

2.1 Simplest SQL Syntax

The simplest Select syntax is just a select query without even the from clause.

 Select 1 + 2;

which is equivalent to

 SELECT 1+ 2 FROM DUAL;

3. Examples

Next, we will look at different examples. We will look at some clauses used with Select.

3.1 SELECT with FROM Clause

To get all records, we use *. But if the table is big (like the one in the example which has 16,000+ records), it is advisable to use a LIMIT clause.

Query

SELECT * 
FROM RENTAL 
LIMIT 10;
SQL SELECT - Select all columns from a table
Select all columns from table

We can select specific column names from a table as well.

Query

SELECT INVENTORY_ID, CUSTOMER_ID, RETURN_DATE
FROM RENTAL
LIMIT 12;
SQL SELECT - select specific column names from table
Select specific columns

3.2 Select with FROM and WHERE CLAUSE

Select Clause can have a where clause and can also form subqueries or joins. The Where clause filters records based on predicates i.e. conditions.

Where with a predicate

SELECT INVENTORY_ID,CUSTOMER_ID,RETURN_DATE
FROM RENTAL
WHERE CUSTOMER_ID =459;
SQL SELECT - Select with Where clause
Select with Where clause

Query for where with a subquery:

SELECT *
FROM PAYMENT
WHERE AMOUNT >=2.99 
AND CUSTOMER_ID = 1
AND RENTAL_ID IN (SELECT RENTAL_ID from RENTAL);
SQL SELECT - subquery
Subquery

3.3 SELECT WITH Aggregate functions and group by function

We can Select queries with Aggregate functions like AVG, MAX, COUNT. Along with the Aggregate functions we use Having clause because it filters the result set returned by aggregate functions.

3.3.1 Aggregate function Query

SELECT AVG(AMOUNT),CUSTOMER_ID
FROM PAYMENT
GROUP BY CUSTOMER_ID;
SQL SELECT - Select with aggregate functions
Select with aggregate functions

3.3.2 Having Clause Query:

The having clause is used to filter the aggregate function result set.

SELECT C.CUSTOMER_ID,C.FIRST_NAME,C.LAST_NAME
FROM PAYMENT P,CUSTOMER C
WHERE P.CUSTOMER_ID = C.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
HAVING AVG(P.AMOUNT) > 3
LIMIT 20;
SQL SELECT - Select with a Having clause
Select with a Having clause

3.3.3 Select in Having Clause Query

Having clause can have select to form a subquery

SELECT P.CUSTOMER_ID,FIRST_NAME,LAST_NAME,EMAIL
FROM PAYMENT P,CUSTOMER C
WHERE P.CUSTOMER_ID=P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
HAVING MAX(P.AMOUNT) > (SELECT AVG(AMOUNT) FROM PAYMENT)
LIMIT 20;
Having with subquery
Having with subquery

3.3.4 Query for

The SQL Select statement is used extensively when creating Joins on tables. More on Joins here.

The following query gives back the business done by the 2 DVD-rental stores

SELECT S1.STORE_ID, S2.SALES 
FROM STORE S1
JOIN (
	SELECT CUS.STORE_ID, SUM(PAY.AMOUNT) SALES
	FROM CUSTOMER CUS
	JOIN PAYMENT PAY
	ON PAY.CUSTOMER_ID = CUS.CUSTOMER_ID
    GROUP BY CUS.STORE_ID
  ) S2
ON S1.STORE_ID = S2.STORE_ID
ORDER BY S1.STORE_ID;
join clause
Join Clause

3.3.5 Select with In-Built Functions

In-built functions like ROW_NUMBER or RANK can be used with Select. Below Query Shows an example of a ROW_NUMBER function in MySQL

select ROW_NUMBER() OVER (ORDER BY AMOUNT DESC) CUSTOMER_ID, AMOUNT
FROM PAYMENT
LIMIT 10;

row number function
row number function

3.3.6 Select in Views

Views or Inline Views use select statements as well and below given Query is one such View related to Documentary Films

Query for View creation

CREATE VIEW DOCUMENTARY_FILM_LIST AS 
SELECT F.TITLE,F.DESCRIPTION,F.LENGTH,F.RATING,GROUP_CONCAT(CONCAT(A.FIRST_NAME,A.LAST_NAME) SEPARATOR ', ') AS `ACTORS`
FROM FILM_ACTOR FA, FILM F ,ACTOR A
WHERE F.FILM_ID = FA.FILM_ID 
AND A.ACTOR_ID = FA.ACTOR_ID
AND F.FILM_ID IN (
SELECT FILM_ID FROM FILM_CATEGORY, CATEGORY
WHERE CATEGORY.CATEGORY_ID = FILM_CATEGORY.CATEGORY_ID
AND CATEGORY.CATEGORY_ID = 6)
GROUP BY F.FILM_ID;

Query to get Output:

SELECT * FROM DOCUMENTARY_FILM_LIST;
View Query.
View Query

4. Summary

In this article, we saw one of the most used statements in SQL i.e. the SELECT Clause. We saw a lot of examples with various clauses that are also used with the SELECT Clause, although a lot more combinations are possible.

5. Download the Source Code

This was an example of the SQL SELECT Clause using MySQL RDBMS.

Download
You can download the full source code of this example here: SQL SELECT Statement Example

Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button