Essential Concepts

A handful of techniques are central to writing efficient, sophisticated SQL queries, building on the Basic CRUD examples above.

Sorting

Sorting in SQL queries is achieved with the ORDER BY clause:

SELECT id, fullname, class FROM Students ORDER BY fullname;

SELECT id, fullname, class FROM Students ORDER BY fullname;

id	fullname	class
hh09hhhj	Alex Phil	2A
fsh8sd8f	Ivy Hugh	2D
g98fdhh8	Mike Tudor	1C

Sorts the result set by the fullname column, ascending.

A descending order is achieved by appending the sort field's name with the DESC keyword.

SELECT id, fullname, class FROM Students ORDER BY fullname DESC;

SELECT id, fullname, class FROM Students ORDER BY fullname DESC;

id	fullname	class
g98fdhh8	Mike Tudor	1C
fsh8sd8f	Ivy Hugh	2D
hh09hhhj	Alex Phil	2A

Sorts the result set by the fullname column, descending.

After being sorted once, records can be "further sorted" by comparing other fields when the first sort field yields the same values:

SELECT subj, dt, score FROM Tests ORDER BY subj DESC, dt DESC, score ASC;

SELECT subj, dt, score FROM Tests ORDER BY subj DESC, dt DESC, score ASC;

subj	dt	score
Maths	2023-05-13	57
Maths	2023-05-13	79
Maths	2023-03-21	45
Maths	2023-03-21	53
Maths	2023-01-05	45
Maths	2023-01-05	87
History	2023-05-18	99

Sorts primarily by subj (descending), then by dt (descending) to break ties, then by score (ascending) to break any remaining ties.

Limit

With one integer, the LIMIT n clause specifies the first n records retrieved:

SELECT subj, dt, score FROM Tests ORDER BY score LIMIT 3;

SELECT subj, dt, score FROM Tests ORDER BY score LIMIT 3;

subj	dt	score
Maths	2023-01-05	45
Maths	2023-03-21	45
Maths	2023-03-21	53

Returns only the first 3 rows of the sorted result set.

With two integers, the LIMIT m, n clause specifies the first n records retrieved after skipping an offset of m records:

SELECT subj, dt, score FROM Tests ORDER BY score LIMIT 2,3;

SELECT subj, dt, score FROM Tests ORDER BY score LIMIT 2,3;

subj	dt	score
Maths	2023-03-21	53
Maths	2023-05-13	57
Maths	2023-05-13	79

Skips the first 2 rows of the sorted result set, then returns the next 3.

To retrieve a random record:

SELECT subj, dt, score FROM Tests ORDER BY RAND() LIMIT 1;

SELECT subj, dt, score FROM Tests ORDER BY RAND() LIMIT 1;

Assigns a random value to every row, sorts by it, then returns the first row.

Using ORDER BY RAND() can be inefficient for large tables, since it requires MySQL to assign random values to all rows before sorting. For a substantial table, it is recommended to explore alternative methods for retrieving random records, such as building and executing a prepared statement instead:

SET @n = (SELECT COUNT(*) FROM Tests);
SET @n = FLOOR(RAND() * @n);
SET @s = CONCAT('SELECT subj, dt, score FROM Tests LIMIT ',@n,",1;");
PREPARE ps FROM @s;
EXECUTE ps;
DEALLOCATE PREPARE ps;

SET @n = (SELECT COUNT(*) FROM Tests);
SET @n = FLOOR(RAND() * @n);
SET @s = CONCAT('SELECT subj, dt, score FROM Tests LIMIT ',@n,",1;");
PREPARE ps FROM @s;
EXECUTE ps;
DEALLOCATE PREPARE ps;

Computes a random offset in a session variable, builds a SELECT ... LIMIT statement as a string, then prepares and executes it. See Injection Attacks for more on prepared statements.

Aggregate

In SQL, aggregates are functions that perform calculations on a set of values and return a single value as the result. Aggregates are commonly used to summarize data and perform calculations across multiple rows of a table.

Common SQL aggregates include:

SUM: Returns the total of all the values in a specified column.
AVG: Returns the average of all the values in a specified column.
COUNT: Returns the number of rows in a specified column.
MAX: Returns the maximum value in a specified column.
MIN: Returns the minimum value in a specified column.

Aggregates can be used with the GROUP BY clause to group data based on one or more columns, then perform the calculation on each group separately. For example, the SUM aggregate could calculate the total sales for each product category in a sales table.

SELECT subj, AVG(score) FROM Tests GROUP BY subj;

SELECT subj, AVG(score) FROM Tests GROUP BY subj;

subj	AVG(score)
History	99.0000
Maths	61.0000

Returns the average score for each distinct subject.

When using aggregates in SQL, any non-aggregated columns included in the SELECT statement must also be included in the GROUP BY clause. See Aggregate for a fuller reference on aggregate functions.

Join

In SQL, joins are used to combine rows from two or more tables based on a related column between them. Different tables can be "joined" to relate data across them.

SELECT * FROM Students JOIN Tests ON Students.id=Tests.student_id;

