-LEGEND
Typographical conventions
To ensure clarity in the SQL syntax examples and pseudo-code, the typographical
conventions that follow are used throughout the local SQL help.
SQL statements
Each topic contains a pseudo-code prototype of an SQL statement or
part of a statement that demonstrates the language element discussed. These
prototype examples appear at the beginning of topics. Actual SQL statements
appear within topics that demonstrate actual use of the language element
discussed. Both the prototype and example statements appear in Courier
New font.
While the local SQL language itself is case-insensitive (language elements
and metadata object names), examples in this help file use the following
convention to differentiate between language and metadata objects. All
language elements appear in uppercase. Metadata names appear in lowercase.
Correlation names appear in mixed case.
Optional elements
Language elements that are available, but that do not have to be used
with an SQL statement for the statement to be valid appear in prototype
syntax examples in brakets ([ and ]). For example, in the line below, the
DISTINCT keyword is optional.
SELECT [DISTINCT] *Syntax choices
ORDER BY column_reference [ASC | DESC]Note Do not mistake the choice typographical symbol for the concatenation function, defined as two vertical bar characters together (||).
ANSI-standard SQL confines each table name to a single word comprised
of alphanumeric characters and the underscore symbol, "_". Local SQL, however,
is enhanced to support multi-word table names.
Local SQL supports full file and path specifications in table references.
Table references with path or filename extensions must be enclosed in single
or double quotation marks. For example:
SELECT *Local SQL also supports BDE aliases in table references. For example:
FROM 'parts.dbf'
SELECT *
FROM "c:\sample\parts.dbf"
SELECT *If you omit the file extension for a local table name, the table is assumed to be the table type specified in the BDE configuration. The default table type is specified either in the default driver setting or in the default driver type for the standard alias associated with the query.
FROM ":pdox:table1"
SELECT passid
FROM "password"
-COLUMN NAMES
ANSI-standard SQL confines each column name to a single word comprised
of alphanumeric characters and the underscore symbol, "_". Local SQL, however,
is enhanced to support multi-word column names.
Local SQL supports Paradox multi-word column names and column names
that duplicate SQL keywords as long as those column names are
Enclosed in single or double quotation marks
Prefaced with an SQL table name or table correlation name
For example, the following column name consists of two words:
SELECT E."Emp Id"In the next example, the column name is the same as the SQL keyword DATE:
FROM employee E
SELECT datelog."date"
FROM datelog
-DATE FORMATS
Local SQL expects date literals to be in a U.S. date format, MM/DD/YY or MM/DD/YYYY. International date formats are not supported. To prevent date literals from being mistaken by the SQL parser for arithmetic calculations, enclose them in quotation marks. This keeps 1/23/1998 from being mistaken for 1 divided by 23 divided by 1998.
SELECT *Leading zeros for the month and day fields are optional.
FROM orders
WHERE (saledate <= "1/23/1998")
SELECT *To query using years outside these bounds, specify the century in the date literal.
FROM orders
WHERE (saledate = "5/5/30") OR (saledate = "5/5/80")
SELECT *
FROM orders
WHERE (saledate = "5/5/1930") OR (saledate = "5/5/2080")
-TIME FORMATS
Local SQL expects time literals to be in the format hh:mm:ss AM/PM; where hh are the hours, mm the minutes, and ss the seconds. When inserting new data with a time value, the AM/PM designator is optional and is case-insensitive ("AM" is the same as "am"). The time literal must be enclosed in quotation marks.
INSERT INTO WorkOrderIndicate which half of the day (morning or after noon) a time literal falls under in one of two ways. If an AM or PM marker is specified, that determines the half of the day. If no AM/PM designator is specified, the hour field is compared to 12. If the hour is less than twelve, the time is in the AM; if greater than 12, after noon. The hour field overrides an AM/PM designator. For example, the time literal "15:03:22 AM" is translated as "3:03:22 PM".
(ID, StartTime)
VALUES ("B00120","10:30:00 PM")
-BOOLEAN LITERALS
The boolean literal values TRUE and FALSE may be represented with or without quotation marks.
SELECT *
FROM transfers
WHERE (paid = TRUE) AND NOT (incomplete = "FALSE")
-TABLE CORRELATION NAMES
Table correlation names are used to explicitly associate a column with
the table from which it comes. This is especially useful when multiple
columns of the same name appear in the same query, typically in multi-table
queries. A table correlation name is defined by following the table reference
in the FROM clause of a SELECT query with a unique identifier. This identifier,
or table correlation name, can then be used to prefix a column name.
If the table name is not a quoted string, the table name is the default
implicit correlation name. An explicit correlation name the same as the
table name need not be specified in the FROM clause and the table name
can prefix column names in other parts of the statement.
SELECT *If the table name is a quoted string, you need to do one of the following:
FROM customer
LEFT OUTER JOIN orders
ON (customer.custno = orders.custno)
SELECT *Use the full table name as a correlation name in the FROM clause (and prefix all column references with the same correlation name).
FROM "customer.db"
LEFT OUTER JOIN "orders.db"
ON ("customer.db".custno = "orders.db".custno)
SELECT *Use a distinct token as a correlation name in the FROM clause (and prefix all column references with the same correlation name).
FROM "customer.db" CUSTOMER
LEFT OUTER JOIN "orders.db" ORDERS
ON (CUSTOMER.custno = ORDERS.custno)
SELECT *
FROM "customer.db" C
LEFT OUTER JOIN "orders.db" O
ON (C.custno = O.custno)
-COLUMN CORRELATION NAMES
Use the AS keyword to assign a correlation name to a column, aggregated value, or literal. Column correlation names cannot be enclosed in quotation marks and so cannot contain embedded spaces.
SELECT SUBSTRING(company FROM 1 FOR 1) AS sub, "Text" AS word
FROM customer
-RESERVED WORDS
Below is an alphabetical list of words reserved by local SQL. Avoid
using these reserved words for the names of metadata objects (tables, columns,
and indexes). An "Invalid use of keyword error" occurs when reserved words
are used as names for metadata objects. If a metadata object must have
a reserved word as it name, prevent the error by enclosing the name in
quotation marks and prefixing the reference with the table name.
ACTIVE ADD ALL AFTER ALTER AND ANY AS ASC ASCENDING AT AUTO AUTOINC
AVG BASE_NAME BEFORE BEGIN BETWEEN BLOB BOOLEAN BOTH BY BYTES CACHE CAST
CHAR CHARACTER CHECK CHECK_POINT_LENGTH COLLATE COLUMN COMMIT COMMITTED
COMPUTED CONDITIONAL CONSTRAINT CONTAINING COUNT CREATE CSTRING CURRENT
CURSOR DATABASE DATE DAY DEBUG DEC DECIMAL DECLARE DEFAULT DELETE DESC
DESCENDING DISTINCT DO DOMAIN DOUBLE DROP ELSE END ENTRY_POINT ESCAPE EXCEPTION
EXECUTE EXISTS EXIT EXTERNAL EXTRACT FILE FILTER FLOAT FOR FOREIGN FROM
FULL FUNCTION GDSCODE GENERATOR GEN_ID GRANT GROUP GROUP_COMMIT_WAIT_TIME
HAVING HOUR IF IN INT INACTIVE INDEX INNER INPUT_TYPE INSERT INTEGER INTO
IS ISOLATION JOIN KEY LONG LENGTH LOGFILE LOWER LEADING LEFT LEVEL LIKE
LOG_BUFFER_SIZE MANUAL MAX MAXIMUM_SEGMENT MERGE MESSAGE MIN MINUTE MODULE_NAME
MONEY MONTH NAMES NATIONAL NATURAL NCHAR NO NOT NULL NUM_LOG_BUFFERS NUMERIC
OF ON ONLY OPTION OR ORDER OUTER OUTPUT_TYPE OVERFLOW PAGE_SIZE PAGE PAGES
PARAMETER PASSWORD PLAN POSITION POST_EVENT PRECISION PROCEDURE PROTECTED
PRIMARY PRIVILEGES RAW_PARTITIONS RDB$DB_KEY READ REAL RECORD_VERSION REFERENCES
RESERV RESERVING RETAIN RETURNING_VALUES RETURNS REVOKE RIGHT ROLLBACK
SECOND SEGMENT SELECT SET SHARED SHADOW SCHEMA SINGULAR SIZE SMALLINT SNAPSHOT
SOME SORT SQLCODE STABILITY STARTING STARTS STATISTICS SUB_TYPE SUBSTRING
SUM SUSPEND TABLE THEN TIME TIMESTAMP TIMEZONE_HOUR TIMEZONE_MINUTE TO
TRAILING TRANSACTION TRIGGER TRIM UNCOMMITTED UNION UNIQUE UPDATE UPPER
USER VALUE VALUES VARCHAR VARIABLE VARYING VIEW WAIT WHEN WHERE WHILE WITH
WORK WRITE YEAR
The following are operators used in local SQL. Avoid using these characters
in the names of metadata objects.
||, -, *, /, <>, <, >, ,(comma), =, <=, >=, ~=, !=, ^=,
(, )
-DML STATEMENT LIST
Local SQL supports the following data manipulation language (DML) statements:
Retrieves data from tables.
SELECT [DISTINCT] * | column_listDescription
FROM table_reference
[WHERE predicates]
[ORDER BY order_list]
[GROUP BY group_list]
[HAVING having_condition]
SELECT custno, companyUse DISTINCT to limit the retrieved data to only distinct rows. The distinctness of rows is based on the combination of the columns in the SELECT clause columns list.
FROM orders
SELECT *
FROM "customers.qbe"
Deletes one or more rows from a table.
DELETE FROM table_referenceDescription
[WHERE predicates]
DELETE FROM "employee.db"The optional WHERE clause restricts row deletions to a subset of rows in the table. If no WHERE clause is specified, all rows in the table are deleted.
DELETE FROM "employee.db"The table reference cannot be passed to the DELETE statement via a parameter.
WHERE (empno IN (SELECT empno FROM "old_employee.db"))
Adds one or more new rows of data in a table
INSERT INTO table_referenceDescription
[(columns_list)]
VALUES (update_atoms)
INSERT INTO "holdings.dbf"If an explicit columns list is stated, incoming update atoms (in the order they appear in the VALUES clause) are stored in the listed columns (in the order they appear in the columns list). NULL values are stored in any columns that are not in a columns list.
VALUES (4094095, "BORL", 5000, 10.500, "1/2/1998")
INSERT INTO "customer.db"To add rows to one table from another, omit the VALUES keyword and use a subquery as the source for the new rows.
(custno, company)
VALUES (9842, "Borland International, Inc.")
INSERT INTO "customer.db"Update atom values may be passed to the INSERT statement via parameters. You cannot use parameters for the table reference and columns list.
(custno, company)
SELECT custno, company
FROM "oldcustomer.db"
Modifies one or more existing rows in a table.
UPDATE table_referenceDescription
SET column_ref = update_atom [, column_ref = update_atom...]
[WHERE predicates]
UPDATE salesinfoUpdate atom values may be passed to the UPDATE statement via parameters. You cannot use parameters for the table reference and columns list.
SET taxrate = 0.0825
WHERE (state = "CA")
Local SQL supports the following SQL statement clauses:
Specifies the tables from which a SELECT statement retrieves data.
FROM table_reference [, table_reference...]Description
SELECT *See the section Relational Operators for more information on retrieving data from multiple tables in a single SELECT query.
FROM "customer.db"
Specifies filtering conditions for a SELECT or UPDATE
statement.
WHERE predicates
Description
Use a WHERE clause to limit the effect of a SELECT or
UPDATE statement to a subset of rows in the table. Use of a WHERE
clause is optional.
The value for a WHERE clause is one or more logical expressions,
or predicates, that evaluate to TRUE or FALSE for each row in the table.
Only those 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
STATE column contains a value of "CA".
SELECT company, stateMultiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria. For example, the SELECT statement below retrieves all rows where the STATE column contains a value of "CA" and those with a value of "HI".
FROM customer
WHERE state = "CA"
SELECT company, stateThe SELECT statement below retrieves all rows where the SHAPE column is "round" or "square". It also returns those rows where the COLOR column is "red", regardless of the value in the SHAPE column.
FROM customer
WHERE (state = "CA") OR (state = "HI")
SELECT shape, color, costSubqueries are supported in the WHERE clause. A subquery works like a search condition to restrict the number of rows returned by the outer, or "parent" query.
FROM objects
WHERE ((shape = "round") AND (shape = "square")) OR (color = "red")
Sorts the rows retrieved by a SELECT statement.
ORDER BY column_reference [, column_reference...] [ASC|DESC]Description
SELECT EXTRACT(YEAR FROM lastinvoicedate) AS YY, state, UPPER(company)See the section Relational Operators for more information on retrieving data from multiple tables in a single SELECT query.
FROM customer
ORDER BY YY DESC, state ASC, 3
Combines rows with column values in common into single rows.
GROUP BY column_reference [, column reference...]Description
SELECT company, SUM(sales) AS TOTALSALESA column may be referenced in a GROUP BY clause by a column correlation name, instead of actual column names. The statement below forms groups using the first column, COMPANY, represented by the column correlation name Co.
FROM sales1998
GROUP BY company
ORDER BY company
SELECT company AS Co, SUM(sales) AS TOTALSALESNote Derived values (calculated fields) cannot be used as the basis for a GROUP BY clause.
FROM sales1998
GROUP BY Co
ORDER BY 1
Specifies filtering conditions for a SELECT statement.
HAVING predicates
Description
Use a HAVING clause to limit the rows retrieved by a SELECT
statement to a subset of rows where aggregated column 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.
One or more columns are the subjects of aggregate functions.
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 sales for individual total sales exceed
$1,000.
SELECT company, SUM(sales) AS TOTALSALESMultiple predicates must be separated by one of the logical operators OR or AND. Each predicate can be negated with the NOT operator. Parentheses can be used to isolate logical comparisons and groups of comparisons to produce different row evaluation criteria.
FROM sales1998
GROUP BY company
HAVING (SUM(sales) >= 1000)
ORDER BY company
SELECT company, SUM(sales) AS TOTALSALESSubqueries are supported in the HAVING clause. A subquery works like a search condition to restrict the number of rows returned by the outer, or "parent" query.
FROM sales1998
WHERE (state = "CA")
GROUP BY company
HAVING (SUM(sales) >= 1000)
ORDER BY company
Local SQL supports the following data manipulation language functions:
String function Description
Concatenation Concatenates two string values.
Converts all characters to lowercase.
LOWER(column_reference)Description
SELECT LOWER(name)When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
FROM country
Converts all characters to uppercase.
UPPER(column_reference)Description
SELECT name, capital, continentWhen applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
FROM country
WHERE UPPER(name) LIKE UPPER("Pe%")
Extracts a substring from a string.
SUBSTRING(column_reference FROM start_index [FOR length])Description
SELECT SUBSTRING("ABCDE" FROM 2 FOR 3) AS SubIn the SELECT statement below only the second and subsequent characters of the NAME column are retrieved.
FROM country
SELECT SUBSTRING(name FROM 2)When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
FROM country
Removes the trailing or leading character, or both, from a string.
TRIM([LEADING|TRAILING|BOTH] [trimmed_char] FROM column_reference)Description
TRIM syntax ResultWhen applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
TRIM(LEADING "_" FROM "_ABC_") "ABC_"
TRIM(TRAILING "_" FROM "_ABC_") "_ABC"
TRIM(BOTH "_" FROM "_ABC_") "ABC"
TRIM(BOTH "A" FROM "ABC") "BC"
Returns the average of the values in a specified column or an expression.
AVG([ALL] column_reference | DISTINCT column_reference)Description
SELECT AVG(itemstotal)ALL returns the average for all rows. When DISTINCT is not specified, ALL is the implied default.
FROM orders
SELECT AVG(itemstotal), AVG(itemstotal * 0.0825) AS AverageTaxWhen used with a GROUP BY clause, AVG calculates one value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the average value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
FROM orders
SELECT C."company", AVG(O."itemstotal") AS Average,Applicability
MAX(O."itemstotal") AS Biggest,
MIN(O."itemstotal") AS Smallest
FROM "customer.db" C, "orders.db" O
WHERE (C."custno" = O."custno")
GROUP BY C."company"
ORDER BY C."company"
Returns the number of rows that satisfy a query’s search condition.
COUNT(* | [ALL] column_reference | DISTINCT column_reference)Description
SELECT COUNT(amount)ALL returns the count for all rows. When DISTINCT is not specified, ALL is the implied default.
FROM averaging
Returns the largest value in the specified column.
MAX([ALL] column_reference | DISTINCT column_reference)Description
SELECT MAX(itemstotal)ALL returns the largest value for all rows. When DISTINCT is not specified, ALL is the implied default.
FROM orders
SELECT MAX(itemstotal), MAX(itemstotal * 0.0825) AS HighestTaxWhen used with a GROUP BY clause, MAX returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the largest value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
FROM orders
SELECT C."company", AVG(O."itemstotal") AS Average,Applicability
MAX(O."itemstotal") AS Biggest,
MIN(O."itemstotal") AS Smallest
FROM "customer.db" C, "orders.db" O
WHERE (C."custno" = O."custno")
GROUP BY C."company"
ORDER BY C."company"
Returns the smallest value in the specified column.
MIN([ALL] column_reference | DISTINCT column_reference)Description
SELECT MIN(itemstotal)ALL returns the smallest value for all rows. When DISTINCT is not specified, ALL is the implied default.
FROM orders
SELECT MIN(itemstotal), MIN(itemstotal * 0.0825) AS LowestTaxWhen used with a GROUP BY clause, MIN returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the smallest value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
FROM orders
SELECT C."company", AVG(O."itemstotal") AS Average,Applicability
MAX(O."itemstotal") AS Biggest,
MIN(O."itemstotal") AS Smallest
FROM "customer.db" C, "orders.db" O
WHERE (C."custno" = O."custno")
GROUP BY C."company"
ORDER BY C."company"
Calculates the sum of values for a column.
SUM([ALL] column_reference | DISTINCT column_reference)Description
SELECT SUM(itemstotal)ALL returns the smallest value for all rows. When DISTINCT is not specified, ALL is the implied default.
FROM orders
SELECT SUM(itemstotal), SUM(itemstotal * 0.0825) AS TotalTaxWhen used with a GROUP BY clause, SUM returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The statement below aggregates the total value for the order totals column in the ORDERS table, producing a subtotal for each company in the COMPANY table.
FROM orders
SELECT C."company", SUM(O."itemstotal") AS SubTotalApplicability
FROM "customer.db" C, "orders.db" O
WHERE (C."custno" = O."custno")
GROUP BY C."company"
ORDER BY C."company"
Converts specified value to the specified data type.
CAST(column_reference AS data_type)Description
SELECT CAST(saledate AS DATE)Converting a column value with CAST allows use of other functions or predicates on an otherwise incompatible data type, such as using the SUBSTRING function on a DATE column.
FROM ORDERS
SELECT saledate,When applied to retrieved data of a SELECT statement, the effect is transient and does not affect stored data. When applied to the update atoms of an UPDATE statement, the effect is persistent and permanently converts the case of the stored values.
SUBSTRING(CAST(CAST(saledate AS DATE) AS CHAR(10)) FROM 1 FOR 1)
FROM orders
Returns one field from a date value.
EXTRACT (extract_field FROM column_reference)Description
SELECT saledate,The statement below uses a DOB column (containing birthdates) to filter to those rows where the date is in the month of May. The month field from the DOB column is retrieved using the EXTRACT function and compared to 5, May being the fifth month.
EXTRACT(YEAR FROM saledate) AS YY,
EXTRACT(MONTH FROM saledate) AS MM,
EXTRACT(DAY FROM saledate) AS DD
FROM orders
SELECT DOB, LastName, FirstNameApplicability
FROM People
WHERE (EXTRACT(MONTH FROM DOB) = 5)
Perform arithmetic operations.
numeric_value1 + numeric_value2
numeric_value1 - numeric_value2
numeric_value1 * numeric_value2
numeric_value1 / numeric_value2
Description
Use arithmetic operators to perform arithmetic calculations on data
in SELECT queries. Calculations can be performed wherever non-aggregated
data values are allowed, such as in a SELECT or WHERE clause.
In the statement below, a column value is multiplied by a numeric literal.
SELECT (itemstotal * 0.0825) AS TaxArithmetic calculations are performed in the normal order of precedence: multiplication, division, addition, and then subtraction. To cause a calculation to be performed out of the normal order of precedence, use parentheses around the operation to be performed first. In the statement below the addition is performed before the multiplication.
FROM orders
SELECT (n.numbers * (n.multiple + 1)) AS ResultApplicability
FROM numbertable n
Connect multiple predicates.
[NOT] predicate OR [NOT] predicateDescription
[NOT] predicate AND [NOT] predicate
SELECT *Logical operator comparisons are performed in the order of precedence: OR and then AND. To perform a comparison out of the normal order of precedence, use parentheses around the comparison to be performed first. The SELECT statement below retrieves all rows where the SHAPE column is "round" and the COLOR "blue". It also returns those rows where the COLOR column is "red", regardless of the value in the SHAPE column (such as "triangle"). It would not return rows where the SHAPE is "round" and the COLOR anything but "blue" or where the COLOR is "blue" and the SHAPE anything but "round".
FROM reservations
WHERE ((reservdate < "1/31/1998") OR (paid = TRUE))
SELECT shape, color, costWithout the parentheses, the default order of precedence is used and the logic changes. The statement below, a variation on the above statement, would return rows where the SHAPE is "square" and the COLOR is "blue". It would also return rows where the SHAPE is "square" and the COLOR is "red". But unlike the preceding statement, it would not return rows where the COLOR is "red" and the SHAPE "triangle".
FROM objects
WHERE ((shape = "round") AND (color = "blue")) OR (color = "red")
SELECT shape, color, costUse the NOT operator to negate the boolean result of a comparison. In the statement below, only those rows where the PAID column contains a FALSE value are retrieved.
FROM objects
WHERE shape = "round" AND color = "blue" OR color = "red"
SELECT *
FROM reservations
WHERE (NOT (paid = "TRUE"))
Local SQL supports the following predicates:
Predicate Description
Comparison Compares two values.
Compare two values.
value1 < value2 less than
value1 > value2 greater than
value1 = value2 equal to
value1 <> value2 not equal to
value1 >= value2 greater than or equal to
value1 <= value2 less than or equal to
Description
Use comparison predicates to compare two like values. Values compared
can be: column values, literals, or calculations. The result of the comparison
is a boolean value that is used in a WHERE clause to determine on a row-by-row
basis whether a row meets the filtering criteria.
SELECT *Comparisons must be between two values of the same or a compatible data type. If one value is of an incompatible data type, convert that value with the CAST function to a compatible data type.
FROM orders
WHERE (itemstotal >= 1000)
Determines whether a value falls inside a range.
value1 [NOT] BETWEEN value2 AND value3Description
SELECT saledateHint BETWEEN is useful when filtering to retrieve rows with contiguous values that fall within the specified range. For filtering to retrieve rows with noncontiguous values, use the IN predicate.
FROM orders
WHERE (saledate BETWEEN "1/1/1988" AND "12/31/1988")
Indicates whether values exist in a subquery.
EXISTS subqueryDescription
SELECT O.orderno, O.custnoThe subquery may be further filtered with other conditions. For example, the statement below returns the rows pertaining to all customers who have placed orders the totals for which exceed $1000.
FROM orders O
WHERE EXISTS
(SELECT C.custno
FROM customer C
WHERE (C.custno = O.custno))
SELECT C.company, C.custnoUse NOT to return the converse of an EXISTS comparison.
FROM customer C
WHERE EXISTS
(SELECT O.custno
FROM orders O
WHERE (O.custno = C.custno) AND (O.itemstotal > 1000))
Indicates whether a value exists in a set of values.
value [NOT] IN (value_set)Description
SELECT C.company, C.stateThe comparison set can also be the result set from a subquery. The subquery may return multiple rows, but must only return a single column for comparison.
FROM customer C
WHERE (C.state IN ("CA", "HI"))
SELECT C.company, C.stateUse NOT to return the converse of an IN comparison.
FROM customer C
WHERE (C.state IN
(SELECT R.state
FROM regions R
WHERE (R.region = "Pacific")))
Indicates the similarity of one value as compared to another.
value [NOT] LIKE [substitution_char] comparison_value [substitution_char] [ESCAPE escape_char]Description
SELECT *The wildcard substitution character ("%") may be used in the comparison to represent an unknown number of characters. LIKE returns a TRUE when the portion of the column value matches that portion of the comparison value not corresponding to the position of the wildcard character. The wildcard character can appear at the beginning, middle, or end of the comparison value (or multiple combinations of these positions). For example, the statement below retrieves rows where the column value begins with "A" and is followed by any number of any characters. Matching values could include "Action Club" and "Adventure Undersea", but not "Blue Sports".
FROM customer
WHERE (company LIKE "Adventure Undersea")
SELECT *The single-character substitution character ("_") may be used in the comparison to represent a single character. LIKE returns a TRUE when the portion of the column value matches that portion of the comparison value not corresponding to the position of the single-character substitution character. The single-character substitution character can appear at the beginning, middle, or end of the comparison value (or multiple combinations of these positions). Use one single-character substitution character for each character to be wild in the filter pattern For example, the statement below retrieves rows where the column value begins with "b" ends with "n", with one character of any value between. Matching values could include "bin" and "ban", but not "barn".
FROM customer
WHERE (company LIKE "A%")
SELECT wordsUse NOT to return the converse of a LIKE comparison.
FROM dictionary
WHERE (words LIKE "b_n")
SELECT *LIKE can be used only with CHAR or compatible data types. If one value is of an incompatible data type, convert that value with the CAST function to a compatible data type. The comparison performed by the LIKE predicate is case-sensitive.
FROM Sales
WHERE (PercentValue LIKE "%10$%%" ESCAPE "$")
Indicates whether a column contains a NULL value.
column_reference IS [NOT] NULLDescription
SELECT *Use NOT to return the converse of a IS NULL comparison.
FROM customer
WHERE (invoicedate IS NULL)
Compares a column value to a column value in multiple rows in a subquery.
column_reference comparison_predicate SOME | ANY | ALL (subquery)
Description
Use the quantified comparison predicates SOME, ANY, and
ALL to filter a table by comparing a column value with multiple
comparison values. The quantified comparison predicates are used with comparison
predicates to compare a column value to the multiple values in a column
of a subquery.
The ANY predicate evaluates TRUE when the accompanying comparison
predicate evaluates TRUE for any value from the subquery. The SOME
predicate operates functionally the same as ANY. For example, using
the statement below, for any row to be retrieved from the HOLDINGS table,
the value in the PUR_PRICE column need only be greater than any one value
returned in the subquery’s PRICE column.
SELECT *The ALL predicate evaluates TRUE when the accompanying comparison predicate evaluates TRUE for all values from the subquery. For example, using the statement below, for any row to be retrieved from the HOLDINGS table, the value in the PUR_PRICE column needs to be greater than every value returned in the subquery’s PRICE column.
FROM "holdings.dbf" H
WHERE (H."pur_price" > ANY
(SELECT O."price"
FROM "old_sales.dbf"))
SELECT *Note The subquery providing the comparison values for the quantified comparison predicates may retrieve multiple rows, but can only have one column.
FROM "holdings.dbf" H
WHERE (H."pur_price" > ALL
(SELECT O."price"
FROM "old_sales.dbf"))
Local SQL supports the following join types:
Join operator Description
Joins two tables based on column values common between the two, excluding non-matches.
SELECT column_listDescription
FROM table_reference, table_reference [, table_reference...]
WHERE predicate [AND predicate...]
SELECT *More that one table may be joined with an equi-join. One column comparison predicate in the WHERE clause is required for each column compared to join each two tables. The statement below joins the CUSTOMER table to ORDERS, and then ORDERS to ITEMS. In this case, the joining table ORDERS acts as a source table for the joining table ITEMS.
FROM customer C, orders O
WHERE (C.custno = O.custno)
SELECT *Tables may also be joined using a concatenation of multiple column values to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are concatenated and compared with the values in the single column ID in SOURCE.
FROM customer C, orders O, items I
WHERE (C.custno = O.custno) AND
(O.orderno = I.orderno)
SELECT *An ORDER BY clause in equi-join statements can use columns from any table specified in the FROM clause to sort the result set.
FROM source S, joining J
WHERE (S.ID = J.ID1 || J.ID2)
Joins two tables based on column values common between the two, excluding non-matches.
SELECT column_listDescription
FROM table_reference
[INNER] JOIN table_reference
ON predicate
[[INNER] JOIN table_reference
ON predicate...]
SELECT *More than one table may be joined with an INNER JOIN. One use of the INNER JOIN operator and corresponding ON clause is required for each each set of two tables joined. One columns comparison predicate in an ON clause is required for each column compared to join each two tables. The statement below joins the CUSTOMER table to ORDERS, and then ORDERS to ITEMS. In this case, the joining table ORDERS acts as a source table for the joining table ITEMS. (The statement below appears without the optional INNER keyword.)
FROM customer C
INNER JOIN orders O
ON (C.custno = O.custno)
SELECT *Tables may also be joined using a concatenation of multiple column values to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are concatenated and compared with the values in the single column ID in SOURCE.
FROM customer C
JOIN orders O
ON (C.custno = O.custno)
JOIN items I
ON (O.orderno = I.orderno)
SELECT *An ORDER BY clause in INNER JOIN statements can use columns from any table specified in the FROM clause to sort the result set.
FROM source S
INNER JOIN joining J
ON (S.ID = J.ID1 || J.ID2)
Joins two tables based on column values common between the two, including non-matches.
SELECT column_listDescription
FROM table_reference
LEFT | RIGHT | FULL [OUTER] JOIN table_reference
ON predicate
[LEFT | RIGHT | FULL [OUTER] JOIN table_reference
ON predicate...]
SELECT *The LEFT modifier causes all rows from the table on the left of the OUTER JOIN operator to be included in the result set, with or without matches in the table to the right. If there is no matching row from the table on the right, its columns contain NULL values. The RIGHT modifier causes all rows from the table on the right of the OUTER JOIN operator to be included in the result set, with or without matches. If there is no matching row from the table on the left, its columns contain NULL values. The FULL modifier causes all rows from the all tables specified in the FROM clause to be included in the result set, with or without matches. If there is no matching row from one of the tables, its columns contain NULL values.
FROM customer C
LEFT OUTER JOIN orders O
ON (C.custno = O.custno)
SELECT *Tables may also be joined using expressions to produce a single value for the join comparison predicate. Here, the ID1 and ID2 columns in JOINING are separately compared with two values produced by the SUBSTRING function using the single column ID in SOURCE.
FROM customer C
FULL OUTER JOIN orders O
ON (C.custno = O.custno)
FULL OUTER JOIN items I
ON (O.orderno = I.orderno)
SELECT *An ORDER BY clause in OUTER JOIN statements can use columns from any table specified in the FROM clause to sort the result set.
FROM source S
RIGHT OUTER JOIN joining J
ON (SUBSTRING(S.ID FROM 1 FOR 2) = J.ID1) AND
(SUBSTRING(S.ID FROM 3 FOR 1) = J.ID2)
Joins two tables in a non-relational manner.
SELECT *Description
FROM table_reference, table_reference [,table_reference...]
SELECT *
FROM "employee.dbf", "items.db"
Concatenates the rows of one table to the end of another table.
SELECT col_1 [, col_2, ... col_n]Description
FROM table_reference
UNION [ALL]
SELECT col_1 [, col_2, ... col_n]
FROM table_reference
SELECT custno, companyMatching names and data types is not mandatory for result set columns retrieved by the UNION across the multiple tables. If there is a data type difference between two tables for a given column, the data type of the column in the first SELECT statement in the UNION join is used. Data is translated as best the BDE can for that column from the other tables. For example, if the second column of the first table is SMALLINT and CHAR(10) in the second table, the result set uses SMALLINT. Any data from the second table that cannot be converted from CHAR(10) to SMALLINT becomes zeros. Column name differences are also automatically handled. If the first column of two tables has a different name, the first column in the UNION result set will use that from the first SELECT statement.
FROM customers
UNION
SELECT custno, company
FROM old_customers
SELECT S.id, name
FROM source S
UNION ALL
SELECT J.id, CAST("" AS CHAR(10))
FROM joiner J
Joins two tables from different databases.
SELECT column_listDescription
FROM ":database_reference:table_reference", ":database_reference:table_reference" [,":database_reference:table_reference"...]
WHERE predicate [AND predicate...]
SELECT *
FROM ":DBDEMOS:customer.db" C, ":BCDEMOS:orders.db" O
WHERE (C.custno = O.custno)