SQL Statements

This topic provides selected notes and discussion on statements and clauses that are built into the Manifold query engine.  The list in this topic is comprehensive but new items are often added with updates to Manifold and may appear in the product before they appear in this documentation.   See the list in the query builder tab of the Command Window for an authoritative list of statements, operators, commands and functions.

Constants

See the Identifiers, Constants and Literals topic for some useful constants.

Statements and Clauses

Notation

 

A / slash character indicates "or" - a choice of one of the options is required.   For example 'auto'/'none'/'aggressive' indicates a choice of 'auto' or 'none' or 'aggressive'.  

BLUE text indicates a sub-clause.

GREEN text indicates a sub-sub-clause.

 

 

ALTER <component-type> <component> (<action>, ...)

Given a component type, such as Table, Drawing, etc., and the name of the component, add or drop a property.

 

ALTER Drawing [Islands Drawing] (

  ADD PROPERTY 'StyleColorBack' '{ "Value": 6591981 }'

);

 

ADD PROPERTY <property> <value>

Add the named property with the given value.

DROP PROPERTY <property>

Remove the named property.  If the named property does not exist, that is not an error.

 

 

ALTER TABLE <table> (<action>, ...)

Modify a table using the specified action.

 

ALTER Table [Islands Table] (

  ADD PROPERTY 'FieldCoordSystem.Geom' 'EPSG:4269,mfd:{"Axes":"XY"}',

  ADD [Island Name] nvarchar

);

 

ADD <field> <type>

When altering the table, add a field of the specified type.

 

ALTER Table [Islands Table] (

  ADD [Island Name] nvarchar

);

 

ADD <field> <type> WITH [[ <context> ]] AS [[ <expression> ]]

When altering the table, add a computed field of the specified type and fill the field with the value of the expression that is computed within the specified context.

 

See the Computed Fields and Constraints topic for examples.

ADD CONSTRAINT <constraint> WITH [[ <context> ]] AS [[ <condition> ]]

When altering the table, add the specified constraint that is computed within the specified context.

 

See the Computed Fields and Constraints topic for examples.

ADD INDEX <index> <index-type> (<field> <options>, ...)

When altering the table, add the specified index.

 

ALTER TABLE t (

  ADD mfd_id INT64,

  ADD INDEX mfd_id_x BTREE (mfd_id)

);

 

See the discussion for the CREATE TABLE statement below, for various options.

ADD PROPERTY <property> <value>

When altering the table, add the named property with the given value.

DROP <field>

When altering the table, delete the named field from the schema.  If the named field does not exist, that is not an error.

DROP CONSTRAINT <constraint>

When altering the table, delete the named constraint from the schema.   If the named constraint does not exist, that is not an error.

DROP INDEX <index>

When altering the table, delete the named index from the schema.    If the named index does not exist, that is not an error.

DROP PROPERTY <property>

When altering the table, delete the named property from the table.    If the named property does not exist, that is not an error.

RENAME <field> <field-new>

When altering the table, rename the specified field.

RENAME CONSTRAINT <constraint> <constraint-new>

When altering the table, rename the specified constraint.

RENAME INDEX <index> <index-new>

When altering the table, rename the specified index.

 

 

CALL <table function>

Launch a function that returns a table.  Functions that return a table are called query functions.

 

? CALL CoordSystems()

 

Returns a table of coordinate systems maintained in Manifold's internal database.

CREATE <component-type> <component> (<item>, ...)

Create a component of the given type.   Some components, such as drawings or images, should be created with properties using the PROPERTY sub-clause.

 

CREATE COMMENTS [My Notes];

 

The CREATE statement puts the final inserted name of the created component into the createdname and creatednamequoted pragma values, for use by further statements that might want to create components linked to the created component, or otherwise use the name of the component. The createdname value contains the plain name and the creatednamequoted value contains the name in bracketed [...] form for use in properties.

 

PROPERTY <property> <value>

When creating a component, create it with the named property and given value.    For example, drawings are created with properties that cite the table they use and the field for their geometry.

 

CREATE DRAWING [Addresses Drawing] (

  PROPERTY 'Table' '[Addresses]',

  PROPERTY 'FieldGeom' 'Geom'

);

 

 

 

CREATE DATASOURCE <component> AS ROOT

Creates a new data source in the temporary database which links back to the original database of the query. This allows bringing in data for analysis. Attempting to create such a link on the regular, non-temporary database fails.

 

See the Temporary Databases  topic.

 

 

CREATE ROOT <root>

