SQL Clause, Statement, Command, Expression and Batch Defined

Problem

I often hear the words clause, statement, command, expression, batch, etc., used when describing certain aspects of SQL Server. Some DBAs call "SELECT" a statement, while others refer to it as a clause. Is there a difference? Can someone tell me the difference between these in a way that a novice can understand?

Solution

In this article, we will briefly describe these common descriptive words that define the basic structure of SQL Server syntax. While some overlap exists between one term and another, we will break each into easy-to-understand definitions to illustrate the slight differences.

Items that will be addressed:

• SQL Statement
• SQL Command
• SQL Batch
• SQL Query
• SQL Clause
• SQL Expression
• SQL Function

SQL Statement

SQL Server "statements" are the most basic unit of code that can be executed in SQL Server.

A statement is typically a single line of code, although multiple lines may be combined into a single statement. You can also use a semicolon (;) as a batch terminator that separates one command statement or batch from another. For example, the SELECT statement, a part of the DML command set, is considered a statement regarding the instruction of what you want to do. Whereas the FROM clause determines what sub-action you want the SELECT statement to take. 

Here is an example of a statement using SELECT:

SELECT 1;

Results:

SQL Command

Commands are often referred to as a STATEMENT or QUERY. However, a SQL command is any instruction set that directs SQL Server to perform an action against a given SQL Server object. Normally, you refer to the SELECT statement as a member of the DML command set. As a DBA, the commands you may work with most often will fall into the category of the DML command set, but the others will be used as well.

SQL Command Table

SQL commands are divided into four main categories:

1. Data Definition Language (DDL) statements are used to create and modify database objects such as tables, views, stored procedures, and user-defined functions.
2. Data Manipulation Language (DML) statements are used to retrieve, insert, update, and delete data from SQL Server database objects.
3. Data Control Language (DCL) statements grant or deny permissions to database objects based on a user’s login credentials.
4. Transaction Control Language (TCL) statements are used to save or revert actions implemented on database objects.

The following table breaks down these categories and lists the command statements that allow data manipulation of database objects by category:

We use the word "command" in a high-level discussion about SQL Server statements. We could say that the SELECT, UPDATE, and DELETE statements are within the group of DML type of commands.

SQL Batch

A SQL Server "batch" is a single or multi-line block of code that tells the SQL Server what action to take on a SQL Server object. Batches are typically separated by the "GO" statement at the end of a SQL query; thus, the "GO" statement is also referred to as a "batch separator."

Below is an example of three different SQL queries separated by the "GO" batch separator.

USE AdventureWorks2019;
GO
SELECT *
FROM HumanResources.Department;
GO
SELECT *
FROM HumanResources.Employee;
GO

At first glance, some may look at the above query as having only two batches. Realistically, the "USE AdventureWorks2019" statement is part of its own batch. Remember: GO is the batch separator, so you can easily count the number of batches by counting the GO separators.

Technically, the GO batch separator is also a statement. It instructs SQL Server to run the statements above it and close the batch process.

SQL Query

The "query" keyword in SQL Server is used to perform an action against a database or database object.

You would use the word "query" when discussing a set of commands at a high level. When you want to manipulate data in a SQL Server database, you first open a "query window" in SQL Server Management Studio (SSMS). This query editor is where you place all the statements, filters, clauses, etc., to manipulate your data. You may have one or more batches or blocks of code within a single query.

For example:

USE AdventureWorks2019;
GO
SELECT *
FROM HumanResources.Department;
GO
SELECT *
FROM HumanResources.Employee;
GO

In the sample above, I may want to run the entire query, or I may want to run the last commands within this query. A SQL Server query could be as simple as the "SELECT 1;" example mentioned earlier in this article or as complex as you need. In short, a query is a complete block of code, also known as a batch, that gets executed as a whole.

While we could look at the above code block as being one query, we can also refer to each batch as a query. So, we have three small queries within a large query.

SQL Clause

A SQL Server "clause" is a code element that defines a particular action. Clauses are typically used to control the flow of a query or Transact-SQL program.

The most common clauses are the FROM, WHERE, and ORDER BY clauses. The SELECT clause is used to specify the columns or expressions that will be returned by a query.

Okay, did you catch that? Here we are calling "SELECT" a clause. Earlier, it was listed as a statement. In most respects, SELECT is a statement, but it can be considered a clause in the relationship we are referencing here. A clause sorts, filters, etc., our data results. The SELECT statement, although it is considered a statement, filters our data by returning only specific columns from one or more tables. So, in this example, SELECT can be both a statement and a clause. Either term will work in this situation.

The FROM clause is used to specify the tables or other data sources that the query will access. The WHERE clause is used to filter rows based on specified conditions. The ORDER BY clause is used to sort the results of a query. Clauses can be combined in various ways to create complex queries and programs. For example, the WHERE and ORDER BY clauses can be combined to sort rows based on specific conditions. Similarly, the FROM and ORDER BY clauses can be combined to sort data from multiple sources. In general, SQL Server clauses are very powerful and flexible and can be used to solve a variety of problems. With a little practice, you should be able to use them effectively in your work.

In general, a SQL Server clause is an action that needs to be taken on a particular statement or set of statements. A clause is most commonly used to filter certain data or return the data in a certain way.

SQL Expression

SQL Server "expression" is a keyword used in Transact-SQL (T-SQL) that allows for the specification of a computation, or expression, to be performed on one or more columns in a table.

Expressions can be used in various places within T-SQL code, such as the SELECT, WHERE, and ORDER BY clauses. Computations performed as part of an expression can involve mathematical operators (+, -, *, /), logical operators (AND, OR, NOT), bitwise operators (&, |), and others.

Additionally, expressions can use built-in functions, such as ISNULL() and CASE(). Expressions can also reference columns by name. When used in the SELECT clause, expressions can be used to compute new values from existing ones (e.g., to calculate discounts or tax rates), or they can be used to generate entirely new columns (e.g., to create a column that contains full names based on first and last name columns).

In the WHERE clause, expressions can filter rows based on computed values (e.g., only selecting rows where the discount rate is greater than 10%). And in the ORDER BY clause, expressions can be used to sort data according to computed values (e.g., to sort rows by full name instead of just last name).

SQL Function

SQL Server "functions" are powerful tools that can perform various operations on data. Functions can be used to manipulate data, create new data, or even delete data. Functions can be used to perform mathematical operations, string operations, or date/time operations. Many different functions can be used in SQL Server, and each has its purpose and syntax.

As a general rule, functions in SQL Server and other programming languages are followed by opening and closing parenthesis (). In some programming languages, such as C#, JavaScript, etc., these functions can also be referred to as methods. The parentheses hold two different uses:

1. Inside the parentheses is where you would place any needed parameters.
2. The fact that the parentheses are present indicates that it is a function in most cases.

A function can also be referred to as a clause. For example, when using the .WRITE() function in a SQL query, it can take the form of a clause because we are using that function to update or modify specific data within a table:

UPDATE tableName
SET columnName .WRITE('expression', @OffsetValue, @LenghtValue) 

In this scenario, we are using what is obviously a function with three parameters as a clause to update our database table.

Wrap Up

As you can see from the sections above, there are some noticeable differences between one term and another. But in the real world, you will often hear them used interchangeably. In your day-to-day job, this usually is not an important issue, and no one will criticize you for using the wrong term out of place. However, if you are going for a job interview, you need to understand the slight differences as you may be asked the differences—just some food for thought.

Generally speaking, the real difference between statements, clauses, batches, etc., is determined by how you use them at the time.

Next Steps

• SQL Server Queries
• SQL Server Functions
• SQL Server Expressions