What is a Self-Join in SQL?

✅ A self-join is when:

A table is joined to itself.

• We treat the same table as if it were two different tables (using aliases).
• We match rows with other rows in the same table based on a condition.

It’s not a new type of JOIN — it’s just a regular JOIN where the table joins to itself.

---

Why Use a Self-Join?

• To compare rows within the same table.
• To find relationships between rows.
• To link hierarchical data (e.g., employees and their managers).

---

Real-World Example

Imagine a table Employees:

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Dave	2

• Each employee has a manager who is also an employee (manager_id points to employee_id).

You want to find each employee and their manager’s name.

---

Self-Join Example

SELECT 
    e.name AS employee_name,
    m.name AS manager_name
FROM 
    Employees e
LEFT JOIN Employees m ON e.manager_id = m.employee_id;

employee_name	manager_name
Alice	NULL
Bob	Alice
Charlie	Alice
Dave	Bob

✅ We use aliases e (for employee) and m (for manager) to distinguish the table used twice.

---

How It Works

• e is the current employee.
• m is the manager — but from the same table.
• Join on:

e.manager_id = m.employee_id

→ manager’s employee_id matches employee’s manager_id.

Self-Join Use Cases

Use Case	Example
Employees and managers	Employees table joins to itself.
Finding duplicates	Find rows with the same value in a table.
Finding related records	E.g., products with the same category, friends-of-friends in a social network.
Comparing rows	E.g., find rows where column A is greater than column B (in the same table).

---

Visual Diagram

Employees (alias e)         Employees (alias m)
+-------------+             +-------------+
| employee_id |             | employee_id |
| name        |             | name        |
| manager_id  |             | (other columns)
+-------------+             +-------------+

Join: e.manager_id = m.employee_id

In Short

Self-Join = Joining a table to itself using aliases, to find relationships inside the same table.

✅ Same table, treated as two different copies during the query.

It’s mostly about reading speed — but let’s break it down:

---

Why is CHAR Sometimes Faster Than VARCHAR?

1. Fixed Size = Simple Calculations

• CHAR(n) always stores exactly n characters.
• The database can easily calculate: “Where is the 10th record? Just jump n × 10 bytes.”

✅ Fast offset calculation — no need to scan row lengths.

2. No Extra Metadata

• VARCHAR(n) stores variable-length data.
• It needs extra storage for length information — 1 or 2 bytes per field.
• Every time you read, the database must: “First check how long the value is, then read it.”

✅ With CHAR, it just reads a fixed block — no lookup needed.

3. Row Alignment and Memory Access

• In memory (RAM), fixed-length rows are easier to align and read.
• CPUs love aligned data — it’s faster to load into cache.

✅ CHAR rows can be packed into aligned memory blocks — speeding up sequential reading.

So in short:

CHAR (Fixed)	VARCHAR (Variable)
Fixed size — faster memory access	Variable size — slower memory jumps
No need to check length	Need to check length each time
Simple math for row offsets	Complex row parsing
Slightly faster reads in large datasets	Slightly slower reads

---

Is It Also Faster for Writing?

❌ No — not faster for writing:

• In fact, CHAR wastes space — even if you only store 2 characters in CHAR(100), it still reserves space for all 100 characters.
• More disk I/O (because bigger rows).
• Longer writes for small values.

✅ VARCHAR is better for writing:

• It stores only the bytes needed.
• More storage efficient — smaller rows = faster disk writes.

---

So Final Answer:

Operation	Which is Faster?	Why?
Read (Sequential Reads)	CHAR (small advantage)	Fixed length, faster offsets, no length checking.
Write (Insert/Update)	VARCHAR	Smaller storage, less disk I/O, more efficient space use.

---

When Does This Matter?

• In very large tables (millions of rows).
• In high-performance analytics or data warehouses.
• When you need extremely predictable row size (like in OLTP banks, telecoms).

Otherwise — for most apps (web, mobile) — VARCHAR is better for storage efficiency and flexibility.

In Short

CHAR can be slightly faster for reading large datasets because of fixed length, but it wastes space and is not faster for writing.

Difference Between CHAR and VARCHAR in SQL

Feature	CHAR(n)	VARCHAR(n)
Meaning	Fixed-length string	Variable-length string
Storage	Always exactly n characters	Only stores the actual length used (up to n)
Padding	Pads with spaces if shorter than n	No padding — stores only the actual characters
Performance	Slightly faster for fixed-size values	More storage-efficient for variable sizes
Use Case	When all values are about the same length	When values vary widely in length
Example	CHAR(5) always stores 5 characters	VARCHAR(5) stores 1–5 characters

