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;
We can select specific column names from a table as well.
Query
SELECT INVENTORY_ID, CUSTOMER_ID, RETURN_DATE FROM RENTAL LIMIT 12;
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;
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);
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;
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;
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;
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;
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;
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;
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.
You can download the full source code of this example here: SQL SELECT Statement Example
