SQL How to Add a Column : 4 ways with examples

picture depicting teaching

In this post on SQL How to add a column, let us look at 4 different approaches to adding columns to a database table. But first, let’s start with some context.

A column is the smallest unit for capturing an object’s attribute. Let that sink in !

An attribute is nothing more than a property. For instance, if I want to capture information about a Person, what type of information would make sense ?

What would uniquely define a person ?

Name definitely, Height, Weight, Age, Gender, Race, Date of Birth just to name a few. It’s starting to make sense isn’t it ? 

Each of these attributes of a person is stored in a separate column. A grouping of these columns specific to a single person then constitutes a row in a table.

To limit the scope of this post, I wont go past the above explanation. However, if you like to learn more, email me at info@obstkel.com and maybe I will write up another post.

Moving on to the next step, how do we sql add column to a table ?  

For that you need to use DDL commands !

DDL stands for Data Definition Language and is associated with defining objects in a database. Depending on the database vendor, the commands considered DDL can vary slightly. However, CREATE, ALTER, DROP are considered DDL universally. 

The syntax used in these examples are Oracle specific. However, you should be able to use the same in any other database with minimum tweaks. 

Now lets dive into the below create column sql examples.

  1. SQL How to add a Column with CREATE TABLE
  2. SQL How to add a Column using DROP and Re-CREATE
  3. SQL How to add a Column using ALTER TABLE
  4. SQL How to add a Column using CREATE TABLE AS (CTAS)

1. SQL How to add a Column with CREATE TABLE

The best way to add column(s) is when creating a table. using the CREATE DDL. This would require you to plan your data model well ahead of time. However, we all know how that goes. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar(30) default 'John',
last_name        varchar(30) default 'Doe',
email            varchar(60) default 'john.doe@xyz.com',
phone            varchar(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer     default 0
);

2. SQL How to add a Column using DROP and Re-CREATE

In some cases, you do not really care about the data in your table. For instance if you are working in a development environment and have dummy data or scrambled data. If this defines your situation, then the best option is to just copy the DDL for the existing table, drop the table and then recreate it with the new fields you need. The steps to follow this approach are listed below.

STEP 1: Copy the table DDL into a text editor

STEP 2: After that, drop the table

DROP TABLE employees;

STEP 3: Recreate the table with the new columns. In this case we add MIDDLE_NAME, SUFFIX and DOB to the new DDL.

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar(30) default 'John',
middle_name     varchar(30),
last_name        varchar(30) default 'Doe',
suffix     varchar(10),
email            varchar(60) default 'john.doe@xyz.com',
phone            varchar(15) default '000-000-0000',
dob     date,
hire_date        date        default '1901-01-01',
sales_id         integer     default 0
);

3. SQL How to add a Column using ALTER TABLE

Now let’s look at a third option to sql add column using an ALTER TABLE command.

If you have data in your table and do not want to loose it, or any of the constraints and permissions, then an ALTER TABLE command is the best. You can add a single column or multiple columns with constraints and data type to a single table using this statement. 

However, do keep in mind that an ALTER TABLE adds the column to the end of the table as the last column. 

Using the EMPLOYEES tables from the previous example, lets add MIDDLE_NAME to this table.

OPTION1 : Adding a single column with constraint and data type to a table.

ALTER TABLE employees 
ADD middle_name varchar2(30) NOT NULL;

OUTPUT: The table created from running the ALTER TABLE statement is shown below. Pay close attention to how the newly created column is appended to the end of the table. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar2(30) default 'John',
last_name        varchar2(30) default 'Doe',
email            varchar2(60) default 'john.doe@xyz.com',
phone            varchar2(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer      default 0,
middle_name     varchar2(30) NOT NULL
);

OPTION 2 : Lets look at an example on how to add multiple columns to a table using the ALTER TABLE statement.

ALTER TABLE employees 
ADD
(middle_name varchar2(30) NOT NULL,
suffix varchar2(10) NOT NULL,
dob date);

OUTPUT: The resulting table from executing the above ALTER TABLE statement is shown below. Once again, fields MIDDLE_NAME, SUFFIX and DOB are added to the end of the table. 