SELECT * FROM Students JOIN Tests ON Students.id=Tests.student_id;

id	fullname	dob	class	test_id	student_id	subj	dt	score
hh09hhhj	Alex Phil	2010-12-11	2A	M_sasgkj	hh09hhhj	Maths	2023-01-05	87
hh09hhhj	Alex Phil	2010-12-11	2A	M_lgjg88	hh09hhhj	Maths	2023-03-21	53
hh09hhhj	Alex Phil	2010-12-11	2A	M_qas9ij	hh09hhhj	Maths	2023-05-13	57
g98fdhh8	Mike Tudor	2011-05-28	1C	H_sdjksd	g98fdhh8	History	2023-05-18	99
fsh8sd8f	Ivy Hugh	2010-10-23	2D	M_sasgkj	fsh8sd8f	Maths	2023-01-05	45
fsh8sd8f	Ivy Hugh	2010-10-23	2D	M_lgjg88	fsh8sd8f	Maths	2023-03-21	45
fsh8sd8f	Ivy Hugh	2010-10-23	2D	M_qas9ij	fsh8sd8f	Maths	2023-05-13	79

An INNER JOIN combining every Students row with its matching Tests rows. The asterisk (*) denotes all fields.

There are several types of joins in SQL, including:

INNER JOIN (example above): Returns only the matching rows from both tables based on the join condition.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. If there is no match in the right table, the result contains NULL values for the right table's columns.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. If there is no match in the left table, the result contains NULL values for the left table's columns.
FULL OUTER JOIN (or FULL JOIN): Returns all rows from both tables, including non-matching rows. Non-matching columns are filled with NULL values.
CROSS JOIN: Returns the Cartesian product of the two tables, combining every row of the first table with every row of the second table. It does not require a join condition.
SELF JOIN: Joins a table with itself, allowing rows within the same table to be combined based on a related column.

Each type of join serves a different purpose depending on the data being retrieved.

Indexes and Constraints

Recall how the student's id was designated a PRIMARY KEY in Basic CRUD, referenced by the FOREIGN KEY in each test record.

A primary key is an index in SQL. Defining a primary key on a table creates a unique index that enforces the primary key constraint. This index is used to speed up searches and queries that use the primary key column(s).

Defining a primary key on a table can potentially slow down writes, since it requires additional overhead to enforce the uniqueness constraint - the database engine must check each new record against the primary key index to ensure the key values are unique. This additional check can result in slower write performance, especially when inserting or updating large amounts of data.

However, the benefits of a primary key, such as improved read performance and data integrity, generally outweigh the potential impact on write performance.

To minimize the impact on write performance, consider the following best practices:

Keep the primary key simple and concise, consisting of as few columns as possible.
Avoid using a primary key that is frequently updated or changed.
Use non-clustered indexes for other columns that are frequently searched or queried.
Regularly monitor and optimize database performance to ensure it remains optimal.

A foreign key establishes a relationship between two tables. It is a column or set of columns in one table that refers to the primary key of another table, creating a link between the two tables that enforces referential integrity and keeps the data consistent and accurate. See Indexes for a fuller reference on defining indexes.

Subquery

The result of a scalar (0 dimension), vector (1 dimension), or table (2 dimensions) "subquery" can be used within another query, ie. an inner SELECT statement nested within an outer SELECT statement. Subqueries can be nested repeatedly.

SELECT * FROM Tests
WHERE subj='Maths' and score >
(SELECT AVG(score) FROM Tests GROUP BY subj HAVING subj='Maths');

SELECT * FROM Tests
WHERE subj='Maths' and score >
(SELECT AVG(score) FROM Tests GROUP BY subj HAVING subj='Maths');

test_id	student_id	subj	dt	score
M_sasgkj	hh09hhhj	Maths	2023-01-05	87
M_qas9ij	fsh8sd8f	Maths	2023-05-13	79

A scalar subquery: retrieves every Maths test row scoring above the average Maths score.

See Subquery for a deeper treatment of subqueries.

CTE

A Common Table Expression (CTE) is a temporary result set that is predefined for use by the later parts of an SQL statement. It behaves like an ordinary table, as shown in the example below.

See CTE for a deeper treatment of common table expressions, including recursive CTEs.

ch01-cte-example.sql:

WITH
best_scores AS (
   SELECT student_id, subj, MAX(score) as max_score
      FROM tests GROUP BY student_id, subj
)
SELECT fullname, subj, max_score FROM students JOIN best_scores ON
students.id=best_scores.student_id;

WITH
best_scores AS (
   SELECT student_id, subj, MAX(score) as max_score
      FROM tests GROUP BY student_id, subj
)
SELECT fullname, subj, max_score FROM students JOIN best_scores ON
students.id=best_scores.student_id;

fullname	subj	max_score
Alex Phil	Maths	87
Mike Tudor	History	99
Ivy Hugh	Maths	79

Basic CRUD	<<<	SQL	>>>	Data Definition Language
last update: 17 Aug 2026

MENU