SQL CRUD Operations: INSERT, SELECT, UPDATE, DELETE Explained (2026)

What Is CRUD and Why It Matters

CRUD stands for Create, Read, Update, Delete. These four operations represent every possible action you can perform on data stored in a database. Whether you are building a web application, running analytics, or managing a backend system, every interaction with your database boils down to one of these operations.

In SQL, CRUD maps directly to four statements:

• Create = INSERT - Add new rows to a table
• Read = SELECT - Retrieve data from one or more tables
• Update = UPDATE - Modify existing rows in a table
• Delete = DELETE - Remove rows from a table

Understanding CRUD is fundamental because every application feature translates to these operations. A user registration form runs an INSERT. A product listing page runs a SELECT. Changing your profile picture triggers an UPDATE. Canceling an account fires a DELETE. If you understand these four operations well, you can build and maintain any data-driven application.

This guide walks through each operation with progressively complex examples, covers syntax differences between major databases, and shares the safe practices that prevent costly mistakes in production.

CREATE: Inserting Data with INSERT

The INSERT statement adds new rows to a table. It is the "C" in CRUD and the starting point for getting data into your database.

Insert a single row

The most common form specifies the target columns and their values.

INSERT INTO customers (first_name, last_name, email, created_at)
VALUES ('Sarah', 'Chen', 'sarah.chen@example.com', NOW());

Always list the column names explicitly. Relying on column order without naming them breaks when the table schema changes.

Insert multiple rows

Instead of running separate INSERT statements, you can add multiple rows in a single query. This is significantly faster because it reduces the number of round trips between your application and the database.

INSERT INTO customers (first_name, last_name, email, created_at)
VALUES
    ('Sarah', 'Chen', 'sarah.chen@example.com', NOW()),
    ('Marcus', 'Johnson', 'marcus.j@example.com', NOW()),
    ('Priya', 'Patel', 'priya.patel@example.com', NOW()),
    ('James', 'Wilson', 'james.w@example.com', NOW());

Most databases support inserting hundreds or thousands of rows in a single statement. If you are loading large datasets, batch your inserts into groups of 500 to 1,000 rows for the best balance of speed and memory usage.

Insert from a SELECT query

You can populate a table using data from another table or query. This is useful for creating backups, migrating data, or building summary tables.

-- Copy active users into a new table
INSERT INTO active_customers (customer_id, email, last_order_date)
SELECT c.id, c.email, MAX(o.order_date)
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.id, c.email;

INSERT with RETURNING (PostgreSQL)

PostgreSQL lets you return the inserted row, which is useful for getting auto-generated IDs without a second query.

INSERT INTO customers (first_name, last_name, email)
VALUES ('Sarah', 'Chen', 'sarah.chen@example.com')
RETURNING id, created_at;

READ: Querying Data with SELECT

SELECT is the most frequently used SQL statement. It is the "R" in CRUD and covers everything from simple lookups to complex analytical queries.

Basic SELECT

-- Select specific columns
SELECT first_name, last_name, email
FROM customers;

-- Select all columns (avoid in production code)
SELECT * FROM customers;

-- Select with aliases
SELECT
    first_name AS name,
    email AS contact_email,
    created_at AS signup_date
FROM customers;

Filtering with WHERE

The WHERE clause limits which rows are returned. You can combine conditions with AND and OR.

-- Single condition
SELECT * FROM orders
WHERE status = 'shipped';

-- Multiple conditions
SELECT * FROM orders
WHERE status = 'shipped'
  AND total_amount > 100
  AND order_date >= '2026-01-01';

-- Using OR with parentheses
SELECT * FROM customers
WHERE (country = 'US' OR country = 'CA')
  AND is_active = true;

-- Using IN for multiple values
SELECT * FROM products
WHERE category IN ('electronics', 'computers', 'accessories');

-- Pattern matching with LIKE
SELECT * FROM customers
WHERE email LIKE '%@gmail.com';

-- Range filtering with BETWEEN
SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

Sorting with ORDER BY

-- Sort by a single column (descending)
SELECT product_name, price
FROM products
ORDER BY price DESC;

-- Sort by multiple columns
SELECT first_name, last_name, created_at
FROM customers
ORDER BY last_name ASC, first_name ASC;

-- Limit results (pagination)
SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

Joining tables

Real applications store data across multiple tables. JOIN lets you combine them in a single query.

-- INNER JOIN: only rows that match in both tables
SELECT
    o.id AS order_id,
    o.order_date,
    c.first_name,
    c.email,
    o.total_amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.order_date DESC;

-- LEFT JOIN: all customers, even those with no orders
SELECT
    c.first_name,
    c.last_name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.first_name, c.last_name
ORDER BY total_spent DESC;

Aggregation with GROUP BY

