SQL ADD COLUMN

Summary: in this tutorial, you will learn how to use the SQL ADD COLUMN clause of the ALTER TABLE statement to add one or more columns to an existing table.

Overview of SQL ADD COLUMN clause

To add a new column to a table, you use the ALTER TABLE ADD COLUMN statement as follows:

ALTER TABLE table_name
ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

In this statement,

• First, specify the table to which you want to add the new column.
• Second, specify the column definition after the ADD COLUMN clause.

The typical syntax of the column_definition is as follows:

column_name data_type constraint;
Code language: SQL (Structured Query Language) (sql)

If you want to add multiple columns to an existing table using a single statement, you use the following syntax:

ALTER TABLE table_name
ADD [COLUMN] column_definition,
ADD [COLUMN] column_definition,
 ...;
Code language: SQL (Structured Query Language) (sql)

Different database systems support the ALTER TABLE ADD COLUMN statement with some minor variances. Please check it out the next section for references.

SQL ADD COLUMN examples

The following statement creates a new table named candidates:

CREATE TABLE candidates (
    id INT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);
Code language: SQL (Structured Query Language) (sql)

In order to add the phone column to the candidates table, you use the following statement:

ALTER TABLE candidates
ADD COLUMN phone VARCHAR(50);
Code language: SQL (Structured Query Language) (sql)

To add three columns: home address, date of birth, and linkedin account to the candidates table, you use the following statement:

ALTER TABLE candidates
ADD COLUMN home_address VARCHAR(255),
ADD COLUMN dob DATE,
ADD COLUMN linkedin_account VARCHAR(255);
Code language: SQL (Structured Query Language) (sql)

SQL ADD COLUMN statement in some common database systems

The following section provides you with the syntax of the ALTER TABLE ADD COLUMN statement in some common database systems.

PostgreSQL

Add one column to a table in PostgreSQL:

ALTER TABLE table_name
ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in PostgreSQL:

    ALTER TABLE table_name
    ADD COLUMN column_definition,
    ADD COLUMN column_definition,
    ...
    ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

MySQL

Add one column to a table in MySQL:

    ALTER TABLE table_name
    ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in MySQL:

    ALTER TABLE table_name
    ADD [COLUMN] column_definition,
    ADD [COLUMN] column_definition,
    ...
    ADD [COLUMN] column_definition;
Code language: SQL (Structured Query Language) (sql)

Oracle

Add one column to a table in Oracle:

ALTER TABLE table_name
ADD column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in Oracle:

ALTER TABLE table_name 
ADD (
    column_definition,
    column_definition,
    ...
);
Code language: SQL (Structured Query Language) (sql)

SQL Server

Add one column to a table in SQL Server:

ALTER TABLE table_name
ADD column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in SQL Server:

ALTER TABLE table_name
ADD
    column_definition,
    column_definition,
    ...;
Code language: SQL (Structured Query Language) (sql)

SQLite

Add one column to a table in SQLite:

ALTER TABLE table_name
ADD COLUMN column_definition;
Code language: SQL (Structured Query Language) (sql)

SQLite does not support adding multiple columns to a table using a single statement. To add multiple columns to a table, you must execute multiple ALTER TABLE ADD COLUMN statements.

DB2

Add one column to a table in DB2

ALTER TABLE table_name
ADD column_definition;
Code language: SQL (Structured Query Language) (sql)

Add multiple columns to a table in DB2:

ALTER TABLE table_name
ADD
    column_definition
    column_definition
    ...;
Code language: SQL (Structured Query Language) (sql)

Notice that there are no commas between columns.

In this tutorial, you have learned about the SQL ADD COLUMN clause of the ALTER TABLE statement to add one or more columns to an existing table.