SQL Add a New Column: 4 ways with examples
- Updated on:
- April 30, 2023
In this post on how to SQL add a new 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 won’t 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 and 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.
Lastly, the techniques mentioned in the next 4 examples are the same if you want to sql add multiple columns or just a single column.
Now let’s dive into the below create column sql examples.
1. SQL add a new column with CREATE TABLE
The best way to add column(s) is when creating a table using the CREATE DDL.
Likewise, a best practice would be to set column default values when you create your table. This; however, requires planning your data model well ahead of time.
The example below shows how to create a table using CREATE DDL and add default column values for date fields, varchar and integer data types at the same time.
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
);
Related post : The correct technique to find sql duplicates in your query
2. Add new column(s) in SQL 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.
In addition, you can specify column default values as we did in the previous example.
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 add a new column using ALTER TABLE
Now let’s look at a third option to sql add a new column using an ALTER TABLE command.
If you have data in your table and do not want to lose 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 in sql 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 add a new column using CREATE TABLE AS (CTAS)
The fourth and final way to sql add a new 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!
Let’s 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 new 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;
Trending questions on adding a new column
How to sql add column default value?
Setting defaults when creating a table is an absolute best practice. It helps us avoid NULL values and to some extend helps improve data quality.
First, use the keyword ‘default’ when creating your table ddl. Then depending on the data type set your column default values. For character/string values and date fields, make sure to enclose the value in single quotes. Numeric column defaults do not have to be enclosed in single quotes.
Refer the first example on Create table for syntax and usage.
How can I sql create new column in query output?
When you query a table, you are not changing the structure of the underlying table. Creating a new column in query output is as simple as adding a column alias.
You create a column alias by using the ‘as‘ keyword as shown in the image below.
Wrapping things up
So let’s sum up the key takeaways from this post.
- A single command to sql add a new 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
MySQL Reference Manual
For MySQL related syntax, statements and examples
Transact SQL (T-SQL) Reference
For SQL Server transact SQL functions, examples and syntax