---

1. CHAR(n) — Fixed Length

• Always exactly n characters.
• If the input is shorter, it’s padded with spaces.
• Faster for fixed-length data because the database knows exactly how much space each value takes.

✅ Example:

CREATE TABLE ExampleChar (
    code CHAR(5)
);

Insert:

INSERT INTO ExampleChar (code) VALUES ('A1');

code
A1

Always takes 5 bytes (assuming 1 byte per character, like in ASCII).

2. VARCHAR(n) — Variable Length

• Stores only the characters you provide (up to n).
• No extra spaces.
• More storage efficient when lengths vary a lot.

✅ Example:

CREATE TABLE ExampleVarchar (
    code VARCHAR(5)
);

Insert:

INSERT INTO ExampleVarchar (code) VALUES ('A1');

code
A1

Takes only 2 bytes for A1, plus 1–2 bytes extra to store the length internally (depends on DB engine).

When Should You Use CHAR vs VARCHAR?

Use CHAR When	Use VARCHAR When
Data is always fixed length	Data is variable length
Example: country codes (US, FR)	Example: names, emails, descriptions
Speed is a tiny bit more important	Storage efficiency is important
Minor gain for huge tables	Better if length varies a lot

---

Real-World Example

Field	Type	Why
Country code	CHAR(2)	Always 2 characters — US, FR, JP.
User email	VARCHAR(255)	Varies a lot — from short to very long addresses.
Phone number	CHAR(10)	Always 10 digits (in some countries).
Address	VARCHAR(500)	Highly variable.

---

In Short

CHAR(n): Fixed-length string — always n characters, padded with spaces.
VARCHAR(n): Variable-length string — stores exactly what you insert, no padding.

You can use a simple UPDATE statement without a WHERE clause.

General Syntax

UPDATE table_name
SET column_name = default_value;

Without WHERE → it affects every row in the table.

With WHERE → it affects only rows matching the condition.

Example

Imagine you have a Users table:

user_id	name	status
1	Alice	NULL
2	Bob	NULL
3	Charlie	NULL

Now, you want to set the status column to 'Active' for all users.

✅ Here’s the SQL:

UPDATE Users
SET status = 'Active';

✅ Result:

user_id	name	status
1	Alice	Active
2	Bob	Active
3	Charlie	Active

---

Important Points

• Be careful: Without WHERE, it affects all rows — no filtering!
• You can set constants, functions, or default expressions.
  • E.g., set a timestamp:

UPDATE Orders SET order_date = CURRENT_DATE;

Bonus: Reset to Default Value (if defined)

If your column has a DEFAULT value defined in the table schema, you can:

• In PostgreSQL:

UPDATE Users
SET status = DEFAULT;

But in MySQL and SQL Server, DEFAULT in UPDATE is not supported — you have to explicitly set the default value manually.

In Short

To update all rows, just omit the WHERE clause:

✅ All rows will be updated.

✅ Aggregate functions are special SQL functions that:

Take multiple input values (from rows) and return a single value as a summary.

• They work on a set of rows — not just one row at a time.
• Used commonly with GROUP BY to summarize data.

Common Aggregate Functions

Function	What It Does
COUNT()	Counts the number of rows.
SUM()	Adds up values.
AVG()	Calculates the average (mean) value.
MIN()	Finds the minimum value.
MAX()	Finds the maximum value.

Quick Examples

Assume a table Sales:

sale_id	amount
1	100
2	150
3	200

1. COUNT()

Count how many sales there are:

SELECT COUNT(*) FROM Sales;

Result: 3

2. SUM()

Total sales amount:

SELECT SUM(amount) FROM Sales;

Result: 450 (100 + 150 + 200)

3. AVG()

Average sale amount:

SELECT AVG(amount) FROM Sales;

Result: 150.00

4. MIN()

Smallest sale:

SELECT MIN(amount) FROM Sales;

Result: 100

5. MAX()

Largest sale:

SELECT MAX(amount) FROM Sales;

Result: 200

---

Other Useful Aggregate Functions

Function	Description
COUNT(DISTINCT col)	Count unique values only.
GROUP_CONCAT(col) (MySQL)	Concatenates values into a string (e.g., "A,B,C")
STRING_AGG(col, ', ') (PostgreSQL, SQL Server)	Newer SQL standard for string concatenation.
VAR_SAMP() / STDDEV()	Variance / standard deviation for statistical summaries.