Creates a new temporary database with the specified name.  The name must be unique and cannot contain multiple parts (it is a name and not a path).

 

See the Temporary Databases  topic.

 

 

CREATE TABLE <table> (<item>, ...)

Create a table.    We can create a table that contains some items, such as fields, using the various sub-clauses.

<field> <type>

When creating a table, create a field of given name and data type.

 

CREATE TABLE [Provinces] ([Name] NVARCHAR);

 

<field> <type> WITH [[ <context> ]] AS [[ <expression> ]]

When creating a table, create a field of given name and data type as a computed field, using the expression that is computed within the specified context.

 

See the Computed Fields and Constraints topic for examples.

 

CREATE TABLE [Prices] (

  [mfd_id] INT64,

  [Cost] INT32,

  [Retail] INT32 AS [[ [Cost]*2 ]],

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

 

Creates a table where if we open the table and enter 55 as a value for the Cost field of the first record the Retail field will automatically be populated with the value 110.  Note that unlike the equivalent sub-clause for the ALTER TABLE statement, in this case we can only create a computed field and cannot add a field to be filled with a static value.

CONSTRAINT <constraint> WITH [[ <context> ]] AS [[ <condition> ]]

When creating a table, create a constraint of given name and condition  that is computed within the specified context.

 

See the Computed Fields and Constraints topic for examples.

 

CREATE TABLE [Prices] (

  [mfd_id] INT64,

  [Cost] INT32,

  [Retail] INT32,

  CONSTRAINT [Profitable] AS [[ (

    [Retail] > [COST]*2

  ) ]],

  INDEX [mfd_id_x] BTREE ([mfd_id])

);

 

Creates a table with a constraint called Profitable such that a value specified for the Retail field in a record must be more than twice as large as the value in the Cost field.

INDEX <index> BTREE (<field> <options>, ...)

When creating a table, create a  BTREE index on the specified fields optionally using options as cited below the BTREENULL entry.  

 

CREATE TABLE [Cities] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [Name] NVARCHAR,

  INDEX [Name_x] BTREE ([Name] COLLATE 'fr-BE' NOACCENT)

);

 

The above creates a table with a BTREE index on the Name field  which compares values using the French as spoken in Belgium collation without considering accents on characters.

INDEX <index> BTREEDUP (<field> <options>, ...)

When creating a table, create a  BTREEDUP index on the specified fields optionally using options as cited below the BTREENULL entry.

 

CREATE TABLE [Cities] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [Name] NVARCHAR,

  INDEX [Name_x] BTREEDUP ([Name] NOCASE)

);

 

The above creates a table with a BTREEDUP index on the Name field which compares values without considering case.   

INDEX <index> BTREEDUPNULL (<field> <options>, ...)

When creating a table, create a  BTREEDUPNULL index on the specified fields optionally using options as cited below the BTREENULL entry.   

 

An index (any of the BTREExxx index types) can be built using more than one field:

 

CREATE TABLE [Cities] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [Name] NVARCHAR,

  [State] NVARCHAR,  

  INDEX [Name_x] BTREEDUPNULL ([Name] NOSYMBOLS, [State] NOCASE)

);

 

The above creates a table with a BTREEDUPNULL index on the Name and the State fields.   Name values are compared without considering symbols while State values are compared without considering case.

INDEX <index> BTREENULL (<field> <options>, ...)

When creating a table, create a  BTREENULL index on the specified fields optionally using options as cited below.   

 

CREATE TABLE [Parks] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [Name] NVARCHAR,

  INDEX [Name_x] BTREENULL ([Name])

);

 

ASC/DESC

When creating a table with an index for this field, build the index in ascending or descending order.

COLLATE '<collate>'

See the COLLATE topic.  When creating a table with an index for this field, build the index using the specified collation.   Options for the collate string include:

 

  • <name> - A short string that specifies the collation language. Collations are identified by names such as en-US, en-GB and zh-CN.   Use neutral for the default collation language, or the name of a collation taken from a roster of over 150 supported collations, similar to those used by PostgreSQL.   Collations are identified by names such as en-US, en-GB and zh-CN.   An empty name is allowed and will specify the default collation.  The only option supported by neutral is nocase: all other options for neutral are ignored. The short form of '' for the entire default collation is accepted, but 'neutral, nocase' must spell the collation language as neutral and cannot omit it or use an empty string.
  • noaccent - Ignore diacritical marks, accents, and other similar characters, referred to as non-space characters in Unicode jargon.  The default is to use such characters when sorting.
  • nocase - Ignore case.  The default is case-sensitive sort order.
  • nokanatype - Ignore kana type for Asian languages.  The default is to utilize the kana type, so that two instances of the same character in, say, hiragana and katakana, are considered to be different when sorting.
  • nosymbols - Ignore symbols and punctuation.  The default is to utilize symbols and punctuation.
  • nowidth - Ignore character widths for Asian languages. The default is to utilize width, so that two instances of the same character which only vary in width are considered to be different when sorting.
  • wordsort - Ignore space between words, that is, sort only on the characters that make up words and not also the spaces between words. The default is that space between words matters when sorting, so that words are not treated distinctly but only the full content of the string, words and spaces both.

