Clause in SQL – Types with Syntax and Example (Part-1)
Earlier we have discussed the RDBMS Concept in SQL. Today, we will see Clause in SQL. This is our first part of SQL Clause Tutorial. In this, we will discuss 3 types of Clause in SQL and that is WITH Clause, SELECT Clause, and FROM Clause. Also, we will see nested table references.
So, let’s start SQL Clause Tutorial.
Keeping you updated with latest technology trends, Join DataFlair on Telegram
1. What is Clause in SQL Server?
We will study in detail SQL command which we have used in the previous tutorials SQL command follow standard SQL Syntax and functionality.
Basically, we use them to apply filters for queries and thus get a filtered result. The most important types are-
|DISTINCT Clause||Used to retrieve unique records|
|FROM Clause||Used to list out tables and join information|
|WHERE Clause||Used to filter results|
|ORDER BY Clause||Used to sort the query results|
|GROUP BY Clause||Used to group by one or more columns|
|HAVING Clause||Used to restrict the groups of returned rows|
In this section, we will discuss 3 types of SQL Clauses and that are –
- With Clause in SQL
- SELECT Clause in SQL
- FROM Clause in SQL
2. WITH Clause in SQL
This clause was first introduced by Oracle in the Oracle 9i release 2 database. It allows naming a table too. The queries can also be written with nested subqueries bit it will make the process of debugging more complex.
Non-recursive common table expressions are supported with the help of with clause they can be referenced as tables in subsequent with clause items and in the main query this clause can be thought as if it is providing query scope for temporary tables
Syntax of SQL WITH Clause
WITH name [(column, ...)] AS (query expression) ...
Syntax rules for WITH Clause in SQL
- The column name must be unique and if in any case, they are not unique then the column name list is to be provided
- If the columns that are with clause item are declared then there must be a match in the number of columns of the Quarry Express query expression projected and also is closed item must have a unique name
3. SELECT Clause in SQL
In SQL the queries which start with the select keyword usually refers as select statements.
Syntax of SELECT Clause in SQL
SELECT [DISTINCT|ALL] ((expression [[AS] name])|(group identifier.STAR))*|STAR ...
Syntax rules for SELECT Clause in SQL
- The aliased expressions can only be used in the order by clause or the output column names they cannot be used anywhere else in the query
- Only when the symbols are comparable the distinct may be specified
4. FROM Clause in SQL
This clause specifies for SELECT, UPDATE and DELETE for the target tables. We can use it in subquery specification, which is then used as a new relation on which an outer query is applied.
We cannot use the variables of correlation in sub queries which is from the relations.
- FROM table [[AS] alias]
- FROM table1 [INNER|LEFT OUTER|RIGHT OUTER|FULL OUTER] JOIN table2 ON join-criteria
- FROM table1 CROSS JOIN table2
- FROM (subquery) [AS] alias
- FROM TABLE(subquery) [AS] alias
- FROM table1 JOIN /*+ MAKEDEP */ table2 ON join-criteria
- FROM table1 JOIN /*+ MAKENOTDEP */ table2 ON join-criteria
- FROM /*+ MAKEIND */ table1 JOIN table2 ON join-criteria
- FROM /*+ NO_UNNEST */ vw1 JOIN table2 ON join-criteria
- FROM table1 left outer join /*+ optional */ table2 ON join-criteria
- FROM TEXTTABLE…
- FROM XMLTABLE…
- FROM ARRAYTABLE…
- FROM (SELECT …
i. From Clause Hints
MAKEIND, MAKEDEP, and MAKENOTDEP are used as hints in the process of determining and controlling the dependent join behavior. They should just be used in circumstances where the optimal plan is not used via optimizer using query structure, metadata and costing information.
These hints can be seen in the comment that proceeds the clause. They have to be specified infront of a clause and cannot be specified infront of any name table.
NO_UNNEST will be specified against a subquery from clause or view to instruct the planner to not merge the nested SQL within the encompassing query – additionally called read flattening.
Recommended Reading – SQL Query Optimization Tools
ii. Nested Table Reference
Nested tables may seem in the FROM clause with the TABLE keyword. They’re an alternate to using a view with normal join semantics. The columns projected from the command contained within the nested table is also used even as any of the other FROM clause projected columns in be part of criteria, the where clause, etc.
A nested table could have related to references to preceding FROM clause column references as long as INNER and LEFT OUTER joins are used. This is particularly helpful in cases wherever then nested expression is a procedure or function call.
- Valid example:
select * from t1, TABLE(call proc(t1.x)) t2
- Invalid example, as t1 appears after the nested table in the from clause:
select * from TABLE(call proc(t1.x)) t2, t1
The TEXTTABLE funciton processes character input to produce tabular ouptut. It supports each fixed and delimited file format parsing. The function itself defines what columns it projects. The TEXTTABLE function is implicitly a nested table and will be correlated to preceeding FROM clause entries.
TEXTTABLE(expression COLUMNS , ... [NO ROW DELIMITER] [DELIMITER char] [(QUOTE|ESCAPE) char] [HEADER [integer]] [SKIP integer]) AS name COLUMN := name datatype [WIDTH integer [NO TRIM]]
Expression – the text content to process, that should be convertible to CLOB.
NO ROW DELIMITER indicates that fixed parsing shouldn’t assume the presence of newline row delimiters.
DELIMITER sets the sector delimiter character to use. Defaults to ‘,’.
QUOTE as the name suggests, sets the quote, or qualifier, or a character which is used to wrap field values. Defaults to ‘”‘.
ESCAPE also as the name suggests sets the escape character to use if no quoting character is in use. This can be used in situations wherever the delimiter or newline characters are escaped with a preceding character, e.g. \,
HEADER specifies the text line number (counting each new line) on that the column names occur. All lines prior to the header are skipped. If HEADER is specified, then the header line are used to determine the TEXTTABLE column position by case-insensitive name matching. This can be especially useful in things wherever solely a set of the columns are required. If the HEADER value isn’t given, it defaults to one. If HEADER isn’t given, then columns are expected to match positionally with the text contents.
SKIP specifies the number of text lines (counting each new line) to skip before parsing the contents. HEADER should be specified with SKP.
WIDTH here indicates the fixed-width length of a column present in characters and not in bytes. The cr NL newline value counts as one character.
NO TRIM specifies that the text value mustn’t be trimmed of all leading and trailing whitespace.
Syntax Rules –
- If the width is given for one column it should be given for all columns and be a non-negative integer.
- If the width is given, then fixed width parsing is used and ESCAPE, QUOTE, and HEADER mustn’t be given.
- The width isn’t given, then NO ROW DELIMITER can’t be used.
The columns names should be not contain duplicates.
- When a HEADER parameter it returns 1 row [‘b’]:
SELECT * FROM TEXTTABLE(UNESCAPE('col1,col2,col3\na,b,c') COLUMNS col2 string HEADER) x
- When fixed width it returns 2 rows [‘a’, ‘b’, ‘c’], [‘d’, ‘e’, ‘f’]:
SELECT * FROM TEXTTABLE(UNESCAPE('abc\ndef') COLUMNS col1 string width 1, col2 string width 1, col3 string width 1) x
Recommended Reading – Like Clause in Detail
The XMLTABLE function uses XQuery to provide tabular output. The XMLTABLE function is implicitly a nested table and will correlate to preceding FROM clause entries. XMLTABLE is part of the SQL/XML 2006 specification.
XMLTABLE([,] xquery-expression  [COLUMNS , ... )] AS name COLUMN := name (FOR ORDINALITY | (data type [DEFAULT expression] [PATH string]))
- The elective XMLNAMESPACES clause specifies the namespaces that we use within the XQuery and COLUMN path expressions.
- The xquery-expression should be a valid XQuery. Every sequence item returned by the xquery are wont to produce a row of values as defined by the COLUMNS clause.
- If COLUMNS isn’t such as, then that’s an equivalent as having the COLUMNS clause: “COLUMNS OBJECT_VALUE XML PATH ‘.'”, that returns the entire item as an XML value.
- A FOR ORDINALITY column is typed as integer number and can return the 1-based item number as its value.
- If PATH isn’t such as, then the path are an equivalent because of the column name.
Syntax Rules –
- Also, there is only 1 FOR ORDINALITY column.
- The columns names should be not contain duplicates.
So, this was all in Clause in SQL. Hope you liked our explanation.
Hence, we have completed our first part of SQL Clauses Tutorial. In this, we discussed 3 main types of clause in SQL that is – WITH Clause, SELECT Clause, and FROM Clause. Further, in FROM Clause, we saw from clause hints, nested table reference, texttable and XMLtable. Also, we learned the syntax and syntax rules of SQL Clauses. Next, we will look at the 2nd part of SQL Clause.
Still, if you have any query regarding Clause in SQL, ask in the comment tab.
Also, you can check – SQL Constraints