---

Where Are Aggregate Functions Commonly Used?

• GROUP BY summaries:

SELECT department, SUM(salary)
FROM Employees
GROUP BY department;

• Reports — totals, averages, counts.
• Data analytics — distributions, trends.

In Short

Aggregate functions summarize multiple rows into a single result — essential for reporting, analytics, and grouped summaries.

Perfect! Let’s do a real SQL example where we:

• Cause a Dirty Read at low isolation.
• Fix it by changing the isolation level.

Step-by-Step Dirty Read Example

Assume we have a simple Accounts table:

CREATE TABLE Accounts (
    account_id INT PRIMARY KEY,
    balance DECIMAL(10, 2)
);

INSERT INTO Accounts (account_id, balance) VALUES (1, 1000.00);

We have:

account_id	balance
1	1000.00

Now Simulate Two Transactions

Transaction A (updates balance, but does NOT commit yet):

-- Transaction A
START TRANSACTION;

UPDATE Accounts
SET balance = 0
WHERE account_id = 1;
-- DO NOT COMMIT YET!

Transaction B (tries to read the balance at the same time):

✅ In READ UNCOMMITTED (lowest isolation level):

-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

START TRANSACTION;

SELECT balance FROM Accounts WHERE account_id = 1;

🎯 Result:

• Transaction B sees balance = 0, even though A hasn’t committed!
• This is a dirty read — Transaction B sees uncommitted data!

Now Rollback Transaction A:

ROLLBACK;  -- Undo the update

✅ The balance is back to 1000.00 — but Transaction B already saw the wrong value 0!

🛡️ How to Fix It

Use a higher isolation level — for example, READ COMMITTED (default in most systems):

-- Transaction B
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

START TRANSACTION;

SELECT balance FROM Accounts WHERE account_id = 1;

✅ Now:

• Transaction B will wait until Transaction A commits or rollbacks.
• It won’t see uncommitted data.
• No dirty read happens.

Key Point

Isolation Level	Behavior
READ UNCOMMITTED	Sees uncommitted data → dirty reads happen.
READ COMMITTED (or higher)	Waits for commit → no dirty reads.

Full Flow Summary

1. A starts transaction and updates balance to 0.
2. B at READ UNCOMMITTED sees balance = 0 (dirty read!).
3. A rolls back — but B already saw wrong value.
4. 🚫 Data inconsistency — BAD.

✅ Solution: Use READ COMMITTED or higher.

Real-Life Analogy

Imagine seeing a restaurant’s bill while the waiter is still writing it — not finalized — and assuming it’s final.
Later, the waiter throws it away and makes a new bill — but you already made decisions based on the wrong bill!

What are Anomalies in Transactions?

When multiple transactions run at the same time (concurrency), if the database isn’t careful, weird problems (called anomalies) can happen.

👉 Anomalies are unwanted behaviors like:

• Reading data that isn’t finished.
• Seeing inconsistent results.
• Losing updates.

Main Anomalies (Problems)

Anomaly	What Happens
Dirty Read	Reading data from another transaction that hasn’t been committed yet.
Non-Repeatable Read	Reading the same row twice and getting different values because someone else changed it.
Phantom Read	New rows appear (or disappear) if you re-run the same query during a transaction.
Lost Update	Two transactions overwrite each other’s updates without knowing.

Let’s Explain Each One

---

1. Dirty Read 🧼📖

You read data that another transaction has changed but not yet committed.

✅ Example:

• Transaction A updates balance to $500 but hasn’t committed yet.
• Transaction B reads the balance — sees $500.
• Later, A rolls back — but B already saw incorrect data!

❌ Very dangerous — you see data that might not actually happen.

2. Non-Repeatable Read 🔄📖

You read the same row twice but get different results.

✅ Example:

• Transaction A reads account balance: $500.
• Meanwhile, Transaction B updates it to $600 and commits.
• Transaction A reads again: now it’s $600.

❌ Bad for consistency — you lose stability during your transaction.

3. Phantom Read 👻📖

New rows appear (or disappear) when you rerun a query during the same transaction.

✅ Example:

• Transaction A reads: SELECT * FROM Orders WHERE status = 'pending'; — 5 rows.
• Transaction B inserts a new “pending” order and commits.
• Transaction A reads again — now 6 rows.

❌ Bad for batch operations — data set keeps changing.

4. Lost Update 🔥📝

