SQL Add Column: A How-To Guide

There is no SQL ADD COLUMN statement. To add a column to an SQL table, you must use the ALTER TABLE ADD syntax. ALTER TABLE lets you add, delete, or modify columns in a table.

After you have created a table in SQL, you may realize that you forgot to add in a specific column that you need. For example, say you may have created a database to store a list of suppliers. Maybe you forgot to include a field to store the category of goods the supplier offers.

In these situations, you can use the SQL ALTER TABLE statement to amend an existing table. This statement allows you to add a column, change a column, or delete a column.

In this tutorial, we will break down the basics of the SQL ALTER TABLE statement. We’ll walk through an example to show how you can use the add column SQL syntax to create a new column in a table.

Creating a Table

Below is an SQL command that will create an employee database that stores three columns. This database stores name of an employee, their title, and the branch for which they work.

CREATE TABLE employees (
	name VARCHAR(50) NOT NULL,
	title VARCHAR(75) NOT NULL,
	branch VARCHAR(25) NOT NULL
);

But what if we’ve forgotten to add a column? Say we need to add a column that states whether an employee is on probation. That’s where the ALTER TABLE statement comes in.

SQL Add Column

To perform an SQL add column operation, use the ALTER TABLE ADD command. This command alters a table and adds in a column with the specified data type. Using this command, you can add a column to a table after you have created the table.

As we discussed above, we forgot to include a column that stores whether an employee is on probation. We can use the ALTER TABLE statement to alter our existing table and add in this new column.

The basic syntax for adding a new column is as follows:

ALTER TABLE table_name ADD column_name data_type constraints;

The SQL ALTER TABLE add column statement we have written above takes four arguments. First, we specify the name of our table. Then, we state name of the column we want to create.

Next, we specify data type we want that column to have. The last argument contains any constraints we want our new column to have.

Our new column is added to the database schema. We can set the value of the new column in any existing records using an SQL UPDATE statement. If we create a new record, we can use the SQL INSERT command. We will be asked to specify a value for the new column in any new records we add to the table.

Add Column to Table SQL Example

Let’s perform an SQL add column to table action. We’ll add a fourth column to an existing table, our “employees” table:

ALTER TABLE employees ADD probation BOOLEAN;

Now our table appears as follows:

The fourth column is called probation. It has been added to our table with the boolean data type.

If you want to add a column with an SQL NOT NULL constraint to an existing table, you must specify a default value. You can do this using the DEFAULT constraint. This is because NOT NULL protects a database against null values, and that policy applies to existing records. 

Here’s an example of this in action:

ALTER TABLE employees ADD probation BOOLEAN NOT NULL DEFAULT false;

Now our probation column has a NOT NULL value, and every employee’s probation status has been set to false in the database. Here’s a query that we can use to find employee names and their probation statuses:

SELECT name, probation FROM employees;

Our query returns the following:

SQL ALTER TABLE has a wide range of other functions. You can use the statement to drop a column or rename a table column in an existing table.

SQL Add Multiple Columns to a Table

You can add multiple columns to an SQL table using the ALTER TABLE syntax. To do so, specify multiple columns to add after the ADD keyword. Separate each column you want to add using a comma.

Suppose that we want to add two columns called “salary” and “bio” to our existing “employees” table. We could do so using this command:

ALTER TABLE employees
ADD salary FLOAT,
ADD bio VARCHAR(250);

We’ve added two columns to our table. The “salary” column stores a floating-point number (a number that can store a decimal). Our “bio” column is a text field that can store up to 250 characters.

Conclusion

The ALTER TABLE statement lets you add a column to a table on an SQL server. You must use the ALTER TABLE ADD keyword to add a column to a table. There is no ADD COLUMN command that adds a column to a statement.

To learn more about coding in SQL, read our How to Learn SQL guide.