How to Add a New Column to a Table in SQL

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.

Introduction to SQL ADD COLUMN clause #

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

Here’s the basic syntax of the ALTER TABLE ... ADD COLUMN statement:

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

In this statement:

First, provide the name of the table (table_name) to which you want to add the new column.
Second, specify the column’s definition after the ADD COLUMN clause.

Some databaes systems allows you to add multiple columns to an existing table once with the following syntax:

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

Different database systems support the ALTER TABLE... ADD COLUMN statement with some variances.

SQL ADD COLUMN examples #

First, create a new table called leave_requests:

CREATE TABLE leave_requests (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  employee_id INT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, use the ALTER TABLE ... ADD COLUMN statement to add the leave_type column to the leave_requests table:

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

Third, add two columns status and requested_date to the leave_requests table at the same time:

ALTER TABLE leave_requests
ADD COLUMN status VARCHAR(25)
ADD COLUMN requested_date DATE;Code language: SQL (Structured Query Language) (sql)

Summary #

Use the ALTER TABLE ... ADD COLUMN statement to add a new column to an existing table.

Databases #

Quiz #

Was this tutorial helpful ?