Two transactions update the same data — but the second one overwrites the first without knowing.

✅ Example:

• Transaction A reads balance $500, adds $50, plans to update $550.
• Transaction B reads the same $500, adds $100, plans to update $600.
• A writes $550, then B writes $600 — A’s update is lost.

❌ Real problem for concurrent updates — leads to data loss.

How to Fix These Anomalies? 🛡️

✅ We fix them by using:
Transaction Isolation Levels

Isolation Level = how much protection a transaction has from other transactions.

---

SQL Standard Isolation Levels

Isolation Level	Prevents	Allows
READ UNCOMMITTED (lowest)	Nothing	Dirty reads, non-repeatable reads, phantom reads
READ COMMITTED (default in many DBs)	Dirty reads	Non-repeatable reads, phantom reads
REPEATABLE READ	Dirty reads, non-repeatable reads	Phantom reads (depends on DB)
SERIALIZABLE (highest)	Dirty reads, non-repeatable reads, phantom reads	Nothing — highest isolation

Quick Summary

Level	Dirty Read	Non-Repeatable Read	Phantom Read
Read Uncommitted	❌ Not prevented	❌ Not prevented	❌ Not prevented
Read Committed	✅ Prevented	❌ Not prevented	❌ Not prevented
Repeatable Read	✅ Prevented	✅ Prevented	❌ (depends)
Serializable	✅ Prevented	✅ Prevented	✅ Prevented

Real-World Meaning

• Read Uncommitted — super fast, super dangerous. (Almost never use.)
• Read Committed — most common; safer for many apps (prevents dirty reads).
• Repeatable Read — safer for apps that read data multiple times.
• Serializable — safest, but slowest — like transactions run one after another.

---

Why Do We Need Isolation Levels?

• Performance vs Safety:
  High isolation (like Serializable) is safe but slower — more locking, blocking.
• Balance:
  Choose an isolation level depending on:
  • How important data consistency is.
  • How much concurrency (parallel transactions) you expect.

✅ For banks — Serializable (money is serious!).
✅ For social media — Read Committed (comments aren’t life-critical).

In Short

Anomalies = concurrency problems.

Isolation Levels = how much the database protects you from these problems.

---

Real-life Analogy

Dirty Read: Reading someone’s unfinished draft — they might throw it away!

Non-Repeatable Read: You look at a price tag, go back, and it changed.

Phantom Read: You count people in a room, but more people come in before you finish.

Lost Update: You and your friend edit the same Google Doc at the same time — overwriting each other.

What is a Transaction in SQL?

A transaction is a single unit of work that consists of one or more SQL statements, executed together as a group.

• The database treats a transaction as all or nothing:
  • All statements succeed ➔ transaction is committed (saved permanently).
  • Any statement fails ➔ transaction is rolled back (undone completely).

✅ Think of a transaction like a package deal — either the whole deal goes through, or none of it does.

Key Properties of a Transaction

(These are the ACID properties we talked about earlier.)

Property	Meaning
Atomicity	All or nothing — no partial changes.
Consistency	Database must move from one valid state to another.
Isolation	Transactions don’t interfere with each other.
Durability	Once committed, the changes are permanent.

Typical Transaction Commands

Command	What It Does
BEGIN or START TRANSACTION	Start a new transaction.
COMMIT	Save all changes made in the transaction.
ROLLBACK	Undo all changes if something goes wrong.

Example

Let’s say you’re transferring money between two accounts:

START TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;  -- Debit
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;  -- Credit

COMMIT;

✅ If both updates succeed: the transaction is committed — money moves.

❌ If anything fails (e.g., system crash in the middle): the transaction can ROLLBACK — no money is moved.

Why Use Transactions?

• To ensure data integrity.
• To group multiple steps into a single, safe operation.
• To recover safely if something fails.
• To avoid partial updates (e.g., one account debited but the other not credited).

Real-Life Analogy

Buying an item online:

• Deduct payment from your account.
• Create an order record.
• Reduce inventory.
• Confirm shipment.

✅ Either all of this happens, or none of it happens — no half-bought products!

In Short

A transaction is a set of operations that must all succeed together or fail together — ensuring the database stays reliable and consistent.

What are ACID Properties in Databases?

ACID stands for:

Letter	Property	Meaning
A	Atomicity	All or nothing — a transaction is fully done or fully undone.
C	Consistency	The database moves from one valid state to another.
I	Isolation	Transactions do not interfere with each other.
D	Durability	Once committed, the transaction’s changes are permanent.

