This guide explains the SELECT statement in detail, covering how to retrieve, filter, limit, and sort data from your MariaDB database.
This guide explains how to retrieve data from MariaDB using the SELECT statement, progressing from basic syntax to more involved queries. Learn to select specific columns, limit results, filter with WHERE, sort with ORDER BY, join tables, and use various helpful options and functions.
To follow the examples, first create and populate the books and authors tables:
Selecting All Columns:
Use * to select all columns from a table.
Output (example):
Selecting Specific Columns:
List the column names separated by commas.
Output (example):
Limiting the Number of Rows with LIMIT:
To get the first N rows:
Output (example):
To get N rows starting from an offset (offset is 0-indexed):SQL
Output (example, assuming only 3 more rows exist after offset 5):
Filtering with WHERE:
Use the WHERE clause to specify conditions for row selection.
Output (example):
Ordering with ORDER BY:
Use ORDER BY column_name [ASC|DESC] to sort the result set.
Output (example):
ASC (ascending) is the default order. DESC is for descending order.
You can order by multiple columns: ORDER BY col1 ASC, col2 DESC.
Clause Order: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT .... MariaDB generally processes WHERE
Joining Tables:
Use JOIN to combine rows from two or more tables based on a related column.
Output (example):
Alternative JOIN syntax: ... JOIN authors ON books.author_id = authors.author_id .... For more on joins, see the or a "Basic Joins Guide".
CONCAT(str1, str2, ...): Concatenates strings.
AS alias_name: Assigns an alias to an output column.
Pattern Matching with LIKE:
Use LIKE in the WHERE clause for pattern matching. % is a wildcard for zero or more characters.
Output (example, same as above if only Dostoevsky matches):
Place these modifiers immediately after the SELECT keyword.
ALL vs DISTINCT:
ALL (default): Returns all rows that meet the criteria.
DISTINCT: Returns only unique rows for the selected columns. If multiple identical rows are found for the specified columns, only the first one is displayed.
This page is licensed: CC BY-SA / Gnu FDL
ORDER BYLIMITOutput (example, showing one "Crime & Punishment"):
HIGH_PRIORITY:
Gives the SELECT statement higher priority over concurrent data modification statements (use with caution as it can impact write performance).
SQL
SQL_CALC_FOUND_ROWS and FOUND_ROWS():
To find out how many rows a query would have returned without a LIMIT clause, use SQL_CALC_FOUND_ROWS in your SELECT statement, and then execute SELECT FOUND_ROWS(); immediately after.
Output (example for the first query):
Then, to get the total count:
Output (example, if 6 Dostoevsky books in total):
The value from FOUND_ROWS() is temporary and specific to the current session.
SELECT DISTINCT HIGH_PRIORITY title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;SELECT SQL_CALC_FOUND_ROWS isbn, title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title -- Order before limit to ensure consistent FOUND_ROWS() for a given query logic
LIMIT 5;+------------+------------------------+
| isbn | title |
+------------+------------------------+
| 0805210644 | America |
| 0553212168 | Brothers Karamozov |
| 0553211757 | Crime & Punishment |
| 0679420290 | Crime & Punishment |
| 0192834118 | Idiot |
+------------+------------------------+
5 rows in set (0.001 sec)SELECT FOUND_ROWS();CREATE OR REPLACE TABLE books (
isbn CHAR(20) PRIMARY KEY,
title VARCHAR(50),
author_id INT,
publisher_id INT,
year_pub CHAR(4),
description TEXT
);
CREATE OR REPLACE TABLE authors (
author_id INT AUTO_INCREMENT PRIMARY KEY,
name_last VARCHAR(50),
name_first VARCHAR(50),
country VARCHAR(50)
);
INSERT INTO authors (name_last, name_first, country) VALUES
('Kafka', 'Franz', 'Czech Republic'),
('Dostoevsky', 'Fyodor', 'Russia');
INSERT INTO books (title, author_id, isbn, year_pub) VALUES
('The Trial', 1, '0805210407', '1995'),
('The Metamorphosis', 1, '0553213695', '1995'),
('America', 2, '0805210644', '1995'), -- Note: Original data had author_id 2 for 'America', Dostoevsky is author_id 2.
('Brothers Karamozov', 2, '0553212168', ''),
('Crime & Punishment', 2, '0679420290', ''),
('Crime & Punishment', 2, '0553211757', ''),
('Idiot', 2, '0192834118', ''),
('Notes from Underground', 2, '067973452X', '');SELECT * FROM books;+------------+------------------------+-----------+--------------+----------+-------------+
| isbn | title | author_id | publisher_id | year_pub | description |
+------------+------------------------+-----------+--------------+----------+-------------+
| 0192834118 | Idiot | 2 | NULL | | NULL |
| 0553211757 | Crime & Punishment | 2 | NULL | | NULL |
... (other rows)
| 0805210644 | America | 2 | NULL | 1995 | NULL |
+------------+------------------------+-----------+--------------+----------+-------------+
8 rows in set (0.001 sec)SELECT isbn, title, author_id FROM books;+------------+------------------------+-----------+
| isbn | title | author_id |
+------------+------------------------+-----------+
| 0192834118 | Idiot | 2 |
| 0553211757 | Crime & Punishment | 2 |
... (other rows)
+------------+------------------------+-----------+
8 rows in set (0.001 sec)SELECT isbn, title, author_id FROM books LIMIT 5;+------------+--------------------+-----------+
| isbn | title | author_id |
+------------+--------------------+-----------+
| 0192834118 | Idiot | 2 |
| 0553211757 | Crime & Punishment | 2 |
| 0553212168 | Brothers Karamozov | 2 |
| 0553213695 | The Metamorphosis | 1 |
| 0679420290 | Crime & Punishment | 2 |
+------------+--------------------+-----------+
5 rows in set (0.001 sec)SELECT isbn, title, author_id FROM books LIMIT 5, 10; -- Skip 5 rows, show next 10 (or fewer if less remain)+------------+------------------------+-----------+
| isbn | title | author_id |
+------------+------------------------+-----------+
| 067973452X | Notes from Underground | 2 |
| 0805210407 | The Trial | 1 |
| 0805210644 | America | 2 |
+------------+------------------------+-----------+
3 rows in set (0.001 sec)SELECT isbn, title
FROM books
WHERE author_id = 2
LIMIT 5;+------------+------------------------+
| isbn | title |
+------------+------------------------+
| 0192834118 | Idiot |
| 0553211757 | Crime & Punishment |
| 0553212168 | Brothers Karamozov |
| 0679420290 | Crime & Punishment |
| 067973452X | Notes from Underground |
+------------+------------------------+
5 rows in set (0.000 sec)SELECT isbn, title
FROM books
WHERE author_id = 2
ORDER BY title ASC
LIMIT 5;+------------+--------------------+
| isbn | title |
+------------+--------------------+
| 0805210644 | America |
| 0553212168 | Brothers Karamozov |
| 0553211757 | Crime & Punishment |
| 0679420290 | Crime & Punishment |
| 0192834118 | Idiot |
+------------+--------------------+
5 rows in set (0.001 sec)SELECT isbn, title, CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id) -- Assumes 'author_id' column exists in both tables
WHERE name_last = 'Dostoevsky'
ORDER BY title ASC
LIMIT 5;+------------+--------------------+-------------------+
| isbn | title | author |
+------------+--------------------+-------------------+
| 0805210644 | America | Fyodor Dostoevsky |
| 0553212168 | Brothers Karamozov | Fyodor Dostoevsky |
| 0553211757 | Crime & Punishment | Fyodor Dostoevsky |
| 0679420290 | Crime & Punishment | Fyodor Dostoevsky |
| 0192834118 | Idiot | Fyodor Dostoevsky |
+------------+--------------------+-------------------+
5 rows in set (0.00 sec)SELECT isbn, title, CONCAT(name_first, ' ', name_last) AS author
FROM books
JOIN authors USING (author_id)
WHERE name_last LIKE 'Dostoevsk%'
ORDER BY title ASC
LIMIT 5;+------------+--------------------+-------------------+
| isbn | title | author |
+------------+--------------------+-------------------+
| 0805210644 | America | Fyodor Dostoevsky |
| 0553212168 | Brothers Karamozov | Fyodor Dostoevsky |
| 0553211757 | Crime & Punishment | Fyodor Dostoevsky |
| 0679420290 | Crime & Punishment | Fyodor Dostoevsky |
| 0192834118 | Idiot | Fyodor Dostoevsky |
+------------+--------------------+-------------------+
5 rows in set (0.001 sec)SELECT DISTINCT title
FROM books
JOIN authors USING (author_id)
WHERE name_last = 'Dostoevsky'
ORDER BY title;+------------------------+
| title |
+------------------------+
| America |
| Brothers Karamozov |
| Crime & Punishment |
| Idiot |
| Notes from Underground |
+------------------------++--------------+
| FOUND_ROWS() |
+--------------+
| 6 |
+--------------+
1 row in set (0.000 sec)