SQL Basics Part 3: INNER JOIN, LEFT JOIN, RIGHT JOIN, UPDATE, and DELETE

In Part 1 of this series, I covered creating tables and connecting them with primary and foreign keys. In Part 2, I practiced querying a single table using SELECT, WHERE, GROUP BY, and HAVING.

Part 3 is where relational databases finally started feeling relational to me. Before learning joins, tables in a database just felt like separate spreadsheets sitting next to each other. Joins are how you actually stitch those tables together at query time. Alongside INNER JOIN, LEFT JOIN, RIGHT JOIN, and how to simulate a FULL JOIN in MySQL using UNION, this post also covers ALTER TABLE, UPDATE, and DELETE.

Setting Up Two Simple Tables

To see how different joins behave, it helps to set up two small tables that intentionally don’t line up 100%, because real-world data almost never does.

First, here is an orders table with three orders:

CREATE TABLE orders(
    o_id INT,
    cust_id INT,
    price INT
);

INSERT INTO orders
VALUES
(1, 101, 1000),
(2, 201, 1100),
(3, 501, 1200);

And here is a customers table with three customers:

CREATE TABLE customers(
    id INT,
    name VARCHAR(100),
    email VARCHAR(100)
);

INSERT INTO customers
VALUES
(101, 'love', 'aa'),
(201, 'ansh', 'bb'),
(301, 'lamba', 'cc');

Notice the deliberate mismatch between the two tables:

• orders has a row with cust_id = 501, but there is no customer 501 in the customers table.
• customers has a row with id = 301 (lamba), but that customer has never placed an order in orders.

That small mismatch makes it immediately obvious what each type of join keeps and what it drops.

INNER JOIN: Only the Matches

An INNER JOIN returns the intersection of two tables. A row only appears in the result if the joining column matches in both tables.

SELECT * 
FROM orders o
INNER JOIN customers c
    ON o.cust_id = c.id;

Here, o and c are table aliases for orders and customers so we can write o.cust_id = c.id instead of spelling out the full table names every time.

With our data, this query returns 2 rows:

o_id	cust_id	price	id	name	email
1	101	1000	101	love	aa
2	201	1100	201	ansh	bb

• Order 1 (cust_id = 101) matches customer 101 (love).
• Order 2 (cust_id = 201) matches customer 201 (ansh).
• Order 3 (cust_id = 501) is dropped completely because no customer with id = 501 exists.
• Customer 301 (lamba) is also dropped because they have no matching row in orders.

INNER JOIN is strict: if there is no match on both sides, the row does not show up at all.

LEFT JOIN: Everything From the Left Table

A LEFT JOIN keeps every row from the left table (orders, since it appears first after FROM), whether or not it finds a matching row in the right table (customers).

SELECT * 
FROM orders o
LEFT JOIN customers c
    ON o.cust_id = c.id;

This returns 3 rows, preserving every order in orders:

o_id	cust_id	price	id	name	email
1	101	1000	101	love	aa
2	201	1100	201	ansh	bb
3	501	1200	NULL	NULL	NULL

Orders 1 and 2 pull in their matching customer details as usual. Order 3 (cust_id = 501) has no matching customer, but instead of dropping the order, SQL keeps the row from orders and fills the missing customers columns (id, name, email) with NULL.

RIGHT JOIN: Everything From the Right Table

A RIGHT JOIN is the mirror image of a LEFT JOIN. It keeps every row from the right table (customers), whether or not a matching row exists in the left table (orders).

SELECT * 
FROM orders o
RIGHT JOIN customers c
    ON o.cust_id = c.id;

This also returns 3 rows, but now anchored to customers:

o_id	cust_id	price	id	name	email
1	101	1000	101	love	aa
2	201	1100	201	ansh	bb
NULL	NULL	NULL	301	lamba	cc

Customers 101 and 201 match orders 1 and 2. Customer 301 (lamba) never placed an order, so the order columns (o_id, cust_id, price) come back as NULL while the customer record stays intact.

How to Do a FULL JOIN in MySQL Using UNION

