Clauses

  • FROM

    Use FROM to specify the tables from which a SELECT statement retrieves data.

    FROM <table to select from> [, <table to select from> ...]

    The FROM keyword is followed by a comma-separated list of table names. For example, the SELECT statement below retrieves data from all the fields in a table using the * wildcard character.

    SELECT * FROM G_lith

  • WHERE

    You can use a WHERE clause to limit the effect of a SELECT or UPDATE statement to a subset of rows in the table.

    The WHERE keyword is followed by one or more logical expressions or predicates that evaluate to TRUE or FALSE for each row in the table.

    Only the rows where the predicates evaluate to TRUE are retrieved by a SELECT statement or modified by an UPDATE statement.

    For example, the SELECT statement below retrieves all rows where the ZONE ID column contains a value of "T4".

    SELECT company_name, ten_id

    FROM tenements

    WHERE zone_id = "T4"

    Multiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator.

    SELECT company_name, ten_id

    FROM tenements

    WHERE (zone_id = "T4") OR (zone_id = "T5")

    You can use subqueries in a WHERE clause to restrict the number of rows returned by the main or outer query:

    SELECT T.company_name, T.ten_id

    FROM tenements T

    WHERE (T.company_name IN

      (SELECT E.company_name

      FROM exploration E

      WHERE (E.region = "Goldfields")))

    how the table names above are prefixed with an alias. The alias is used to differentiate between fields of the same name in the two tables being accessed. This avoids having to prefix each field with the full table name.

  • ORDER BY

    Use ORDER BY to sort the rows retrieved by a SELECT statement based on the values of one or more fields.

    ORDER BY <field_name> [, <field_name> ...] [ASC|DESC]

    The ORDER BY keyword is a comma-separated list of field names. The field names in the list must also be in the SELECT clause of the query statement.

    Use ASC (ASCENDING) to force the sort to be in ascending order, or use DESC (DESCENDING) for a descending sort order. When not specified, an ASCENDING sort order is used by default.

    SELECT company_name, ten_id

    FROM tenements

    WHERE (zone_id = "T4") OR (zone_id = "T5")

    ORDER BY company_name ASC, ten_id DESC

  • GROUP BY

    Use GROUP BY to combine rows with the field values into a single row. The criteria for combining rows is based on the values in the fields specified in the GROUP BY clause.

    Use GROUP BY to aggregate records based upon the unique values of one or more fields. You can only use a GROUP BY clause when one or more fields have an aggregate function applied to them.

    GROUP BY <field_name> [, <field_name> ...]

    The GROUP BY keyword is followed by a comma-separated list of fields. Each field must meet the following criteria:

    • be present in one of the tables specified in the FROM clause
    • appear in the SELECT clause of the query.
    • must not have an aggregate function applied to it.

    Fields that are the subject of an aggregate function have their values across all rows in the group combined.

    Fields that are not the subject of an aggregate function retain their value and serve to distinctly identify the group.

    For example, in the SELECT statement below, the values in the AREA field are aggregated (totalled) based on distinct values in the COMPANY_NAME field. This produces a total area for each company.

    SELECT company_name, SUM(area) AS TOTALAREA

    FROM tenements

    GROUP BY company_name

    ORDER BY company_name

  • HAVING

    You can use a HAVING clause to limit the rows retrieved by a SELECT statement to a subset of rows where the aggregated field values meet the specified criteria.

    A HAVING clause can only be used in a SELECT statement when the statement also has a GROUP BY clause and one or more fields are the subject of an aggregate function.

    The value for a HAVING clause is one or more logical expressions, or predicates, that evaluate to true or false for each aggregate row retrieved from the table. Only those rows where the predicates evaluate to true are retrieved by a SELECT statement. For example, the SELECT statement below retrieves all rows where the total area for the tenements of an individual companies exceed 5000.

    SELECT company_name, SUM(area) AS TOTALAREA

    FROM tenements

    GROUP BY company_name

    HAVING (SUM(area) >= 5000)

    ORDER BY company_name

Operators