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,

ALTER TABLE "table_name"
ADD "column_name" "Data Type";

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

ALTER TABLE "table_name"
ADD COLUMN "column_name" "Data Type" [, ...];

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

ALTER TABLE "table_name"
ADD COLUMNS "column_name" "Data Type";

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:

ALTER TABLE Customer ADD Gender char(1);

Oracle:

ALTER TABLE Customer ADD Gender char(1);

SQL Server:

ALTER TABLE Customer ADD Gender char(1);

Google BigQuery:

ALTER TABLE Customer ADD COLUMN Gender char(1);

SparkSQL:

ALTER TABLE Customer ADD COLUMNS Gender char(1);

HiveQL:

ALTER TABLE Customer ADD COLUMNS Gender char(1);

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:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

Oracle:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

SQL Server:

ALTER TABLE Customer ADD (Email char(30), Telephone char(20) );

Google BigQuery:

ALTER TABLE Customer ADD COLUMN Email char(30), ADD COLUMN Telephone char(20);

SparkSQL:

ALTER TABLE Customer ADD COLUMNS (Email char(30), Telephone char(20) );

HiveQL:

ALTER TABLE Customer ADD COLUMNS (Email char(30), Telephone char(20) );

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.