{
    "mode": "perldoc",
    "parameter": "SQL::Statement::Syntax",
    "section": "",
    "url": "https://www.chedong.com/phpMan.php/perldoc/SQL%3A%3AStatement%3A%3ASyntax/json",
    "generated": "2026-06-15T20:03:08Z",
    "synopsis": "See SQL::Statement for usage.",
    "sections": {
        "NAME": {
            "content": "SQL::Statement::Syntax - documentation of SQL::Statement's SQL Syntax\n",
            "subsections": []
        },
        "SYNOPSIS": {
            "content": "See SQL::Statement for usage.\n",
            "subsections": []
        },
        "DESCRIPTION": {
            "content": "The SQL::Statement module can be used either from a DBI driver like DBD::CSV or directly. The\nsyntax below applies to both situations. In the case of DBDs, each DBD can implement its own\nsub-dialect so be sure to check the DBD documentation also.\n\nSQL::Statement is meant primarily as a base class for DBD drivers and as such concentrates on a\nsmall but useful subset of SQL. It does *not* in any way pretend to be a complete SQL parser for\nall dialects of SQL. The module will continue to add new supported syntax, and users may also\nextend the syntax (see \"#Extending the SQL syntax\").\n",
            "subsections": []
        },
        "USAGE": {
            "content": "",
            "subsections": [
                {
                    "name": "Default Supported SQL syntax - Summary",
                    "content": "SQL Statements\n\nCALL <function>\nCREATE [TEMP] TABLE <table> <columndefclause>\nCREATE [TEMP] TABLE <table> AS <select statement>\nCREATE [TEMP] TABLE <table> AS IMPORT()\nCREATE FUNCTION <userdefinedfunction> [ NAME <perlsubroutine> ]\nCREATE KEYWORD  <userdefinedkeyword>  [ NAME <perlsubroutine> ]\nCREATE OPERATOR <userdefinedoperator> [ NAME <perlsubroutine> ]\nCREATE TYPE     <userdefinedtype>     [ NAME <perlsubroutine> ]\nDELETE FROM <table> [<whereclause>]\nDROP TABLE [IF EXISTS] <table>\nDROP FUNCTION <function>\nDROP KEYWORD  <keyword>\nDROP OPERATOR <operator>\nDROP TYPE     <type>\nINSERT [INTO] <table> [<columnlist>] VALUES <valuelist>\nLOAD <userdefinedfunctionsmodule>\nSELECT <function>\nSELECT <selectclause>\n<fromclause>\n[<whereclause>]\n[ ORDER BY ocol1 [ASC|DESC], ... ocolN [ASC|DESC]] ]\n[ GROUP BY gcol1 [, ... gcolN] ]\n[ LIMIT [start,] length ]\nUPDATE <table> SET <setclause> [<whereclause>]\n\nExplicit Join Qualifiers\n\nNATURAL, INNER, OUTER, LEFT, RIGHT, FULL\n\nBuilt-in Functions\n\n* Aggregate : MIN, MAX, AVG, SUM, COUNT\n* Date/Time : CURRENTDATE, CURDATE, CURRENTTIME, CURTIME, CURRENTTIMESTAMP, NOW,\nUNIXTIMESTAMP\n* String    : ASCII, CHAR, BITLENGTH, CHARACTERLENGTH, CHARLENGTH, COALESCE,\nNVL, IFNULL, CONV, CONCAT, DECODE, HEX, OCT, BIN, INSERT, LEFT, RIGHT,\nLOCATE, POSITION, LOWER, UPPER, LCASE, UCASE, LTRIM, RTRIM, OCTETLENGTH,\nREGEX, REPEAT, REPLACE, SOUNDEX, SPACE, SUBSTITUTE, SUBSTRING, SUBSTR,\nTRANSLATE, TRIM, UNHEX\n* Numeric   : ABS, CEILING, CEIL, FLOOR, ROUND, EXP, LOG, LN, LOG10, MOD, POWER,\nRAND, SIGN, SQRT, TRUNCATE, TRUNC\n* Trig      : ACOS, ACOSEC, ACOSECH, ACOSH, ACOT, ACOTAN, ACOTANH, ACOTH, ACSC,\nACSCH, ASEC, ASECH, ASIN, ASINH, ATAN, ATAN2, ATANH, COS, COSEC,\nCOSECH, COSH, COT, COTAN, COTANH, COTH, CSC, CSCH, DEG2DEG, DEG2GRAD,\nDEG2RAD, DEGREES, GRAD2DEG, GRAD2GRAD, GRAD2RAD, PI, RAD2DEG, RAD2GRAD,\nRAD2RAD, RADIANS, SEC, SECH, SIN, SINH, TAN, TANH\n* System    : DBNAME, USERNAME, USER\n\nSpecial Utility Functions\n\n* IMPORT  - imports a table from an external RDBMS or perl structure\n* RUN     - prepares and executes statements in a file of SQL statements\n\nOperators and Predicates\n\n= , <> , < , > , <= , >= , IS [NOT] (NULL|TRUE|FALSE) , LIKE , CLIKE , IN , BETWEEN\n\nIdentifiers and Aliases\n\n* regular identifiers are case insensitive (though see note on table names)\n* delimited identifiers (inside double quotes) are case sensitive\n* column and table aliases are supported\n\nConcatenation\n\n* use either ANSI SQL || or the CONCAT() function\n* e.g. these are the same:  {foo || bar} {CONCAT(foo,bar)}\n\nComments\n\n* comments must occur before or after statements, cannot be embedded\n* SQL-style single line -- and C-style multi-line /* */ comments are supported\n\nNULLs\n\n* currently NULLs and empty strings are identical in non-ANSI dialect.\n* use {col IS NULL} to find NULLs, not {col=''} (though both may work depending on dialect)\n\nSee below for further details.\n"
                },
                {
                    "name": "Syntax - Details",
                    "content": "CREATE TABLE\nCreates permanent and in-memory tables.\n\nCREATE [TEMP] TABLE <tablename> ( <columndefinitions> )\nCREATE [TEMP] TABLE <tablename> AS <select statement>\nCREATE [TEMP] TABLE <tablename> AS IMPORT()\n\nColumn definitions are standard SQL column names, types, and constraints, see \"Column\nDefinitions\".\n\n# create a permanent table\n#\n$dbh->do(\"CREATE TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))\");\n\nThe \"AS SELECT\" clause creates and populates the new table using the data and column structure\nspecified in the select statement.\n\n# create and populate a table from a query to two other tables\n#\n$dbh->do(\"CREATE TABLE qux AS SELECT id,word FROM foo NATURAL JOIN bar\");\n\nIf the optional keyword TEMP (or its synonym TEMPORARY) is used, the table will be an in-memory\ntable, available for the life of the current database handle or until a DROP TABLE command is\nissued.\n\n# create a temporary table\n#\n$dbh->do(\"CREATE TEMP TABLE qux (id INT PRIMARY KEY,word VARCHAR(30))\");\n\nTEMP tables can be modified with SQL commands but the updates are not automatically reflected\nback to any permanent tables they may be associated with. To save a TEMP table - just use an AS\nSELECT clause:\n\n$dbh = DBI->connect( 'dbi:CSV:' );\n$dbh->do(\"CREATE TEMP TABLE quxtemp AS (id INT, word VARCHAR(30))\");\n#\n# ... modify quxtemp with INSERT, UPDATE, DELETE statements, then save it\n#\n$dbh->do(\"CREATE TABLE quxpermanent AS SELECT * FROM quxtemp\");\n\nTables, both temporary and permanent may also be created directly from perl arrayrefs and from\nheterogeneous queries to any DBI accessible data source, see the IMPORT() function.\n\nCREATE [ {LOCAL|GLOBAL} TEMPORARY ] TABLE $table\n(\n$col1 $coltype1 $colconstraints1,\n...,\n$colN $coltypeN $colconstraintsN,\n)\n[ ON COMMIT {DELETE|PRESERVE} ROWS ]\n\n* coltype must be a valid data type as defined in the\n\"validdatatypes\" section of the dialect file for the\ncurrent dialect\n\n* colconstraints may be \"PRIMARY KEY\" or one or both of\n\"UNIQUE\" and/or \"NOT NULL\"\n\n* IMPORTANT NOTE: temporary tables, data types and column\nconstraints are checked for syntax violations but are\ncurrently otherwise *IGNORED* -- they are recognized by\nthe parser, but not by the execution engine\n\n* The following valid ANSI SQL92 options are not currently\nsupported: table constraints, named constraints, check\nconstraints, reference constraints, constraint\nattributes, collations, default clauses, domain names as\ndata types\n\nDROP TABLE\nDROP TABLE $table [ RESTRICT | CASCADE ]\n\n* IMPORTANT NOTE: drop behavior (cascade or restrict) is\nchecked for valid syntax but is otherwise *IGNORED* -- it\nis recognized by the parser, but not by the execution\nengine\n\nINSERT INTO\nINSERT INTO $table [ ( $col1, ..., $colN ) ] VALUES ( $val1, ... $valN )\n\n* default values are not currently supported\n* inserting from a subquery is not currently supported\n\nDELETE FROM\nDELETE FROM $table [ WHERE searchcondition ]\n\n* see \"searchcondition\" below\n\nUPDATE\nUPDATE $table SET $col1 = $val1, ... $colN = $valN [ WHERE searchcondition ]\n\n* default values are not currently supported\n* see \"searchcondition\" below\n\nSELECT\nSELECT selectclause\nFROM fromclause\n[ WHERE searchcondition ]\n[ ORDER BY $ocol1 [ASC|DESC], ... $ocolN [ASC|DESC] ]\n[ LIMIT [start,] length ]\n\n* select clause ::=\n[DISTINCT|ALL] *\n| [DISTINCT|ALL] col1 [,col2, ... colN]\n| setfunction1 [,setfunction2, ... setfunctionN]\n\n* set function ::=\nCOUNT ( [ALL] * )\n| COUNT | MIN | MAX | AVG | SUM ( [DISTINCT|ALL] colname )\n\n* from clause ::=\ntable1 [, table2, ... tableN]\n| table1 NATURAL [jointype] JOIN table2\n| table1 [jointype] table2 USING (col1,col2, ... colN)\n| table1 [jointype] JOIN table2 ON table1.colA = table2.colB\n\n* join type ::=\nINNER\n| [OUTER] LEFT | RIGHT | FULL\n\n* if jointype is not specified, INNER is the default\n* if DISTINCT or ALL is not specified, ALL is the default\n* if start position is omitted from LIMIT clause, position 0 is\nthe default\n* ON clauses may only contain equal comparisons and AND combiners\n* self-joins are not currently supported\n* if implicit joins are used, the WHERE clause must contain\nan equijoin condition for each table\n* multiple ANSI joins are not supported; use implicit joins for these\n* this also means that combinations of INNER and non-INNER joins are\nnot supported\n\nSEARCH CONDITION\n[NOT] $val1 $op1 $val1 [ ... AND|OR $valN $opN $valN ]\n\nOPERATORS\n$op  = |  <> |  < | > | <= | >=\n| IS [NOT] NULL | IS [NOT] TRUE | IS [NOT] FALSE\n| LIKE | CLIKE | BETWEEN | IN\n\nThe \"CLIKE\" operator works exactly the same as the \"LIKE\"\noperator, but is case insensitive.  For example:\n\nWHERE foo LIKE 'bar%'   # succeeds if foo is \"barbaz\"\n# fails if foo is \"BARBAZ\" or \"Barbaz\"\n\nWHERE foo CLIKE 'bar%'  # succeeds for \"barbaz\", \"Barbaz\", and \"BARBAZ\"\n\nBUILT-IN AND USER-DEFINED FUNCTIONS\nThere are many built-in functions and you can also create your own new functions from perl\nsubroutines. See SQL::Statement::Functions for documentation of functions.\n\nIdentifiers (table & column names)\nRegular identifiers (table and column names *without* quotes around them) are case INSENSITIVE\nso column foo, fOo, FOO all refer to the same column. Internally they are used in their lower\ncase representation, so do not rely on SQL::Statement retaining your case.\n\nDelimited identifiers (table and column names *with* quotes around them) are case SENSITIVE so\ncolumn \"foo\", \"fOo\", \"FOO\" each refer to different columns.\n\nA delimited identifier is *never* equal to a regular identifier (so \"foo\" and foo are two\ndifferent columns). But do not do that :-).\n\nRemember thought that, in DBD::CSV if table names are used directly as file names, the case\nsensitivity depends on the OS e.g. on Windows files named foo, FOO, and fOo are the same as each\nother while on Unix they are different.\n\nSpecial Utility SQL Functions\nIMPORT()\nImports the data and structure of a table from an external data source into a permanent or\ntemporary table.\n\n$dbh->do(\"CREATE TABLE qux AS IMPORT(?)\",{},$oraclesth);\n\n$dbh->do(\"CREATE TABLE qux AS IMPORT(?)\",{},$AoA);\n\n$dbh->do(\"CREATE TABLE qux AS IMPORT(?)\",{},$AoH);\n\nIMPORT() can also be used anywhere that tablenames can:\n\n$sth=$dbh->prepare(\"\nSELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2 WHERE T1.id ...\n\");\n$sth->execute( $pgsth, $mysqlsth );\n\nThe IMPORT() function imports the data and structure of a table from an external data source.\nThe IMPORT() function is always used with a placeholder parameter which may be 1) a prepared and\nexecuted statement handle for any DBI accessible data source; or 2) an AoA whose first row is\ncolumn names and whose succeeding rows are data 3) an AoH.\n\nThe IMPORT() function may be used in the AS clause of a CREATE statement, and in the FROM clause\nof any statement. When used in a FROM clause, it should be used with a column alias e.g. SELECT\n* FROM IMPORT(?) AS TableA WHERE ...\n\nYou can also write your own IMPORT() functions to treat anything as a data source. See\nUser-Defined Function in SQL::Statement::Functions.\n\nExamples:\n\n# create a CSV file from an Oracle query\n#\n$dbh = DBI->connect('dbi:CSV:');\n$oraclesth = $oracledbh->prepare($anyoraclequery);\n$oraclesth->execute(@params);\n$dbh->do(\"CREATE TABLE qux AS IMPORT(?)\",{},$oraclesth);\n\n# create an in-memory table from an AoA\n#\n$dbh      = DBI->connect( 'dbi:File:' );\n$arrayref = [['id','word'],[1,'foo'],[2,'bar'],];\n$dbh->do(\"CREATE TEMP TABLE qux AS IMPORT(?)\",{},$arrayref);\n\n# query a join of a PostgreSQL table and a MySQL table\n#\n$dbh        = DBI->connect( 'dbi:File:' );\n$pgdbh     = DBI->connect( ... DBD::pg connect params );\n$mysqldbh  = DBI->connect( ... DBD::mysql connect params );\n$pgsth     = $pgdbh->prepare( ... any pg query );\n$pgsth     = $pgdbh->prepare( ... any mysql query );\n#\n$sth=$dbh->prepare(\"\nSELECT * FROM IMPORT(?) AS T1 NATURAL JOIN IMPORT(?) AS T2\n\");\n$sth->execute( $pgsth, $mysqlsth );\n\nRUN()\nRun SQL statements from a user supplied file. Please Note: this function is experimental, please\nlet me know if you have problems.\n\nRUN( sqlfile )\n\nIf the file contains non-SELECT statements such as CREATE and INSERT, use the RUN() function\nwith $dbh->do(). For example, this prepares and executes all of the SQL statements in a file\ncalled \"populate.sql\":\n\n$dbh->do(\" CALL RUN( 'populate.sql') \");\n\nIf the file contains SELECT statements, the RUN() function may be used anywhere a table name may\nbe used, for example, if you have a file called \"query.sql\" containing \"SELECT * FROM Employee\",\nthen these two lines are exactly the same:\n\nmy $sth = $dbh->prepare(\" SELECT * FROM Employee \");\n\nmy $sth = $dbh->prepare(\" SELECT * FROM RUN( 'query.sql' ) \");\n\nIf the file contains a statement with placeholders, the values for the placeholders can be\npassed in the call to $sth->execute() as normal. If the query.sql file contains \"SELECT id,name\nFROM x WHERE id=?\", then these two are the same:\n\nmy $sth = $dbh->prepare(\" SELECT id,name FROM x WHERE id=?\");\n$sth->execute(64);\n\nmy $sth = $dbh->prepare(\" SELECT * FROM RUN( 'query.sql' ) \");\n$sth->execute(64);\n\nNote This function assumes that the SQL statements in the file are separated by a\nsemi-colon+newline combination (/;\\n/). If you wish to use different separators or import SQL\nfrom a different source, just override the RUN() function with your own user-defined-function.\n"
                },
                {
                    "name": "Further details",
                    "content": "Integers\nReals   Syntax obvious\n\nStrings Surrounded by either single quotes; some characters need to be escaped with a backslash,\nin particular the backslash itself (\\\\), the NULL byte (\\0), Line feeds (\\n), Carriage\nreturn (\\r), and the quotes (\\').\n\nNote: Quoting \"Strings\" using double quotes are recognized as quoted identifiers (column\nor table names).\n\nParameters\nParameters represent scalar values, like Integers, Reals and Strings do. However, their\nvalues are read inside Execute() and not inside Prepare(). Parameters are represented by\nquestion marks (?).\n\nIdentifiers\nIdentifiers are table or column names. Syntactically they consist of alphabetic\ncharacters, followed by an arbitrary number of alphanumeric characters. Identifiers like\nSELECT, INSERT, INTO, ORDER, BY, WHERE, ... are forbidden and reserved for other tokens.\nIdentifiers are always compared case-insensitively, i.e. \"select foo from bar\" will be\nevaluated the same as \"SELECT FOO FROM BAR\" (\"FOO\" will be evaluated as \"foo\", similar\nfor \"BAR\").\n\nSince SQL::Statement is internally using lower cased identifiers (unquoted), everytime a\nwildcard is used, the delivered names of the identifiers are lower cased.\n"
                }
            ]
        },
        "Extending SQL syntax using SQL": {
            "content": "The Supported SQL syntax shown above is the default for SQL::Statement but it can be extended\n(or contracted) either on-the-fly or on a permanent basis. In other words, you can modify the\nSQL syntax accepted as valid by the parser and accepted as executable by the executer. There are\ntwo methods for extending the syntax - 1) with SQL commands that can be issued directly in\nSQL::Statement or form a DBD or 2) by subclassing SQL::Parser.\n\nThe following SQL commands modify the default SQL syntax:\n\nCREATE/DROP FUNCTION\nCREATE/DROP KEYWORD\nCREATE/DROP TYPE\nCREATE/DROP OPERATOR\n\nA simple example would be a situation in which you have a table named 'TABLE'. Since table is an\nANSI reserved key word, by default SQL::Statement will produce an error when you attempt to\ncreate or access it. You could put the table name inside double quotes since quoted identifiers\ncan validly be reserved words, or you could rename the table. If neither of those are options,\nyou would do this:\n\nDROP KEYWORD table\n\nOnce that statement is issued, the parser will no longer object to 'table' as a table name.\nCareful though, if you drop too many keywords you may confuse the parser, especially keywords\nlike FROM and WHERE that are central to parsing the statement.\n\nIn the reverse situation, suppose you want to parse some SQL that defines a column as type\nBIGBLOB. Since 'BIGBLOB' is not a recognized ANSI data type, an error will be produced by\ndefault. To make the parser treat it as a valid data type, you do this:\n\nCREATE TYPE bigblob\n\nKeywords and types are case-insensitive.\n\nSuppose you are working with some SQL that contains the cosh() function (an Oracle function for\nhyperbolic cosine, whatever that is :-). The cosh() function is not currently implemented in\nSQL::Statement so the parser would die with an error. But you can easily trick the parser into\naccepting the function:\n\nCREATE FUNCTION cosh\n\nOnce the parser has read that CREATE FUNCTION statement, it will no longer object to the use of\nthe cosh() function in SQL statements.\n\nIf your only interest is in parsing SQL statements, then \"CREATE FUNCTION cosh\" is sufficient.\nBut if you actually want to be able to use the cosh() function in executable statements, you\nneed to supply a perl subroutine that performs the cosh() function:\n\nCREATE FUNCTION cosh AS perlsubroutinename\n\nThe subroutine name can refer to a subroutine in your current script, or to a subroutine in any\navailable package. See SQL::Statement::Functions for details of how to create and load\nfunctions.\n\nFunctions can be used as predicates in search clauses, for example:\n\nSELECT * FROM x WHERE c1=7 AND SOUNDEX(c3,'foo') AND c8='bar'\n\nIn the SQL above, the \"SOUNDEX()\" function full predicate - it plays the same role as \"c1=7 or\nc8='bar'\".\n\nFunctions can also serve as predicate operators. An operator, unlike a full predicate, has\nsomething on the left and right sides. An equal sign is an operator, so is LIKE. If you really\nwant to you can get the parser to not accept LIKE as an operator with\n\nDROP OPERATOR like\n\nOr, you can invent your own operator. Suppose you have an operator \"REVERSEOF\" that is true if\nthe string on its left side when reversed is equal to the string on the right side:\n\nCREATE OPERATOR reverseof\nSELECT * FROM x WHERE c1=7 AND c3 REVERSEOF 'foo'\n\nThe operator could just as well have been written as a function:\n\nCREATE FUNCTION reverseof\nSELECT * FROM x WHERE c1=7 AND REVERSEOF(c3,'foo')\n\nLike functions, if you want to actually execute a user-defined operator as distinct from just\nparsing it, you need to assign the operator to a perl subroutine. This is done exactly like\nassigning functions:\n\nCREATE OPERATOR reverseof AS perlsubroutinename\n",
            "subsections": []
        },
        "Extending SQL syntax using subclasses": {
            "content": "In addition to using the SQL shown above to modify the parser's behavior, you can also extend\nthe SQL syntax by subclassing SQL::Parser. See SQL::Parser for details.\n\nAUTHOR & COPYRIGHT\nCopyright (c) 2005, Jeff Zucker <jzuckerATcpan.org>, all rights reserved. Copyright (c)\n2009-2020, Jens Rehsack <rehsackATcpan.org>, all rights reserved.\n\nThis document may be freely modified and distributed under the same terms as Perl itself.\n",
            "subsections": []
        }
    },
    "summary": "SQL::Statement::Syntax - documentation of SQL::Statement's SQL Syntax",
    "flags": [],
    "examples": [],
    "see_also": []
}