INDEX <index> RTREE (<field>)

When creating a table, create an RTREE index on the specified field.  Typically used to create a spatial index on a geom field in a table so a drawing can use that geom.

 

CREATE TABLE [Addresses] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [addr] NVARCHAR,

  [geom] GEOM,

  INDEX [geom_x] RTREE ([geom])

);

CREATE DRAWING [Addresses Drawing] (

  PROPERTY 'Table' '[Addresses]',

  PROPERTY 'FieldGeom' 'geom'

);

 

INDEX <index> RTREE (<fieldX>, <fieldY>, <fieldTile> TILESIZE (<cx>, <cy>) TILETYPE <type> ...)

When creating a table, create an RTREE index on the specified tile field, optionally applying the TILEREDUCE option in the <fieldTile> argument.

 

CREATE TABLE [Florida Slope Table] (

  [mfd_id] INT64,

  [X] INT32,

  [Y] INT32,

  [Tile] TILE,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  INDEX [X_Y_Tile_x] RTREE (

    [X], [Y], [Tile] TILEREDUCE 'AVERAGE'

    TILESIZE (128, 128) TILETYPE FLOAT32

  ),

  PROPERTY 'FieldTileSize.Tile' '[ 128, 128 ]',

  PROPERTY 'FieldTileType.Tile' 'float32'

);

 

TILEREDUCE 'AVERAGE/INDEXED'

An option for the <fieldTile> specification when creating an RTREE index for tiles. Specifies the tile reduce mode:

 

  • AVERAGE - Default.  Pixel values are averaged.

  • INDEXED - Pixel values are ranked by frequency and the most frequent value is used

 

PROPERTY <property> <value>

When creating a table, create a property of given name and value.

 

CREATE TABLE [Addresses] (

  [mfd_id] INT64,

  INDEX [mfd_id_x] BTREE ([mfd_id]),

  [addr] NVARCHAR,

  [geom] GEOM,

  INDEX [geom_x] RTREE ([geom]),

  PROPERTY 'FieldCoordSystem.geom'

     'EPSG:4269,mfd:{"Axes":"XY"}'

);

 

 

 

DELETE FROM <table>

Delete records from a table.

 

DELETE FROM [Mexico Table];

 

Deletes all records in the table.

WHERE <condition>

Apply the WHERE condition to the DELETE FROM statement.

 

DELETE FROM [Mexico Table]

  WHERE [Name] = 'Durango';

 

Deletes only the record for which the Name is Durango.

 

 

DROP <component-type> <component>

Delete the named component of specified component type, for example,

 

DROP TABLE [Mexico Table];

 

The statement does nothing and returns 0 in the result table if the named component does not exist.   If the named component exists, but is of a different type than that specified in the statement, such as a DROP TABLE that cites the name of a drawing, the statement fails with an error.

 

 

DROP ROOT <root>

Deletes the specified temporary database. If the temporary database being deleted was current at the time of deletion, the query engine switches the current data source to the original database of the query. Attempting to delete the original database of the query fails.

 

See the Temporary Databases  topic.

 

 

EXECUTE <query>

Execute with the given parameters, if any, the query saved within the named query component

 

Suppose we have a query component called p that contains the SQL text:

 

SELECT * FROM [mfd_root] WHERE [mfd_id] >= 1;

 

We could run it from a command window with:

 

EXECUTE [p];

 

If the query component p is stored within a data source called Examples we could run it from a command window with:

 

EXECUTE [Examples]::[p];

 

See the EXECUTE topic.

 

 

EXECUTE  [[ <query-text> ]]

Execute an inline query, the text for which is included within double square [[ ]] brackets in the EXECUTE statement.

 

EXECUTE [[ SELECT * FROM [mfd_root] WHERE [mfd_id] >= 1]];

 

Nesting of EXECUTE statements within [[ ... ]] inline queries is not allowed. If we have one or more EXECUTE statements each should be in its own query component.   See the EXECUTE topic.

