SQL Clauses and Its Types – Syntax and Example (Part-2)

Earlier we have discussed the first part of Clause in SQLin which, we discussed 3 SQL Clauses that are WITH, SELECT and FROM Clause. Today we will move towards the 2nd part of SQL Clauses. In this part of SQL Clauses, we will see ARRAYTABLE, GROUP BY, HAVING, ORDER BY, LIMIT, INTO, OPTION, and WHERE Clause in SQL. Also, we will look at their syntax and examples.

So, let’s explore the SQL Clauses.

SQL Clauses

SQL Clauses and Its Different Types

Recommended Reading – SQL Like Clause

1. What are the types of SQL Clauses?

i. ARRAYTABLE

As the name suggests, the function define what columns it projects. It may correlate to from clause entries and it is implicitly a nested table.

Syntax rules

The columns in the functions should not have any duplicates.

Usage

ARRAYTABLE(expression COLUMNS <COLUMN>, ...) AS name
COLUMN := name datatype

ii. WHERE clause

This clause set the criteria for the records to limit affected by select delete and update statements.

We use the SQL WHERE clause when we require to fetch the data from single or multiple tables. If the condition is specified then it will return a specific value. We can also use the WHERE clause with the UPDATE, DELETE SQL statement, etc and does not limit to SELECT statement.

The general form of the WHERE is:

WHERE criteria

iii.  GROUP BY clause

This clause shows that the rows should be classified according to the specified expression values for every group. One row shall be returned after optionally filtering those aggregate rows which are done by HAVING Clause.

Syntax rules

  • The column differences in this type of Clause must have unaliased output columns.
  • Expressions must appear in the SELECT Clause which we use in the GROUP BY.
  • SELECT Clause use the column references and expressions and that GROUP BY Clause does not use. It should appear in the aggregate function.
  • If in any situation we use an aggregate function with the SELECT Clause and there is no GROUP BY specified then an implicitly GROUP BY Function will perform on the entire result.
  • The columns in GROUP BY Clause must be of comparable type.

The general form of the GROUP BY is:

GROUP BY ,expression)*

iv. HAVING Clause

This clause works exactly as the where clause with the only difference that it operates on the output of the GROUP BY clause even the syntax is same.

The expressions used in this clause must contain an aggregate function that is COUNT, AVG, SUM,  MIN, MAX.

v. ORDER BY Clause

We use this clause to specify how the records have to be stored and sorted. The options are ascending (ASC) and descending(DESC).

Usage:

ORDER BY expression [ASC|DESC] [NULLS (FIRST|LAST)], ...

Syntax Rules:

  • Sort columns are also such as positionally by a 1-based positional integer, by SELECT Clause alias name, by SELECT Clause expression, or by an unrelated expression.
  • Column references might appear within the SELECT Clause as the expression for an aliased column or may reference columns from tables within the FROM Clause. If the column reference isn’t within the select clause the query should not be a set operation, specify SELECT DISTINCT, or contain a GROUP BY Clause.
  • Unrelated expressions, expressions not showing as an aliased expression within the select clause, are allowed within the ORDER BY Clause of a non-set query. The columns referenced within the expression should come from the FROM Clause table references. The column references can’t be to alias names or positional.
  • The ORDER BY columns should be of a comparable type.
  • If we use an ORDER BY in an inline view or view definition without a limit clause, the optimizer will remove it.
  • Now, if NULLS FIRST/LAST is known, then nulls are guaranteed to be sorted either first or last. If the null ordering isn’t known, then results can usually be sorted with nulls as low values that is internal default sorting behavior. But not all sources return results with nulls sorted as low values by default, and may return results with different null orderings.

ix. LIMIT clause

This clause sets limit to the number of records returned by the SELECT clause. Here an optional offset can also be specified, that is the number of rows to be skipped.

If another clause, that is ORDER BY is also specified then it will be applied before OFFSET/LIMIT, and if not specified then the order is not fixed.

It can also be specified via SQL 2008 OFFSET/FETCH FIRST clause.

Usage:

LIMIT [offset,] limit
[OFFSET offset ROW|ROWS] [FETCH FIRST|NEXT [limit] ROW|ROWS solely

Syntax Rules:

  • The limit/offset expressions should be a non-negative number or a parameter reference (?). An offset of 0 is ignored and a limit of 0 will return no rows.
  • The terms FIRST/NEXT are interchangeable additionally as ROW/ROWS.
  • The limit clause might take an optional preceding NON_STRICT hint to indicate that push operations should not be repressed even though the results won’t be in line with the logical application of the limit. The hint is only required on unordered limits.

Have a look at SQL Wildcard Characters

x. INTO Clause

When we declare the INTO clause with a SELECT keyword, then the results will insert into a specific table. We often use this in inserting records of temporary tables.

This precedes the FROM clause.

Usage:

INTO table FROM ...

Syntax Rules:

  • The INTO clause is logically applied last that is after ORDER BY and LIMIT clauses.
  • . Select INTO mustn’t be used UNION query.

xi. OPTION Clause

The keyword helps to denote the options available with user that can pass in with the command.

This SQL clause specifies that the indicated query hint should be used throughout the entire query and for each query hint can be specified only one time, but also multiple query hints are permitted.

The condition with this clause one OPTION clause can be specified with the statement.

OPTION clause can be specified in the SELECT, UPDATE, DELETE and MERGE statements.

Do you know about SQL Date Functions?

Usage:

OPTION option, (option)*

So, this was all in SQL Clauses Part 2. Hope you liked the explanation.

2. Summary

Hence in this SQL Clauses tutorial, we covered different clauses such as WHERE Clause, GROUP BY Clause, HAVING BY Clause, ORDER BY Clause, LIMIT Clause, INTO Clause, OPTION Clause with their syntaxes. Still, if you have any query related to SQL Clauses, ask freely in the comment tab.

You may like – 

SQL Query Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.