CREATE TABLE employees
(
employee_id      integer(30),
first_name       varchar2(30) default 'John',
last_name        varchar2(30) default 'Doe',
email            varchar2(60) default 'john.doe@xyz.com',
phone            varchar2(15) default '000-000-0000',
hire_date        date        default '1901-01-01',
sales_id         integer      default 0,
middle_name     varchar2(30) NOT NULL,
suffix varchar2(10) NOT NULL,
dob date

);

4. SQL How to add a Column using CREATE TABLE AS (CTAS)

The fourth and final way to sql add column to a table is using a CREATE TABLE AS (CTAS) statement. This is an advanced technique and might get you frustrated. But if you are trying to expand your SQL skills, definitely give this approach a shot.

CTAS creates a table based on a Select statement from another table. A little-known feature that most developers do not realize is that you can utilize a CTAS statement to SQL add a column to your table. Matter of fact you can add a column anywhere you please – the beginning, middle or the end. Dealers choice!

Lets continue working with the EMPLOYEE table and assume that we want to add a couple of new columns – MIDDLE_NAME, SUFFIX and DOB (date of birth) in the 3rd, 5th and 8th position.

STEP 1: Select the fields you want from your table. In this case we select from the EMPLOYEES table

SELECT employee_id,first_name, last_name,email,phone,hire_date,sales_id FROM employees;

STEP 2: Add columns MIDDLE_NAME, SUFFIX and DOB in the position(s) you want

SELECT employee_id, first_name, middle_name, last_name, suffix, email, phone, dob, hire_date, sales_id  
FROM
employees;

STEP 3: Now comes the tricky part. We have to set a datatype for these new fields. For this we shall leverage the CAST function.  

SELECT 
employee_id,
first_name,
CAST (NULL as VARCHAR2 (30)) as middle_name,
last_name,
CAST (NULL as VARCHAR2 (10)) as suffix,
email,
phone,
CAST (NULL as DATE ) as dob,
hire_date,
sales_id
FROM employees;

STEP 4: The final create table as statement to sql add columns is listed below. Couple of point to keep in mind.

  • You have to give the newly created table a different name initially. In this case we named it “EMPLOYEES_NEW”
  • EMPLOYEES_NEW now contains all the data that exists in the old EMPLOYEES table
  • After creating EMPLOYEES_NEW table, you have to drop the old table using “DROP TABLE EMPLOYEES;”  and rename the table by issuing “RENAME EMPLOYEES_NEW to EMPLOYEES;”
  • Keep in mind, any indexes, constraints and permissions on the initial EMPLOYEES table would needed to be granted again
  • Lastly, please make sure you explore within our own schema and not anywhere near a production environment. 
CREATE TABLE employee_new AS
     SELECT
          employee_id,
          first_name,
          CAST (NULL as VARCHAR2 (30)) as middle_name,
          last_name,
          CAST (NULL as VARCHAR2 (10)) as suffix,
          email,
          phone,
          CAST (NULL as DATE) as dob,
          hire_date,
          sales_id
     FROM employees;

Wrapping things up

So let’s sum up the key takeaways from this post.

  • A single command to sql add a column does not exist. You have to use it in conjunction with an ALTER TABLE command.
  • If you are frequently adding columns to your table, consider creating a child table with the new columns. 
  • The fastest technique to sql add columns is using the CTAS method. However for this, you need to have a table or two with the columns you need.

And finally, a plug in for our post on Amazon Athena !

SQL is not limited to relational databases. 

For instance, Spark SQL, a module of Apache Spark lets users query structured data using a similar syntax. Similarly, Amazon Athena, a web service by AWS lets users write SQL against data stored in files

SQL helpful links

Oracle Database SQL Reference

For Oracle SQL syntax, expressions and function reference

Transact SQL (T-SQL) Reference

For SQL Server transact SQL functions, examples and syntax

MySQL Reference Manual

For MySQL related syntax, statements and examples

PostgreSQL Language Reference

For information related to PostgreSQL data types and functions

Email us at : info@obstkel.com

Copyright 2020 © OBSTKEL LLC. All rights Reserved.
Scroll to Top