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

Some useful constants:

 

CR -  a string constant for a carriage return character.

LF - a string constant for a line feed character.

WHITESPACE - a string constant for use with StringTrimXxx functions:  contains a space character, a tab character, CR and LF.

 

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.

 

 

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> AS <expression>

When altering the table, add a field of the specified type and fill the field with the value of the expression.

 

We have a table of provinces in Mexico to which we want to add a Country field that is populated with Mexico (since each province is in the country of Mexico):

 

ALTER TABLE [Mexico Table] (

  ADD [Country] nvarchar AS 'Mexico'

);

 

The above adds a field with a static value.

 

ALTER TABLE [Prices] (

  ADD [Govt] INT32 AS [Cost]*4

);

 

The above adds a computed field.  Adding records with values in the Cost field will automatically put a value four times that in the Govt field.

 

ADD CONSTRAINT <constraint> AS <condition>

When altering the table, add the specified constraint.

 

We have a table listing components that will be subject to some action we plan.  We want to add a constraint to that table that requires all of the component names to be names found in the [mfd_root] table, that is names of components in the project:

 

ALTER TABLE [My Components] (

  ADD CONSTRAINT Ncomp AS (

    [Component Name] IN (SELECT [Name] FROM [mfd_root])

  )

);

 

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.

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.

DROP CONSTRAINT <constraint>

When altering the table, delete the named constraint from the schema.

DROP INDEX <index>

When altering the table, delete the named index from the schema.

DROP PROPERTY <property>

When altering the table, delete the named property from the table.

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.

 

 

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];

 

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 must 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> AS <expression>

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

 

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> AS <condition>

When creating a table, create a constraint of given name and condition.

 

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>'

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

CASE/NOCASE

When creating a table with an index for this field, build the index comparing values considering case or not.

ACCENT/NOACCENT

When creating a table with an index for this field, build the index comparing values considering accents on letters or not.

SYMBOLS/NOSYMBOLS

When creating a table with an index for this field, build the index comparing values considering symbols or not.

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.

 

 

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.

 

 

DROP TABLE <table>

Delete the named table.

 

 

EXECUTE <query>

Execute 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> ]]

Execute an inline query, that is, execute a 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 CALL <table-function>

Execute a query function  that is, a CALL to a function that returns a table.

 

EXECUTE CALL SystemGpgpus()

 

Example:

 

FUNCTION f(T TABLE) TABLE AS

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

EXECUTE CALL f([States Table]);

 

See the EXECUTE  topic.

 

 

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> ]]

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.

 

EXECUTE WITH (n int32 = 2000000) [[

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

    WHERE [Population] > n;

  ]]

  ON [Mexico];

 

See the EXECUTE  topic.

 

 

EXECUTE WITH (<parameter> <type> = <expression>, ...) CALL <table-function>

When executing a query function, pass parameters to that function.

 

FUNCTION f(T TABLE) TABLE AS

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

EXECUTE WITH (n TABLE = [States Table]) CALL f(n);

 

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';

 

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 values into a table.

 

CREATE TABLE t (a INT32, b NVARCHAR);

INSERT INTO t (a, b) VALUES (2, 'abc'), (3, 'xyz');

 

Creates a table:

il_insert_into_01.png

 

CREATE TABLE t

  (mfd_id INT64,

    INDEX mfd_id_x BTREE (mfd_id), a INT32);

INSERT INTO t (a)

  EXECUTE CALL ValueSequence(0, 1000, 3);

 

Creates a table:

 

il_insert_into_02.png

 

 

 

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.

'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.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];

 

 

 

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 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.

 

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.

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;

 

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.

 

 

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

Edit an existing table by placing values into fields.

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.

 

 

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 transforms 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

 

SQL Functions

 

SQL Operators

 

Temporary Databases

 

EXECUTE

 

JOIN Statements