Let’s Break Each One Down

1. Atomicity (A) — “All or Nothing”

A transaction must be fully completed or fully rolled back.

• If any part of the transaction fails, the whole transaction fails.
• The database won’t be left in a half-updated state.

✅ Example:

• You transfer money:
  • Debit from one account.
  • Credit to another.
• If the debit succeeds but the credit fails — rollback the debit — no partial transfer.

2. Consistency (C) — “Valid State”

A transaction must maintain database rules.

• After the transaction, the database must be in a consistent state.
• All constraints, triggers, foreign keys, business rules must be respected.

✅ Example:

• Balance cannot go negative (business rule).
• Foreign key constraints must not be violated.

If a transaction violates a rule — it fails and is rolled back.

3. Isolation (I) — “Transactions Don’t Bump Into Each Other”

Each transaction is independent of other concurrent transactions.

• Intermediate results are hidden from other transactions.
• No “dirty reads,” “phantom reads,” or “non-repeatable reads” — depending on isolation level.

✅ Example:

• Two users booking the last ticket simultaneously:
  • Only one can succeed.
  • They shouldn’t see inconsistent intermediate states.

Databases provide isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable.

4. Durability (D) — “It’s Permanent”

Once a transaction is committed, it is permanently recorded, even if:

• There is a crash.
• A power failure occurs.
• Changes are stored in non-volatile storage (disk, SSD).
• Databases often use Write-Ahead Logs (WALs) to ensure durability.

✅ Example:

• After you book a ticket and get confirmation, it’s safe — even if the server crashes the next second.

---

In Short

ACID	Guarantee
Atomicity	All steps succeed or none at all.
Consistency	Database rules are not violated.
Isolation	Transactions don’t interfere with each other.
Durability	Committed transactions survive failures/crashes.

---

Why is ACID Important?

• It ensures that databases are reliable, even with:
  • System crashes
  • Power failures
  • Multiple concurrent users
• Without ACID:
  • Money could be lost in transit.
  • Seats could be double-booked.
  • Inventory could become negative.

✅ ACID protects your data and makes databases trustworthy.

Real-Life Analogy

Bank Transaction:

• Atomicity: Transfer all money or none.
• Consistency: Balances must still add up correctly.
• Isolation: Your transfer doesn’t collide with someone else’s at the same time.
• Durability: Once you get confirmation, the transfer is safe forever.

1. Using LEFT JOIN ... WHERE NULL

This is the most classic and portable method.

Example

Find rows in TableA that don’t exist in TableB:

SELECT A.*
FROM TableA A
LEFT JOIN TableB B ON A.id = B.id
WHERE B.id IS NULL;

• LEFT JOIN returns all rows from TableA and matches from TableB.
• Where TableB.id is NULL, it means there was no match — the record exists only in TableA.

✅ Best for readability and widely supported.

2. Using NOT IN

Another way:

SELECT *
FROM TableA
WHERE id NOT IN (SELECT id FROM TableB);

• Select all ids from TableB.
• Return rows from TableA where id is NOT IN that list.

⚠️ Be careful: NOT IN behaves badly if there are NULL values inside the subquery — it might return no rows at all.

3. Using NOT EXISTS

This is often the best for performance, especially on large datasets:

SELECT *
FROM TableA A
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB B
    WHERE A.id = B.id
);

For each row in TableA, check if there’s a matching id in TableB.

If NOT EXISTS, it means A.id is not present in B.

✅ NOT EXISTS handles NULLs properly and is often faster for large tables.

Which Method is Best?

Method	Pros	Cons
LEFT JOIN ... IS NULL	Simple, readable	Can be slower on large tables if not indexed
NOT IN	Simple syntax	Can be broken by NULLs
NOT EXISTS	Reliable, handles NULLs, often faster	More verbose syntax

Real-World Example

Imagine:

• Customers table — all customers.
• Orders table — customers who have placed orders.

Find customers who never placed an order:

SELECT C.*
FROM Customers C
LEFT JOIN Orders O ON C.customer_id = O.customer_id
WHERE O.customer_id IS NULL;

or:

SELECT *
FROM Customers
WHERE NOT EXISTS (
    SELECT 1 FROM Orders
    WHERE Customers.customer_id = Orders.customer_id
);

In Short

To find records in one table but not in another, use LEFT JOIN … IS NULL, NOT IN, or NOT EXISTS — depending on what works best for your data and DBMS.