Topic 3 of 52 · Beginner
ALTER TABLE – ADD COLUMN
Add a new column to a table that already has data.
01ALTER TABLE – ADD COLUMN
Module 02 – Database & Table Management (DDL)
02What is it?
ALTER TABLE ... ADD COLUMN appends a new column to an existing table. All existing rows get the column too, filled with NULL or with the column's DEFAULT value.
03Why do we need it?
Requirements change: the business suddenly needs each employee's email, or a pipeline needs a loaded_at audit column. Dropping and recreating the table would lose data — ADD COLUMN keeps every row.
04Syntax
ALTER TABLE table_name
ADD [COLUMN] column_name datatype [constraints] [FIRST | AFTER other_column];MySQL 8.0 can add columns instantly (ALGORITHM=INSTANT) for most cases, so ADD COLUMN on large tables is fast.
05Parameter explanation
- table_name
- The existing table to change.
- COLUMN
- Optional keyword; ADD email ... and ADD COLUMN email ... are the same.
- column_name datatype
- Name and type of the new column.
- constraints
- Optional rules such as DEFAULT 'x' or NOT NULL (NOT NULL on a table with rows needs a DEFAULT).
- FIRST / AFTER col
- MySQL-only: position of the new column. Without it the column is added at the end.
06Sample database
Every example and exercise below starts from a fresh copy of this data.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
dept_id INT,
salary DECIMAL(10,2),
hire_date DATE
);
INSERT INTO departments VALUES (10,'Engineering'),(20,'Sales'),(30,'HR');
INSERT INTO employees VALUES
(1,'Asha',10,85000,'2021-04-12'),
(2,'Ravi',20,52000,'2022-01-03'),
(3,'Meena',10,97000,'2019-09-20'),
(4,'Karan',30,46000,'2023-06-15'),
(5,'Divya',20,61000,'2020-11-30');07Basic coding example
08Step-by-step execution
- 1.MySQL locates the employees table and validates that no column called email exists.
- 2.It adds email as a VARCHAR(100) at the end of the column list.
- 3.Every existing row (all 5 employees) receives NULL in the new column because no DEFAULT was given.
- 4.No rows are deleted or changed otherwise.
09Before and after output
Generated live by running the basic example against the sample database.
Running on the SQL engine…
10Intermediate coding example
11Real-world business scenario
An analytics team asks for a region on every customer. The data engineer runs ALTER TABLE customers ADD COLUMN region VARCHAR(30); then back-fills it with an UPDATE ... JOIN from a lookup table.
Pipelines add audit columns like loaded_at DATETIME DEFAULT CURRENT_TIMESTAMP so every row records when it arrived.
12Common mistakes
ALTER TABLE employees ADD email;
No data type was given.
ALTER TABLE employees ADD email VARCHAR(100);
ALTER TABLE employees ADD COLUMN name VARCHAR(60);
A column called name already exists → 'duplicate column name'.
ALTER TABLE employees ADD COLUMN full_name VARCHAR(60);
ALTER TABLE employees ADD COLUMN phone VARCHAR(15) NOT NULL;
Existing rows would have no phone. Strict databases reject it; MySQL fills '' which hides missing data.
ALTER TABLE employees ADD COLUMN phone VARCHAR(15) NOT NULL DEFAULT 'unknown';
13Important comparisons
| Need | Command |
|---|---|
| Add a new column | ALTER TABLE t ADD COLUMN c type |
| Change a column's type | ALTER TABLE t MODIFY COLUMN c newtype |
| Fill values into a column | UPDATE t SET c = value |
| Add many columns at once (MySQL) | ALTER TABLE t ADD c1 type, ADD c2 type |
14Practical exercises
Beginner1. Add a phone VARCHAR(15) column to employees and show all rows.
Reveal solution (15)
Every row shows NULL for phone.
Beginner2. Add location VARCHAR(30) to departments with default 'HQ'.
Reveal solution (15)
Existing rows receive the default.
Beginner3. Add a bonus DECIMAL(10,2) column to employees.
Reveal solution (15)
New numeric column starts as NULL.
Intermediate4. Add bonus, then set it to 10% of salary for everyone.
Reveal solution (15)
ADD COLUMN creates the slot; UPDATE fills it.
Intermediate5. Add a grade column defaulting to 'B', then set 'A' for salaries above 80000.
Reveal solution (15)
Defaults give a baseline that UPDATE refines.
Intermediate6. Add head_count INT to departments and fill it with the number of employees in each department.
Reveal solution (15)
A correlated subquery back-fills the new column.
Advanced7. Add an audit column created_at that defaults to the current timestamp and check every row has it.
Reveal solution (15)
MySQL fills existing rows with the default timestamp. (The browser engine requires a constant default for existing rows, so expect an error here — this is a real engine difference; use a constant like '2024-01-01' to try it.)
Advanced8. Add email and back-fill it as lowercase name + '@company.com'.
Reveal solution (15)
In MySQL write CONCAT(LOWER(name), '@company.com'); || is the portable ANSI form used by the browser engine.
16Key takeaways
- ADD COLUMN keeps all existing rows and data.
- Existing rows get NULL or the DEFAULT.
- NOT NULL on a populated table needs a DEFAULT.
- FIRST / AFTER positioning is MySQL-specific.
17Knowledge check
1. After ADD COLUMN bonus INT (no default), what do existing rows contain?
2. Which clause places a new column right after name in MySQL?
3. Which command fills values into the new column?
Interview questions
Is ALTER TABLE ADD COLUMN DDL or DML?
DDL — it changes the table's structure. Filling the column afterwards with UPDATE is DML.
Lesson test
Take an 8-question test on “ALTER TABLE – ADD COLUMN” — concepts, output prediction, bug spotting and interview questions.