What actually is a SQL clause?

Question

This may sound like an overly simple question but I'm not finding it easy to locate a proper answer.

To the question "What are sql clauses?" most of the resources on internet simply provide a list of clauses and explain what they do.

But I'm trying to understand in abstract terms how it is defined. Like a generic notation that captures all kinds of queries that can be written and how clauses fit in there.

Are there properties common to all clauses? Why is UNION called an operator whereas HAVING is a clause?

Answer 1

In general terms, a clause is just a logical chunk of a SQL statement - and, usually, is a chunk that is (theoretically) optional.

I'm a SQL Server guy, so I'll be thinking in those terms.

SELECT GETDATE() is a valid SQL Server SELECT statement. It'll return the current date and time.

If I add a FROM clause:

SELECT GETDATE()
  FROM sys.objects

I'll get back n rows (where n is the number of rows in the sys.objects table), each of which will have the same (unnamed) column, showing the current date and time.

You can add a WHERE clause to limit the number of rows you bring back; a GROUP BY clause to either deduplicate your data, or to allow you to put aggregate values in the SELECT list; a HAVING clause (usually to eliminate rows based on aggregate data); an ORDER BY clause to sort the data - but, none of those have to be there.

You'll note I said "SELECT list" when referring to the list of columns returned. I've never heard this referred to as a clause - presumably, because it does have to be there.

An operator, on the other hand is used to combine or compare two complete items. Just like the + operator lets you combine numbers (1 + 2) or strings ([firstname] + ' ' + [lastname]), the UNION, EXCEPT, DIFFERENCE, and INTERSECT operators let you combine two SQL statements.

I'm probably oversimplifying, but thinking in these terms should at least head you in the right direction.

Answer 2

You can see the listing that Oracle provides:

CONSTRAINT clause
FOR UPDATE clause
FROM clause
GROUP BY clause
HAVING clause
ORDER BY clause
The result offset and fetch first clauses
USING clause
WHERE clause
WHERE CURRENT OF clause

(Some of this is not 100% SQL standard).

I'd call a clause a "well defined parts of a SQL statement, normally optional". Taking a hint from Grammar (where a clause is defined as "the smallest grammatical unit that can express a complete proposition"), each clause is meaningful. It has also a specific syntax.

A statement (equivalent, somehow, to a sentence in grammar) can be comprised of a single clause (SELECT something), or several of them (SELECT something .. FROM a_table ... WHERE a_condition_is_met ... GROUP BY some_column ... HAVING some_property ... ORDER BY some_criteria)

Answer 3

You can find a good reference on: ANSI SQL-1992

The second standardization of the SQL language.