SQL Add Column Syntax

To add a column to a table using SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the ADD command to tell the RDBMS that we want to add a column.

Syntax

For MySQL, Oracle, and SQL Server, the syntax for ALTER TABLE Add Column is,

For Google BigQuery, the syntax for ALTER TABLE Add Column is,

For SparkSQL and Hive SQL (HiveQL), the syntax for ALTER TABLE Add Column is,

Examples

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

Example 1: Add one column to a table

Our goal is to add a column called "Gender". To do this, we key in:

MySQL:

Oracle:

SQL Server:

Google BigQuery:

SparkSQL:

HiveQL:

The resulting table structure is:

Table Customer

Note that the new column Gender becomes the last column in the Customer table.

Example 2: Add multiple columns to a table

It is also possible to add multiple columns. To do so, start with a parenthesis, then add each column name and its data type separated by comma, in the order that you want the columns to appear.

For example, if we want to add a column called "Email" and another column called "Telephone", we will type the following:

MySQL:

Oracle:

SQL Server:

Google BigQuery:

SparkSQL:

HiveQL:

The table now becomes:

Table Customer

Please note that Spark only has a string data type. Specifying a data type of char() will work in SparkSQL, but the new column will have a data type of string.