Data Query Language (DQL) Syntax

DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it. We can define DQL as follows it is a component of SQL statement that allows getting data from the database and imposing order upon it. It includes the SELECT statement. This command allows getting the data out of the database to perform operations with it. When a SELECT is fired against a table or tables the result is compiled into a further temporary table, which is displayed or perhaps received by the program i.e. a client.

 

statement:

| explain
| describe
| query

 

statement list:

 statement [ ';' statement ]* [ ';' ]

 

explain:

EXPLAIN PLAN
 FOR { query }
EXPLAIN ANALYZE
 FOR { query }

 

Describe:

|DESCRIBE [ STATEMENT ] { query }

 

query:

 values
| WITH withItem [ , withItem ]* query
|{
    select
   | selectWithoutFrom
   }
   [ ORDER BY orderItem [, orderItem ]* ]
   [ LIMIT [ start, ] { count | ALL } ]
   [ OFFSET start ]

    

withItem:

 name
 [ '(' column [, column ]* ')' ]
 AS '(' query ')'

 

orderItem:

expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

 

select:

This is used to retrieve data from the database.

 SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }
  FROM tableExpression
  [ WHERE booleanExpression ]
  [ GROUP BY { groupItem [, groupItem ]* } ]
  [ HAVING booleanExpression ]
            
 
selectWithoutFrom: [Scalar]
  SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }

 

projectItem:

  expression [ [ AS ] columnAlias ]
| tableAlias . *

 

tableExpression:

  tableReference [, tableReference ]*
| tableExpression [ NATURAL ] [ { LEFT | RIGHT }] JOIN tableExpression [ joinCondition ]          

 

joinCondition:

  ON booleanExpression
| USING '(' column [, column ]* ')'

 

tableReference:

 tablePrimary
[ FOR SYSTEM_TIME AS OF expression ]
[ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]        

 

tablePrimary:

 [ [ catalogName . ] schemaName . ] tableName
 '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
| [ LATERAL ] '(' query ')'
| [LATERAL ] TABLE '(' [ SPECIFIC ] functionName '(' expression [, expression ]* ')' ')'          

 

columnDecl:

 column type [ NOT NULL ]

 

optionValue:

stringLiteral
numericLiteral

 

columnOrList:

  column
|'(' column [, column ]* ')'

 

exprOrList:

 expr
| '(' expr [, expr ]* ')'

 

values:

VALUES expression [, expression ]*

 

groupItem:

   expression
 | '(' ')'
 | '(' expression [, expression ]* ')'
| GROUPING SETS '(' groupItem [, groupItem ]* ')'