One thing that surprised me when practicing in MySQL is that MySQL does not have a built-in FULL OUTER JOIN (or FULL JOIN) keyword, even though databases like PostgreSQL and SQL Server support it directly.

A full join is supposed to return everything from both tables at once: all matched rows, all unmatched rows from the left table, and all unmatched rows from the right table.

In MySQL, the standard way to get a FULL JOIN is to combine a LEFT JOIN and a RIGHT JOIN with UNION:

SELECT * 
FROM orders o
LEFT JOIN customers c
    ON o.cust_id = c.id

UNION

SELECT * 
FROM orders o
RIGHT JOIN customers c
    ON o.cust_id = c.id;

Here is how UNION makes this work:

1. The LEFT JOIN query returns orders 1, 2, and 3 (with NULL customer columns on order 3).
2. The RIGHT JOIN query returns customers 101, 201, and 301 (with NULL order columns on customer 301).
3. Because both SELECT * queries list orders first and customers second, both result sets have the exact same columns in the exact same order. UNION stacks the two results together and automatically removes duplicate rows (unlike UNION ALL, which keeps duplicates).

That gives us 4 rows total:

o_id	cust_id	price	id	name	email
1	101	1000	101	love	aa
2	201	1100	201	ansh	bb
3	501	1200	NULL	NULL	NULL
NULL	NULL	NULL	301	lamba	cc

The two matched rows (101 and 201) appeared in both the LEFT JOIN and RIGHT JOIN, and UNION deduplicated them so each appears once alongside the unmatched order (501) and the unmatched customer (301).

Adding a Primary Key Later with ALTER TABLE

While working with these two tables, I realized I hadn’t declared id as a primary key when I first created customers. Instead of dropping and recreating the table, you can add a primary key to an existing table with ALTER TABLE:

ALTER TABLE customers
ADD PRIMARY KEY(id);

As long as the existing values in id are unique and contain no NULL values, MySQL promotes the column to a PRIMARY KEY in place. It’s a nice reminder that schemas don’t have to be frozen on day one; ALTER TABLE lets you tighten constraints as your schema evolves.

UPDATE: Modifying Existing Rows Safely

While INSERT adds brand-new rows, UPDATE modifies values inside rows that already exist in a table.

UPDATE customers
SET name = 'Niraj'
WHERE id = 101;

This updates the name column for customer 101 from 'love' to 'Niraj'. We can verify the change right away:

SELECT * FROM customers;

The single most important clause in any UPDATE statement is WHERE. If you run:

UPDATE customers
SET name = 'Niraj';

without a WHERE clause, SQL will happily overwrite name = 'Niraj' across every single row in the customers table. MySQL Workbench actually enables a “safe updates” mode by default to block UPDATE statements that lack a WHERE clause on a key column, specifically because forgetting WHERE on an UPDATE is one of the most common (and painful) mistakes in SQL.

DELETE: Removing Rows Without Wiping the Table

DELETE follows the exact same pattern as UPDATE, except instead of changing column values, it removes entire rows from the table:

DELETE FROM customers
WHERE id = 101;

This permanently removes the row where id = 101. Just like with UPDATE, omitting the WHERE clause is dangerous:

DELETE FROM customers;

Running DELETE FROM customers; without a WHERE clause deletes every row in the table, leaving you with an empty customers table and no confirmation prompt.

One habit I’ve picked up while practicing SQL is to always write and run the SELECT version of a query first:

-- Step 1: Preview the exact rows that match the condition
SELECT * FROM customers
WHERE id = 101;

-- Step 2: Once confirmed, swap SELECT * for DELETE (or UPDATE)
DELETE FROM customers
WHERE id = 101;

Testing your WHERE condition with SELECT * first takes five seconds and guarantees you know which rows you are about to modify or delete.

Coming Up in Part 4

Now that we can filter, group, and join tables together, the next step is asking multi-step questions where one query depends on the result of another. In Part 4, I’ll get into subqueries and window functions (including how to solve the “top N per group” problem that LIMIT alone couldn’t handle in Part 2).