ON <data-source>

Specify the data source on which to run an inline query, using the native query engine for that data source.  ON applies only to the inline text of the query and does not apply to any parameters or clauses outside of the inline text of the query within [[  ]] brackets.

 

EXECUTE [[ SELECT * FROM [mfd_root]; ]] ON [datasource];

 

The above works fine on data sources that are Manifold .map files, since it uses Manifold SQL.   If we were running the inline query on a non-Manifold data source, such as a PostgreSQL database, we should be careful to write SQL that is legal within PostgreSQL, such as (for a data source named postgresql):

 

SELECT * FROM

  (EXECUTE [[ SELECT risk, randnum FROM aa WHERE evac=0 ]] ON [postgresql])

WHERE randnum > 35000;

 

See the EXECUTE  topic for a discussion of ON clause nuances.

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) <query>

Pass parameters to a named query and execute that query.

 

Suppose query q contains:

 

SELECT * FROM @p WHERE mfd_id=@x;

 

The command:

 

EXECUTE WITH (@p TABLE = mfd_root, @x INT32 = 2) q;

 

...displays a line from mfd_root where the value of the field mfd_id is 2.

 

See the EXECUTE  topic.

 

 

EXECUTE WITH (@<parameter> <type> = <expression>, ...) [[ <query-text> ]]

Pass parameters to an inline query and execute that query.

 

SELECT SPLIT

  (EXECUTE WITH (@n NVARCHAR = Max(name))

    [[ SELECT * FROM mfd_root WHERE name = @n ]])

FROM mfd_meta;

 

See the EXECUTE  topic.

ON <data-source>

When passing parameters to an inline query, specify the data source on which to run an inline query, using the native query engine for that data source.  ON applies only to the inline text of the query and does not apply to any parameters or clauses outside of the inline text of the query within [[  ]] brackets.

 

Suppose we have linked in a project called Mexico.map to create a data source called Mexico, which contains a Mexico Table:

 

EXECUTE WITH (@n int32 = 2000000) [[

  SELECT [Name], [Population] FROM [Mexico Table]

    WHERE [Population] > @n;

  ]]

  ON [Mexico];

 

See the EXECUTE  topic.

 

 

FUNCTION <function>(@<parameter> <type>, ...) <type> AS <expression> END

Define a function.   See the Functions topic.

 

 

FUNCTION <function>(@<parameter> <type>, ...) <type> AS SCRIPT <script>

Define a script function.  See the Functions topic.

 

FUNCTION f(@g GEOM, @i INT32) FLOAT64X2

  AS SCRIPT [s2]

  ENTRY 'Script.GetGeomCoord';

 

 

 

FUNCTION <function>(@<parameter> <type>, ...) <type> AS SCRIPT FILE '<file>'

Define a script function stored in an external file.  See the Functions topic.

 

FUNCTION F(@x FLOAT64) FLOAT64

  AS SCRIPT FILE 'math2.dll'

  ENTRY 'math.Var.F';

 

 

 

FUNCTION <function>(@<parameter> <type>, ...) <type> AS SCRIPT INLINE <script>

Define a script function using an inline script.  See the Functions topic.

 

ALTER TABLE t (

  ADD insertdate DATETIME

    WITH

[[

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

]]

    AS [[ currentdate() ]]

);

 

 

ENTRY '<entry>'

Entry point into the script.

ENTRYSCHEMA '<entry>'

When script functions return table results, for example, in the case of custom splitters,  the Manifold query engine has to know in advance the structure of the returned table, that is, the schema. To accomplish this, the query engine uses a secondary script function which must return an empty table with the structure that will be used by the function that returns the actual data.  The name of that secondary script function is specified via the ENTRYSCHEMA keyword, by default set to <entry>Schema.

 

See the Functions topic.

 

 

INSERT INTO <table> <query>

INSERT INTO <table> <columns to fill> <query>

Insert values into a table.  There are two ways to use INSERT INTO.  In both cases the first argument is <table>, the name of the table into which rows will be inserted.   After that we can either specify the values for however many rows we will add, or we can specify the column names to be filled and then the corresponding values for each column.  

 

 

CREATE TABLE t (a INT32, b NVARCHAR);

INSERT INTO t VALUES (1, 'abc'), (2, 'ijk'), (3, 'xyz');

 

Creates a table:

 

 

 

CREATE TABLE t (a INT32, b NVARCHAR, c NVARCHAR);

INSERT INTO t (a, c) VALUES (1, 'abc'), (2, 'ijk'), (3, 'xyz');

 

Creates a table:

 

 