-- Revenue by month
SELECT
    DATE_TRUNC('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS avg_order_value
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY DATE_TRUNC('month', order_date)
HAVING SUM(total_amount) > 1000
ORDER BY month;

UPDATE: Modifying Existing Data

The UPDATE statement changes the values of existing rows. It is the "U" in CRUD and requires careful handling because a mistake can overwrite data across your entire table.

Update a single column

UPDATE customers
SET email = 'sarah.new@example.com'
WHERE id = 142;

Always include a WHERE clause. Running UPDATE without WHERE modifies every row in the table.

Update multiple columns

UPDATE customers
SET
    email = 'sarah.new@example.com',
    last_name = 'Chen-Williams',
    updated_at = NOW()
WHERE id = 142;

Conditional updates with CASE

Use CASE to apply different updates based on conditions within a single statement.

-- Apply tiered discounts based on order count
UPDATE customers
SET discount_tier = CASE
    WHEN lifetime_orders >= 100 THEN 'platinum'
    WHEN lifetime_orders >= 50  THEN 'gold'
    WHEN lifetime_orders >= 10  THEN 'silver'
    ELSE 'bronze'
END,
updated_at = NOW();

Update with a JOIN

Sometimes you need data from another table to determine the new values. The syntax varies by database.

-- PostgreSQL: UPDATE with FROM
UPDATE orders o
SET status = 'vip_order'
FROM customers c
WHERE o.customer_id = c.id
  AND c.discount_tier = 'platinum';

-- MySQL: UPDATE with JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'vip_order'
WHERE c.discount_tier = 'platinum';

-- SQL Server: UPDATE with FROM and JOIN
UPDATE o
SET o.status = 'vip_order'
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.discount_tier = 'platinum';

Update with a subquery

-- Set each product's price to the category average
UPDATE products
SET price = (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = products.category
)
WHERE price IS NULL;

DELETE: Removing Data

The DELETE statement removes rows from a table. It is the "D" in CRUD and the most dangerous operation because deleted data cannot be recovered without a backup.

Delete specific rows

-- Delete a single row by ID
DELETE FROM customers
WHERE id = 142;

-- Delete rows matching a condition
DELETE FROM orders
WHERE status = 'cancelled'
  AND order_date < '2025-01-01';

Delete with a subquery

-- Delete orders from inactive customers
DELETE FROM orders
WHERE customer_id IN (
    SELECT id FROM customers
    WHERE is_active = false
    AND last_login < '2025-01-01'
);

Delete with JOIN (MySQL)

-- MySQL supports DELETE with JOIN directly
DELETE o
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE c.is_active = false;

TRUNCATE vs DELETE

Both remove rows, but they work differently under the hood.

• DELETE FROM table - Removes rows one at a time. Supports WHERE clause. Fires triggers. Can be rolled back in a transaction. Slower on large tables.
• TRUNCATE TABLE table - Removes all rows at once by deallocating data pages. Cannot use WHERE. Does not fire row-level triggers. Resets auto-increment counters. Much faster on large tables.

-- Delete all rows (slow, logged, can rollback)
DELETE FROM temp_import_data;

-- Truncate all rows (fast, minimal logging)
TRUNCATE TABLE temp_import_data;

Use DELETE when you need to remove specific rows or need trigger support. Use TRUNCATE when you want to empty an entire table quickly, such as clearing staging tables between data loads.

CRUD Across Different Databases

The core CRUD syntax is standardized across databases, but each engine adds its own extensions and quirks. Here are the most common differences you will encounter.

UPSERT (Insert or Update)

One of the most requested features: insert a row if it does not exist, or update it if it does.

-- PostgreSQL: INSERT ON CONFLICT
INSERT INTO customers (email, first_name, last_name)
VALUES ('sarah@example.com', 'Sarah', 'Chen')
ON CONFLICT (email)
DO UPDATE SET
    first_name = EXCLUDED.first_name,
    last_name = EXCLUDED.last_name,
    updated_at = NOW();

-- MySQL: INSERT ON DUPLICATE KEY UPDATE
INSERT INTO customers (email, first_name, last_name)
VALUES ('sarah@example.com', 'Sarah', 'Chen')
ON DUPLICATE KEY UPDATE
    first_name = VALUES(first_name),
    last_name = VALUES(last_name),
    updated_at = NOW();

-- SQL Server: MERGE
MERGE INTO customers AS target
USING (VALUES ('sarah@example.com', 'Sarah', 'Chen'))
    AS source (email, first_name, last_name)
ON target.email = source.email
WHEN MATCHED THEN
    UPDATE SET first_name = source.first_name,
               last_name = source.last_name
WHEN NOT MATCHED THEN
    INSERT (email, first_name, last_name)
    VALUES (source.email, source.first_name, source.last_name);

Row limiting

-- PostgreSQL / MySQL: LIMIT and OFFSET
SELECT * FROM products ORDER BY price DESC LIMIT 10 OFFSET 20;

-- SQL Server: TOP
SELECT TOP 10 * FROM products ORDER BY price DESC;

-- SQL Server (with offset): OFFSET FETCH
SELECT * FROM products
ORDER BY price DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

Auto-increment columns

-- PostgreSQL: SERIAL or GENERATED ALWAYS AS IDENTITY
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

-- MySQL: AUTO_INCREMENT
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

-- SQL Server: IDENTITY
CREATE TABLE customers (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(100)
);

Getting the last inserted ID

-- PostgreSQL: RETURNING clause
INSERT INTO customers (name) VALUES ('Sarah') RETURNING id;

-- MySQL: LAST_INSERT_ID()
INSERT INTO customers (name) VALUES ('Sarah');
SELECT LAST_INSERT_ID();

-- SQL Server: SCOPE_IDENTITY()
INSERT INTO customers (name) VALUES ('Sarah');
SELECT SCOPE_IDENTITY();

Safe Practices for CRUD Operations

A single UPDATE or DELETE without a WHERE clause can wipe out an entire table in seconds. These practices protect you from costly mistakes.

1. Always preview before modifying

Before running an UPDATE or DELETE, run a SELECT with the same WHERE condition to see which rows will be affected.

-- Step 1: Preview what will be deleted
SELECT id, email, last_login
FROM customers
WHERE is_active = false
  AND last_login < '2025-01-01';
-- Verify the result set looks correct

-- Step 2: Run the actual delete
DELETE FROM customers
WHERE is_active = false
  AND last_login < '2025-01-01';

2. Use transactions

Wrap destructive operations in a transaction so you can roll back if something goes wrong.

BEGIN;

UPDATE products
SET price = price * 1.15
WHERE category = 'electronics';

-- Check the results
SELECT product_name, price
FROM products
WHERE category = 'electronics';

-- If everything looks good:
COMMIT;

-- If something is wrong:
-- ROLLBACK;

3. Add LIMIT to DELETE (MySQL)

MySQL supports LIMIT on DELETE statements, which prevents accidentally deleting more rows than intended.

-- Delete at most 100 rows at a time
DELETE FROM log_entries
WHERE created_at < '2025-01-01'
LIMIT 100;

4. Back up before bulk operations

Before running large UPDATE or DELETE operations in production, create a backup of the affected data.

-- Create a backup table before bulk update
CREATE TABLE customers_backup_20260324 AS
SELECT * FROM customers
WHERE discount_tier IS NOT NULL;

-- Now safe to run the bulk update
UPDATE customers
SET discount_tier = 'bronze'
WHERE discount_tier IS NULL;

5. Use WHERE clause checks in your application

At the application level, add safeguards that reject UPDATE or DELETE statements without a WHERE clause. Many ORMs and query builders provide this protection by default. If you are writing raw SQL, consider using a linter or code review checklist that flags unfiltered modifications.

6. Enable query logging

Turn on slow query logging and general query logging during critical operations. If something goes wrong, you can trace exactly what was executed and when.

Practice CRUD safely - AI2SQL lets you describe operations in plain English and generates correct SQL with proper WHERE clauses, so you can avoid destructive mistakes.

Frequently Asked Questions

What does CRUD stand for in SQL?

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations for managing data in any SQL database. In SQL, they map to INSERT (Create), SELECT (Read), UPDATE (Update), and DELETE (Delete) statements.

What is the difference between DELETE and TRUNCATE in SQL?

DELETE removes rows one at a time, can include a WHERE clause to target specific rows, fires triggers, and can be rolled back inside a transaction. TRUNCATE removes all rows at once, cannot use a WHERE clause, is faster on large tables, resets auto-increment counters in most databases, and in some databases cannot be rolled back.

Can I INSERT multiple rows in a single SQL statement?

Yes. Most modern databases support multi-row INSERT syntax: INSERT INTO table (col1, col2) VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'). This is significantly faster than running separate INSERT statements for each row because it reduces round trips to the database server.

How do I safely run UPDATE or DELETE without affecting wrong rows?

Always include a WHERE clause. Before running UPDATE or DELETE, run a SELECT with the same WHERE condition to verify which rows will be affected. Wrap destructive operations in a transaction (BEGIN/COMMIT) so you can ROLLBACK if something goes wrong. In production, take a backup before running bulk updates or deletes.

Are CRUD operations the same across MySQL, PostgreSQL, and SQL Server?

The core CRUD syntax (INSERT, SELECT, UPDATE, DELETE) is nearly identical across MySQL, PostgreSQL, and SQL Server. Differences appear in features like UPSERT syntax (INSERT ON DUPLICATE KEY UPDATE in MySQL vs INSERT ON CONFLICT in PostgreSQL vs MERGE in SQL Server), LIMIT vs TOP for row limiting, and auto-increment column definitions.