SQL add column to Table

SQL add column to table with query example. Learn queries to add new column as a first column or at the end or after a specific column of a table. Example query to to set default value instead of default NULL etc. in MySQL database.

To add a new column to an existing table, first we need to Alter Table and then perform ADD SQL query to add the new column.

SQL add column to table Syntax

syntax:  MySQL

ALTER TABLE table_name
ADD [column_name] [data_type]  [FIRST |AFTER COLUMN_NAME];

“[FIRST |AFTER COLUMN_NAME];” statement is optional. If you want add a new column as a first column,then you need to add FIRST or if want to add new column after a specific column, then you need to add the option “AFTER COLUMN_NAME”. If you don’t add the option, then column will be added as a last column to the table. SQL Query example below.

SQL Syntax for adding new column as a first column of the table.

ALTER TABLE table_name
ADD [column_name] [data_type]  [FIRST];

Syntax for adding new column After a specific column

ALTER TABLE table_name
ADD [column_name] [data_type]  [AFTER COLUMN_NAME];

NOTE
If you use the below query to add new column then by default new column will be added at the end and the value will be set to NULL.

ALTER TABLE table_name
ADD [column_name] [data_type]

SQL add column example

Consider the table BOOKS. We will add a new column PRICE of float data types using below query

ALTER TABLE BOOKS
ADD PRICE FLOAT;

Let’s check the result if new column PRICE is added to the table by selecting all the columns from the table BOOKS using SQL SELECT clause.

SELECT * from books;

You will see the new column PRICE with value NULL to the table.

SQL Add column with default value

To add the default value, we need to use DEFAULT Constraint in SQL  add query.
ALTER TABLE BOOKS
ADD PRICE FLOAT DEFAULT 29.30

So, in the above example, the default value will be 29.30 instead of NULL.