CREATE TABLE t2 (

  mfd_id INT64,

  INDEX mfd_id_x BTREE (mfd_id),

  a INT32);

INSERT INTO t2 (a)

  TABLE CALL ValueSequence(0, 1000, 3);

 

Creates a table:

 

 

See the INSERT INTO topic for more examples.

 

 

 

PRAGMA ('<pragma>' = '<value>', ...)

Set pragma values.  A pragma guides the operation of Manifold or can also be used for debugging.  The PRAGMA statement sets pragma values, which can later be read using the PragmaValue(<pragma>) function, which returns the value of the specified pragma.  See the PRAGMA topic.

'createdname': set by CREATE and SELECT INTO

The CREATE and SELECT INTO statements put the final inserted name of the created component into createdname and creatednamequoted pragma values. The createdname value contains the plain name.

'creatednamequoted': set by CREATE and SELECT INTO

The CREATE and SELECT INTO statements put the final inserted name of the created component into createdname and creatednamequoted pragma values. The creatednamequoted value contains the name in bracketed [...] form for use in properties.

'gpgpu' = 'auto'/'none'/'aggressive'

Controls GPGPU code generation.  'auto' is the default.

 

PRAGMA ('gpgpu' = 'aggressive');

 

'gpgpu.device' = '<index>'

Restrict GPGPU code to the specified GPGPU device, zero based counting. An empty index value removes any previously specified device restrictions.

 

PRAGMA ('gpgpu.device' = '0');

 

... restricts GPGPU code to only the first GPU device in the system.

 

PRAGMA ('gpgpu.device' = '');

 

... removes previously specified restrictions.

'gpgpu.fp' = '32'/'64'

Selects floating-point precision for GPGPU code.  The default is 64 bit operation.

 

PRAGMA ('gpgpu.fp' = '32');

 

... sets GPGPU operation to 32 bit floating point precision.

'progress' = '<status-text>'

Controls progress bar: Override default progress bar text by specifying text to use.

 

PRAGMA ('progress' = 'Almost done!');

 

'progress.percent' = '<percent>'

Controls progress bar:  Advances the position of the progress bar to the specified value as a percent of completion.

'progress.percentinsertsource' = '<percent-for-insert-source>'

Controls progress bar:  

'progress.percentnext' = '<percent-after-next-statement>'

Controls progress bar:  Specifies the position of the progress bar after the next statement as a percent of completion.

 

 

PRAGMA PUSH

Push all pragma values to the stack.  See the PRAGMA topic.

 

 

PRAGMA POP

Restores pragma values from the stack.  Attempting to PRAGMA POP without a corresponding PRAGMA PUSH fails. See the PRAGMA topic.

 

 

RENAME <component-type> <component> <component-new>

Change the name of the specified component type.

 

RENAME COMMENTS [My Notes] [To Do List];

 

RENAME DRAWING [States] [Provinces];

 

 

 

RENAME TABLE <table> <table-new>

Change the name of a table.

 

RENAME TABLE [Parks] [Parks Backup];

 

 

 

SCRIPT <script> ENGINE '<engine>' [[ <text> ]]

The SCRIPT statement allows queries to include an inline script.   Inline scripts provide a way to use script functions in a single self-contained query component.

 

Example: Add a computed field that uses an inline script to set insert date for each record:

 

ALTER TABLE t (

  ADD insertdate DATETIME

    WITH

[[

 

SCRIPT funcs ENGINE 'c#' [[

  class Script

  {

    static System.DateTime F() { return System.DateTime.Now; }

  }

]];

FUNCTION currentdate() DATETIME AS SCRIPT INLINE funcs ENTRY 'Script.F';

 

]]

    AS [[ currentdate() ]]

);

 

After running the above query, existing records in the table will get the current datetime.   If we wait a minute or so to allow the datetime to visibly change and then insert a new record,  either manually or using INSERT, it will get the new, current datetime value.

 

See the SCRIPT topic.

 

 

 

SELECT <field>/SPLIT <query>, ... FROM <table>

Creates a results table by selecting data from tables or queries.  

 

The SPLIT clause works within a SELECT statement to create rows that are permutations between the results of the SPLIT query and the results of the main SELECT query without the SPLIT.   See the SPLIT Clause topic.

DISTINCT

A qualifier that filters SELECT results to omit records that contain duplicate data in the selected fields, leaving only one of each instance.

 

SELECT DISTINCT [Job] FROM [Expenses];

 

If the Expenses table has records where the same type of job, such as Plumbing or Masonry is repeated, the above will result in only one record for each different type of job.

 

