SELECT

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:

  • COLLATE

  • EXCEPT

  • ASC or DESC qualifier for GROUP BY

  • INTERSECT

  • ROLLUP

  • subqueries with ALL or SOME

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:

  • JOIN

  • INNER JOIN

  • STRAIGHT_JOIN

  • NATURAL JOIN

  • CROSS JOIN

  • RIGHT [OUTER] JOIN

  • LEFT [OUTER] JOIN

  • RIGHT [OUTER] STRAIGHT_JOIN

  • LEFT [OUTER] STRAIGHT_JOIN

  • FULL [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  |
+---------------+-----------+

CHANGE HISTORY

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.

Release Series

History

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.