MariaDB: SELECT

-- SELECT FORMAT

SELECT
    [ALL | DISTINCT | DISTINCTROW]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]

    select_expr [, select_expr ...]

    [ FROM table_references
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
      [HAVING where_condition]
      [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
      [LIMIT {[offset,] row_count | row_count OFFSET offset}]
      [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] [export_options]


INTO DUMPFILE 'file_name' INTO var_name [, var_name] ]

      [[FOR UPDATE | LOCK IN SHARE MODE] [WAIT n | NOWAIT] ] ]


-- export_options:

    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]


-- SCALAR SELECT SAMPLE

SELECT * FROM sq1 WHERE num = (SELECT MAX(num)/10 FROM sq2);

-- ROW SELECT SAMPLE

SELECT name,age FROM staff WHERE (name,age) IN (SELECT name,age FROM customer);

-- ALL SELECT (NOT IN is an alias for <> ALL)
# ALL comparison: =, >, <, >=, <=, <> or !=

SELECT * FROM t1 WHERE num > ALL (SELECT * FROM t2);

-- ANY SELECT (SOME is a synmonym for ANY, and IN is a synonym for = ANY)
# ANY comparison: =, >, <, >=, <=, <> or !=.

SELECT * FROM sq1 WHERE num > ANY (SELECT * FROM sq2);
SELECT * FROM sq1 WHERE num < SOME (SELECT * FROM sq2);
SELECT * FROM sq1 WHERE num IN (SELECT * FROM sq2);

-- EXISTS SELECT

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2)

--- SUBQUERY SELECT

SELECT AVG(sq_sum) FROM (SELECT SUM(score) AS sq_sum FROM student GROUP BY name) AS t;