See the DISTINCT topic.

COLLECT <field>, ...

COLLECT is an aggregate that returns a table.  It is usually used with GROUP BY.  COLLECT gathers up the specified fields and their values from within each subgroup formed by GROUP BY, so those fields can be used in an arbitrary SQL expression or together with some other aggregate function.  See the COLLECT topic.

DISTINCT

A qualifier that filters COLLECT results to omit records that contain duplicate data in the collected fields, leaving only one of each instance.

 

See the DISTINCT topic.

WHERE <condition>

The WHERE clause filters records returned by the COLLECT aggregate based on how the <condition> expression evaluates for each record.  If the condition in the WHERE clause returns True, the record is included in the collected set.   if it returns False or NULL the record is excluded from the collected set.

ORDER BY <field> <options>, ...

Specifies ordering of the collected set.  See the ORDER BY topic and the COLLECT topic.

OFFSET <value>

For n = <value> filter the result to provide records in the collected set starting with the n+1 record.

FETCH <value>

For n = <value> filter the result to provide only the first n records in the collected set.   See the FETCH topic.

SPLIT <table> / CALL <table-function> / (<query-expression>)

 

Create rows that are permutations between the results of the SPLIT query and the results of the main SELECT query without the SPLIT.

 

SELECT mfd_id,

SPLIT (VALUES ('a', mfd_id), ('b', mfd_id+1)), name

FROM mfd_root;

 

See the SPLIT Clause topic.

INTO <table-new>

Create a new table and insert into it the results from the SELECT.

 

SELECT [Job], [Payment] INTO [Petty Cash]

  FROM [Expenses] WHERE [Payment] < 50;

 

 

The SELECT INTO statement  puts the final inserted name of the created table into the createdname and creatednamequoted pragma values, for use by further statements that might want to create components linked to the created table, or otherwise use the name of the table. The createdname value contains the plain name and the creatednamequoted value contains the name in bracketed [...] form for use in properties.

 

PROPERTY <property> <value>

When creating a table with SELECT INTO, create that table with a property of given name and value.

 

This allows using SELECT INTO with data sources like GDB or GPKG which require additional configuration for geometry fields at the time the table is created.   Typical properties specified on creation of tables in such data sources are the coordinate system, the geometry type, whether the geometry is 2D or 3D, and so on.   For example, when creating a table within an Esri file GDB if we want to store areas in that table we must specify the FieldGeomType.geom property to have a value of area:

 

--SQL9

SELECT [area], [code], [name], [geom]

INTO [gdb]::[states] (

  PROPERTY 'FieldGeomType.geom' 'area' -- the default would be 'point'

) FROM [states];

 

For databases that limit geometry types to a single type, values can be area, line, point, or pointmulti).  The any type is also allowed for other databases, but is the default with no need explicitly to specify any.

 

JOIN <table> ON <condition>

INNER JOIN <table> ON <condition>

 

LEFT JOIN <table> ON <condition>

RIGHT JOIN <table> ON <condition>

FULL JOIN <table> ON <condition>

 

LEFT OUTER JOIN <table> ON <condition>

RIGHT OUTER JOIN <table> ON <condition>

FULL OUTER JOIN <table> ON <condition>

 

 

 

JOIN statements combine two tables into a results table based on a given condition.   A JOIN follows the FROM in a SELECT statement.

 

FROM <table1> JOIN <table2> ON <condition>

 

An inner join all and a cross join are similar.   All four of the following forms produce the same results tables:

 

Inner Join:

 

FROM <table1> INNER JOIN <table2> ON <condition>

 

FROM <table1> JOIN <table2> ON <condition>

 

Cross Join:

 

FROM <table1>, <table2> WHERE <condition>

 

FROM <table1> CROSS JOIN <table2> WHERE <condition>

 

A cross join, also, called a comma join, or a cross join,  uses a WHERE clause for the join condition.  

 

All other joins use an ON clause to specify the join condition.

 

Outer Joins:

 

Outer joins use the keywords LEFT, RIGHT or FULL to specify the type of outer join desired:

 

FROM <table1> LEFT JOIN <table2> ON <condition>

 

FROM <table1> RIGHT JOIN <table2> ON <condition>

 

FROM <table1> FULL JOIN <table2> ON <condition>

 

If we like, we can optionally also use the OUTER keyword in the above outer joins.  The following three forms are exactly equivalent to the above three forms.  

 

FROM <table1> LEFT OUTER JOIN <table2> ON <condition>

 

