SELECT
This page is part of MariaDB's Documentation.
The parent of this page is: SQL Statements for MariaDB Xpand
Topics on this page:
Overview
Retrieves data from one or more tables.
USAGE
Common Syntax:
SELECT
[ALL | DISTINCT]
<select_expression> [, <select_expression>] ...
[FROM <table_references>]
[WHERE <where_condition>]
[GROUP BY {<col_name> | <expression> | <position>}, ...]
[HAVING <where_condition>]
[ORDER BY {<col_name> | <expression> | <position>}
[ASC | DESC], ... ]
[LIMIT {[<offset>,] <row_count> | <row_count> OFFSET <offset>}]
[FOR UPDATE]
DETAILS
MariaDB Xpand does not support the following in SELECT statements:
COLLATEEXCEPTASCorDESCqualifier forGROUP BYINTERSECTROLLUPsubqueries with
ALLorSOME
EXAMPLES
SELECT
To show all records for active customers from the customers table:
SELECT * FROM customers WHERE customer_status = 'active';
+-------------+---------------+-------------------+-----------------+
| customer_id | customer_name | customer_email | customer_status |
+-------------+---------------+-------------------+-----------------+
| 1 | East Corp | east@example.com | active |
| 3 | East NGO | east@example.org | active |
| 6 | West NGO | west@example.org | active |
| 7 | North Corp | north@example.com | active |
+-------------+---------------+-------------------+-----------------+
SELECT DISTINCT
To show only distinct values from the selected columns:
SELECT DISTINCT invoice_currency, payment_method FROM invoices;
+------------------+----------------+
| invoice_currency | payment_method |
+------------------+----------------+
| EUR | card |
| EUR | wire |
| JPY | card |
| USD | cash |
| BRL | card |
| CNY | card |
| INR | card |
| EUR | gift |
| CNY | wire |
| USD | wire |
| USD | gift |
+------------------+----------------+
LIMIT .. OFFSET
To limit the number of rows shown, use the LIMIT clause:
SELECT * FROM invoices LIMIT 5;
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| invoice_id | branch_id | customer_id | invoice_date | invoice_currency | invoice_total | payment_method | invoice_status |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| 7147787490797008897 | 1 | 1 | 2022-08-01 14:22:12.579346 | EUR | 1049.78 | card | paid |
| 7147787490831368193 | 1 | 2 | 2022-01-08 16:01:10.746294 | EUR | 12775.32 | wire | canceled |
| 7147787490831369217 | 1 | 3 | 2021-01-11 18:21:42.658392 | JPY | 2309.54 | card | paid |
| 7147787490831370241 | 1 | 4 | 2022-06-14 19:08:52.138280 | USD | 201.76 | cash | paid |
| 7147787490831371265 | 1 | 5 | 2022-02-18 01:33:28.498291 | BRL | 672.98 | card | paid |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
To implement pagination, combine the LIMIT clause with the OFFSET clause:
SELECT * FROM invoices LIMIT 5 OFFSET 5;
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| invoice_id | branch_id | customer_id | invoice_date | invoice_currency | invoice_total | payment_method | invoice_status |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
| 7147787490831372289 | 2 | 6 | 2022-01-12 17:31:51.969382 | CNY | 2349.04 | card | paid |
| 7147787490831373313 | 2 | 1 | 2021-01-26 09:42:01.958483 | INR | 2308.11 | card | paid |
| 7147787490831374337 | 2 | 2 | 2022-02-11 10:44:09.563728 | EUR | 100.00 | gift | paid |
| 7147787490831375361 | 2 | 3 | 2022-04-27 22:34:50.385849 | JPY | 1049.78 | card | unpaid |
| 7147787490831376385 | 2 | 4 | 2021-05-10 21:02:45.284839 | CNY | 356898.00 | wire | paid |
+---------------------+-----------+-------------+----------------------------+------------------+---------------+----------------+----------------+
Join Tables
MariaDB Xpand supports the following types of join clauses in SELECT statements:
JOININNER JOINSTRAIGHT_JOINNATURAL JOINCROSS JOINRIGHT [OUTER] JOINLEFT [OUTER] JOINRIGHT [OUTER] STRAIGHT_JOINLEFT [OUTER] STRAIGHT_JOINFULL [OUTER] JOIN
To show information on customers with invoices in Japanese yens, you can join tables explicitly:
SELECT customers.customer_name, invoices.invoice_id,
invoices.invoice_currency, invoices.invoice_total
FROM customers
INNER JOIN invoices
ON customers.customer_id = invoices.customer_id
WHERE invoices.invoice_currency = 'JPY';
+---------------+---------------------+------------------+---------------+
| customer_name | invoice_id | invoice_currency | invoice_total |
+---------------+---------------------+------------------+---------------+
| East NGO | 7145230024445460481 | JPY | 1049.78 |
| East NGO | 7145230024445454337 | JPY | 2309.54 |
+---------------+---------------------+------------------+---------------+
To implicitly join tables using the respective id columns:
SELECT customers.customer_name, invoices.invoice_id,
invoices.invoice_currency, invoices.invoice_total
FROM customers, invoices
WHERE customers.customer_id = invoices.customer_id
AND invoices.invoice_currency = 'JPY';
+---------------+---------------------+------------------+---------------+
| customer_name | invoice_id | invoice_currency | invoice_total |
+---------------+---------------------+------------------+---------------+
| East NGO | 7145230024445454337 | JPY | 2309.54 |
| East NGO | 7145230024445460481 | JPY | 1049.78 |
+---------------+---------------------+------------------+---------------+
Select Data Into a User-Defined Variable
To save the result of a SELECT statement into a variable:
SELECT MAX(invoice_total) INTO @max_eur_invoice_this_year
FROM invoices
WHERE invoice_currency = 'EUR'
AND YEAR(invoice_date) = YEAR(NOW());
Follow up with this statement to check the value assigned to the variable:
SELECT @max_eur_invoice_this_year;
+----------------------------+
| @max_eur_invoice_this_year |
+----------------------------+
| 12775.32 |
+----------------------------+
Select Data in a Particular Order
To show all invoices for a previous year in descending order by date:
SELECT invoice_id, DATE(invoice_date), invoice_currency, invoice_total FROM invoices
WHERE YEAR(invoice_date) = YEAR(NOW()) - 1
ORDER BY invoice_date DESC;
+---------------------+--------------------+------------------+---------------+
| invoice_id | DATE(invoice_date) | invoice_currency | invoice_total |
+---------------------+--------------------+------------------+---------------+
| 7145230024445461505 | 2021-05-10 | CNY | 356898.00 |
| 7145230024445458433 | 2021-01-26 | INR | 2308.11 |
| 7145230024445454337 | 2021-01-11 | JPY | 2309.54 |
+---------------------+--------------------+------------------+---------------+
Group Selected Data
To show average invoice totals by month for the first quarter of the current year:
SELECT MONTH(invoice_date), AVG(invoice_total) FROM invoices
WHERE YEAR(invoice_date) = YEAR(NOW())
AND MONTH(invoice_date) <= 3
GROUP BY MONTH(invoice_date)
HAVING AVG(invoice_total) > 1000;
+---------------------+--------------------+
| MONTH(invoice_date) | AVG(invoice_total) |
+---------------------+--------------------+
| 1 | 7562.180000 |
+---------------------+--------------------+
Select Data Using Functions
To show the ASCII code for the ; symbol:
SELECT ASCII(';');
+------------+
| ASCII(';') |
+------------+
| 59 |
+------------+
Subqueries
To show the inactive or suspended customers that previously paid more than 100 dollars or euros in total:
SELECT c1.customer_name AS 'CUSTOMER NAME',
c1.customer_status AS 'STATUS'
FROM customers AS c1
WHERE (c1.customer_status = 'inactive' OR c1.customer_status = 'suspended')
AND c1.customer_id IN (
SELECT c2.customer_id
FROM customers AS c2
INNER JOIN invoices AS i
ON c2.customer_id = i.customer_id
WHERE i.invoice_currency IN ('USD', 'EUR')
GROUP BY c2.customer_id
HAVING SUM(i.invoice_total) >= 100
)
ORDER BY c1.customer_name DESC;
+---------------+-----------+
| CUSTOMER NAME | STATUS |
+---------------+-----------+
| West Corp | suspended |
| East LLC | inactive |
+---------------+-----------+