What is difference between clause, command, statement and query in SQL?

Question

Sql Clause, Command and Statement, I see these words are used interchangeably. But they are grammatically different.

I read Some Difference here. Already Asked by someone. Still I am not clear.

The suggested question gives the difference between statement and clause only. And `SELECT foo FROM bar JOIN quux WHERE x = y;' is mentioned as Statement is the answer of that question. I want to Ask then what is the difference between Query and Statement.

So I am wondering what exactly is the difference between them? Or may I just go with the flow? Thanks

Answer 1

I will give you the terminology used with SQL Server. Some of these (certainly the ones in your question) will be extremely common across all database systems, some may be system specific.

From highest level to lowest:

• Script. A single file containing SQL code. May contain multiple batches

• Batch. A batch is the unit in which work is submitted to the server. In SQL Server, each batch is (normally) delimited by GO. Splitting the script into batches is a job performed by client tools. A Batch may contain multiple statements.

• Statement (a.k.a Command¹ or Query). This is the smallest unit of individual work that the server will work with. I.e. Every statement is something "complete", which will cause the server to perform some work and may result in data being modified and/or a result set being returned. Typically, the server will compile each statement individually (but may do each compilation for every statement in a Batch before any of them are executed).

• Clause². A Clause is a subunit of a statement - but, beware, some statements may consist of only a single Clause, which may appear to muddy the waters a little. For example, some database systems will accept SELECT 10; as a Query. This is a SELECT statement consisting only of a SELECT clause. Multiple statement types may use the same clause types. E.g. Both SELECT and DELETE statements may contain a WHERE clause. Also, most statements will have a clause that shares the same name.

• Expression². An expression is something that produces a scalar value (Note though that, in most contexts, this is understood to be "one scalar value per row", not "one scalar value in total")

  • Predicate. A boolean expression, most often encountered in WHERE clauses, WHEN clauses and CHECK constraints. These are especially called out because not all database systems support a user visible boolean data type, and so they're not always treated the same as other expressions.

---

¹Many client libraries will expose some kind of command object for submitting queries to the database system. However, to muddy the waters further, many of these will accept a batch. Nevertheless, command seems to have stuck as having a similar meaning as statement, possible because in the vast majority of cases, the command object isn't used for multiple statements in one go.

²Note that to some extent, these share the same level. A SELECT clause may contain a CASE expression, that consists of multiple WHEN and THEN clauses.