FROM <table1> RIGHT OUTER JOIN <table2> ON <condition>

 

FROM <table1> FULL OUTER JOIN <table2> ON <condition>

 

Optional use of the OUTER keyword may make it easier to convert SQL from other database systems.  See the JOIN Statements topic.

FROM <table> CROSS JOIN <table> WHERE <condition>

FROM <table>, <table> WHERE <condition>

A cross join returns all possible combinations of rows for the FROM table and is normally used with a WHERE clause to reduce the number of rows to only those records for which the WHERE <condition> is true.  

 

Both of the following forms are allowed and are equivalent:

 

FROM <table1> CROSS JOIN <table2> WHERE <condition>

 

FROM <table1>, <table2> WHERE <condition>

 

The longer form of a cross join explicitly uses the CROSS JOIN  keywords.   An optional short form uses a comma , character instead of the key words and is often called a comma join, where the cross join is implicit.  

 

When the WHERE condition is the same as the ON condition for the equivalent inner join, a cross join produces the same results table as the inner join.  

 

If the WHERE clause is not used in a cross join, the results table for a comma join returns all possible combinations of rows for the FROM tables.    Important: the order in which the tables are specified chooses from which table common fields are returned.  See the JOIN Statements topic.

WHERE <condition>

The WHERE clause filters records returned by the SELECT query based on how the <condition> expression evaluates for each record.  If the condition in the WHERE clause returns True, the record is included in the results table.   if it returns False or NULL the record is excluded from the results table.

 

SELECT * FROM [Employees]

  WHERE [Name] = 'Jane';

 

In the above the SELECT returns all employees.   For each record WHERE clause evaluates the condition  [Name] = 'Jane'.  Only those records for which the condition is True go into the results table.

GROUP BY <field>, ...

GROUP BY works together with an aggregate function within SELECT.  GROUP BY creates subsets of records to feed into the aggregate function.   See the GROUP BY topic.

HAVING <condition>

A qualifier that works for GROUP BY similar to how the WHERE qualifier works for SELECT.     See the GROUP BY topic.

BATCH <value>

Controls parallel processing.  See the BATCH and THREADS topic.

THREADS <value>

Controls parallel processing.  See the BATCH and THREADS topic.

UNION/EXCEPT/INTERSECT <query>

Operators that can come after a SELECT query and form Boolean combinations between the results of the SELECT query and the results of their own query.  Tables returned by UNION, EXCEPT and INTERSECT by default will have duplicate records eliminated.  Use ALL to retain duplicates in the results table.

 

<SELECT query> UNION <query> - Return a table of all records found either in the <SELECT query> results or in the <query> results.

 

SELECT Name FROM Invoices

  UNION SELECT Name FROM Students;

 

Returns all names found either in the Invoices table or in the Students table.

 

 

<SELECT query> EXCEPT <query> - Return a table of all records in the <SELECT query> results but not in the <query> results.

 

SELECT Name FROM Invoices

  EXCEPT SELECT Name FROM Students;

 

Returns all names found in the Invoices table except those who are students.

 

 

<SELECT query> INTERSECT <query> - Return a table of all records found both in the <SELECT query> results and also in the <query> results.

 

SELECT Name FROM Invoices

  INTERSECT SELECT Name FROM Students;

 

Returns all names found in the Invoices table who also are students.

 

INTERSECT has priority over UNION or EXCEPT.   See the UNION / EXCEPT / INTERSECT topic.

ALL

When added to UNION, EXCEPT or INTERSECT, prevents duplicate records from being removed from the results table.

ORDER BY <field> <options>, ...

Specifies ordering of the results table.  See the ORDER BY topic.

OFFSET <value>

For n = <value> filter the result to provide records starting with the n+1 record.   If there are 14 invoices, the query:

 

SELECT * FROM [Invoices]

  OFFSET 4;

 

... will return a results table beginning with the fifth record.  

 

Tables are unordered so this query makes sense when tables are ordered with ORDER BY.

FETCH <value>

For n = <value> filter the result to provide only the first n records.

 

SELECT * FROM [Invoices]

  ORDER BY [Payment] DESC

  FETCH 3;

 

The results table will provide invoice records with the three largest payments.  

 

Tables are unordered so this query makes sense when tables are ordered with ORDER BY.   See the FETCH topic.

 

 

TABLE <table> / CALL <table-function> / (<query-expression>)

TABLE takes a a table or a  query expression and returns a table.

 

TABLE [mfd_root];

 

TABLE CALL executes a query function  that is, a CALL to a function that returns a table.

 

TABLE CALL CoordSystems();

 

