=============
Basic Queries
=============

.. rubric:: Table of contents

.. contents::
   :local:
   :depth: 2


Introduction
============

``SELECT`` statement in SQL is the most common query that retrieves data from OpenSearch index. In this doc, only simple ``SELECT`` statement with single index and query involved is covered. A ``SELECT`` statement includes ``SELECT``, ``FROM``, ``WHERE``, ``GROUP BY``, ``HAVING``, ``ORDER BY`` and ``LIMIT`` clause. Among these clauses, ``SELECT`` and ``FROM`` are the foundation to specify which fields to be fetched and which index they should be fetched from. All others are optional and used according to your needs. Please read on for their description, syntax and use cases in details.

Syntax
------

The syntax of ``SELECT`` statement is as follows::

  SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
  FROM index_name
  [WHERE predicates]
  [GROUP BY expression [, ...]
   [HAVING predicates]]
  [ORDER BY expression [IS [NOT] NULL] [ASC | DESC] [, ...]]
  [LIMIT [offset, ] size]

Although multiple query statements to execute in batch is not supported, ending with semicolon ``;`` is still allowed. For example, you can run ``SELECT * FROM accounts;`` without issue. This is useful to support queries generated by other tool, such as Microsoft Excel or BI tool.

Fundamentals
------------

Apart from predefined keyword of SQL language, the most basic element is literal and identifier. Literal is numeric, string, date or boolean constant. Identifier represents OpenSearch index or field name. With arithmetic operators and SQL functions applied, the basic literals and identifiers can be built into complex expression.

Rule ``expressionAtom``:

.. image:: /docs/user/img/rdd/expressionAtom.png

The expression in turn can be combined into predicate with logical operator. Typically, predicate is used in ``WHERE`` and ``HAVING`` clause to filter out data by conditions specified.

Rule ``expression``:

.. image:: /docs/user/img/rdd/expression.png

Rule ``predicate``:

.. image:: /docs/user/img/rdd/predicate.png

Execution Order
---------------

The actual order of execution is very different from its appearance::

  FROM index
   WHERE predicates
    GROUP BY expressions
     HAVING predicates
      SELECT expressions
       ORDER BY expressions
        LIMIT size