Add Column to Table in SQL (with Examples)

Various complex operations on SQL tables are directly or indirectly linked to tasks such as creating a new table, altering it, or adding multiple columns. Creating a new column is a frequently performed operation in the real world. In this article, we will learn how to add a new column to a table in SQL.

Let's first clarify our basics about what exactly are columns!

What is a Column in SQL?

A column represents the vertical space within a table where data is organized by the order of row values. Each column in SQL serves as a distinct field, showing specific types of information.

These columns play a crucial role in structuring the relational database, enabling the storage of diverse data types such as numbers, text, dates, and more.

Let us see an example of creating a basic table and then further altering columns in it:

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    reg_no VARCHAR(20) UNIQUE,
    gender VARCHAR(10),
age INT
);

INSERT INTO students (student_id, first_name, last_name, reg_no, gender, age)
VALUES
    (1, 'John', 'Doe', '2023001', 'Male', 18),
    (2, 'Jane', 'Smith', '2023002', 'Female', 19),
    (3, 'Alex', 'Johnson', '2023003', 'Male', 17),
    (4, 'Emily', 'Williams', '2023004', 'Female', 18);

SELECT * FROM students;

Output:

How to Add a Column in a SQL Table?

The ALTER TABLE statement in SQL is used to modify the structure of an existing table. One common operation performed with ALTER TABLE is adding a new column to the table. Adding a new column can be necessary when we need to include additional information in the database schema.

Let’s look at the syntax of the ALTER TABLE operation:

ALTER TABLE table_name

ADD COLUMN column_name column_definition;

SELECT * from table_name;

Let’s use this as an example:

ALTER TABLE students
ADD COLUMN city VARCHAR(50);
SELECT * from students;

Output:

Now, we can add some values to the new column using the SET command. Let’s try to add values with this SQL query:

UPDATE students
SET city = CASE student_id
    WHEN 1 THEN 'New York'
    WHEN 2 THEN 'Los Angeles'
    WHEN 3 THEN 'Chicago'
    WHEN 4 THEN 'San Francisco'
    END
WHERE student_id IN (1, 2, 3, 4);

SELECT * FROM students;

Output:

How to Add Multiple Columns in a SQL Table?

Using multiple columns in a single ALTER TABLE statement allows us to efficiently apply changes to several columns. When altering columns, We can modify the datatype of a column, adjusting it to suit our requirements.

We can add multiple columns directly after writing ALTER TABLE once. This is how we can perform this operation:

ALTER TABLE students
ADD COLUMN email VARCHAR(100),
ADD COLUMN phone_number VARCHAR(15);

Output:

Most of the databases support this, but for some SQL databases like SQLite adding multiple columns requires multiple ALTER TABLE Statements. This is the syntax that you want to use in the case:

ALTER TABLE students
ADD COLUMN email VARCHAR(100);

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(15);

How to Add Columns at Specific Position?

Organizing columns at specific positions in an SQL table offers several advantages. Firstly, it enhances the overall readability of the database schema, making it easier for developers and administrators to comprehend the structure. By logically grouping related columns, this approach promotes a clearer understanding of data relationships.

Here is an example of adding columns at specific positions:

ALTER table students 
ADD COLUMN Title VARCHAR(20) AFTER student_id;

Output:

As mentioned earlier, the ability to specify the exact position of a new column in an existing table depends on the database system. Unfortunately, not all database systems support this feature directly in the ALTER TABLE statement. However, if the database supports it, we can use a workaround, such as creating a new table with the desired column order.

There is an alternative method for the above operation as well. These are the steps:

• First, we will create a new temporary table with the preferred column order. 
• Then, we will copy the data from the original table to this temporary table, making sure the chosen column is in its designated position. 
• We will delete the original table and rename the temporary table to complete the task.

Let’s look at the query for the same:

/*Create a new table with the desired column order*/
CREATE TABLE students_temp AS
SELECT
    student_id,
    title,
    first_name,
    last_name,
    reg_no,
    gender,
    age,
    email,
    phone_number
FROM students;

/*Drop the old table*/
DROP TABLE students;

/*Rename the new table to the original table name*/
ALTER TABLE students_temp
RENAME TO students;

SELECT * FROM students;

How to Add Columns with Default Value?

Default values are a common feature in various database schemas, serving a major role in allowing database integrity checks. They enable verification processes, such as determining the present status of a credit card transaction. This status can be categorized as either "paid" or "unpaid". As a fundamental component of database design, default values ensure the consistency of data entries.

Here is the SQL Query to add columns with Default Value:

ALTER TABLE students
ADD COLUMN attendance VARCHAR(20) DEFAULT 'Present';
SELECT * FROM students;

Output:

Conclusion

Overall in this article, we discovered how to play around with ALTER Table to add columns in a SQL Table. We have gained some cool skills for working with tables and making data work for us. Now you carry a new proficiency with working with columns and managing data in databases easily. For more clarity, our top SQL Tutors are always ready to help you online.