TABLE CALL SystemGpgpus();

 

TABLE CALL ValueSequence(1, 10, 1);

 

Example:

 

FUNCTION f(@T TABLE) TABLE AS

  (SELECT Max([Population]) FROM @T) END;

TABLE CALL f([States Table]);

 

See the TABLE topic.

 

 

UPDATE <table> SET <field> = <expression>, ...

Edit an existing table by placing values into fields.  

 

See an interesting example of UPDATE in the Example: Unique Names using Regular Expressions topic.

WHERE <condition>

The WHERE clause filters which records are edited by the UPDATE statement based on how the <condition> expression evaluates for each record.  If the condition in the WHERE clause returns True, the record is edited.   if it returns False or NULL the record is excluded from UPDATE editing.

 

 

USE CHILD <child>

Switches current data source. Goes to a child data source. The path can contain multiple parts.

 

 

USE PARENT

Switches current data source. Goes to a parent data source. Attempting to go to a parent of the starting data source fails.

 

 

USE ROOT <root>

Switches current data source. Goes to the starting data source.

 

See the Temporary Databases  topic.

 

 

VALUE @<value> <type> = <expression>

VALUE @<value> <type>

The VALUE statement declares global values.  It exists in two forms: the full form used to declare global values and a short form that omits the expression.  

 

Examples

 

VALUE @deg2rad FLOAT64 = Pi / 180;

 

VALUE @angle FLOAT64 = 30;

 

VALUE @anglerad FLOAT64 = @angle * @deg2rad;

 

In the above, a type is required. The type can be TABLE.  A global variable can be redefined and assigned a different type and value. When this happens, previous references to the global variable will continue to use its former type and value,  similar to what happens when overriding functions.

 

Short form example

 

VALUE @name NVARCHAR;

 

The short form of VALUE takes an existing value and converts it to the specified type. If there is no value with the specified name defined, the short form defines it and sets it to a NULL value.  The short form is useful for handling query parameters.

 

See the VALUE topic.

 

 

VALUES (<value>, ...), ...

Returns a table populated with the specified values, using as field names for the table result, result 2, ...   Convenient for specifying literals when building tables and also as a general purpose means of executing expressions in the Command Window and within larger queries.

 

See the VALUES topic.

AS (<field>, ...)

Alias the names of columns to use the specified field names instead of the default result, result 2, ... names.

 

See the VALUES topic.

UNION/EXCEPT/INTERSECT <query>

Operators that can come after a VALUES query and form Boolean combinations between the results of the VALUES query and the results of their own query.  Tables returned by UNION, EXCEPT and INTERSECT by default will have duplicate records eliminated.  Use ALL to retain duplicates in the results table.

 

These operate exactly the same as when used with a SELECT query.

 

INTERSECT has priority over UNION or EXCEPT.   See the UNION / EXCEPT / INTERSECT topic.

ALL

When added to UNION, EXCEPT or INTERSECT, prevents duplicate records from being removed from the results table.

 

 

Notes

Square [ ] brackets - We do not need square [ ] brackets when there is no ambiguity about a name, such as when it does not contain space characters.    Examples in this documentation have been written by many people who at times prefer different styles and will often go back and forth between using or not using square brackets, as in the equivalent:

 

ADD INDEX [mfd_id_x] BTREE ([mfd_id])

 

and

 

ADD INDEX mfd_id_x BTREE (mfd_id)

 

 

Edit - Schema works with SELECT results too - Launching Edit - Schema works with the results table returned by SELECT, same as with a regular table.  This allows us to see the schema of the results table.

 

CPU Parallelization - Manifold automatically runs parallel for internal Manifold tasks and for Transform pane templates and similar operations, using all of the CPU cores available in your system in parallel.   When writing queries manually using the Command Window make sure to add a THREADS SystemCpuCount() command to the query to automatically parallelize the query to use all CPU cores in your system.

See Also

Tables

 

Functions

 

Queries

 

Regular Expressions

 

Command Window

 

Command Window - Query Builder

 

Identifiers, Constants and Literals

 

SQL Operators

 

SQL Functions

 

SQL Operators

 

Temporary Databases

 

EXECUTE

 

JOIN Statements

 

Example: Unique Names using Regular Expressions - We have a table with a text field that contains a list of names, separated by commas.  Some of the names are repeated.   We would like to transform the list of names into a similar list, but without any repetitions of names.   This topic shows how using a regular expression.   It shows the power and speed of a concise regular expression, and the flexibility with which regular expressions can be used in SQL queries as well as in the Transform pane.