Sample Tables

Table X

CREATE TABLE X (
    user_id INT,
    username VARCHAR(20),
    profile VARCHAR(20)
);

INSERT INTO X VALUES(1, 'Alice', 'Dev');
INSERT INTO X VALUES(2, 'Bob', 'QA');

Table Y

CREATE TABLE Y (
    user_id INT,
    username VARCHAR(20),
    score INT
);

INSERT INTO Y VALUES(1, 'Alice', 85);
INSERT INTO Y VALUES(3, 'Bob', 92);

---

Join Operations

Natural Join

Matches rows based on columns with identical names and values. The result contains only one instance of each matching column.

SELECT * FROM X NATURAL JOIN Y;

Result:

+--------+----------+----------+-------+------+
| user_id | username | profile  | score |      |
+--------+----------+----------+-------+      |
|       1 | Alice    | Dev      |    85 |      |
+--------+----------+----------+-------+      |

Join with ON Clause

Performs a Cartesian product filtered by the specified condition. Duplicate columns remain in the result.

SELECT * FROM X JOIN Y ON X.username = Y.username;

Result:

+--------+----------+----------+--------+----------+-------+
| user_id | username | profile  | user_id | username | score |
+--------+----------+----------+--------+----------+-------+
|       1 | Alice    | Dev      |       1 | Alice   |    85 |
|       2 | Bob      | QA       |       3 | Bob     |    92 |
+--------+----------+----------+--------+----------+-------+

Left Natural Outer Join

All rows from the left table appear in the result. Matching rows from the right table are included; non-matching rows have NULL values for right table columns.

SELECT * FROM X NATURAL LEFT OUTER JOIN Y;

Result:

+--------+----------+----------+-------+
| user_id | username | profile  | score |
+--------+----------+----------+-------+
|       1 | Alice    | Dev      |    85 |
|       2 | Bob      | QA       |  NULL |
+--------+----------+----------+-------+

Left Outer Join with ON

Preserves all left table rows while matching on specific conditions. Both tables' columns remain in the output.

SELECT * FROM X LEFT OUTER JOIN Y ON X.user_id = Y.user_id;

Result:

+--------+----------+----------+--------+----------+-------+
| user_id | username | profile  | user_id | username | score |
+--------+----------+----------+--------+----------+-------+
|       1 | Alice    | Dev      |       1 | Alice   |    85 |
|       2 | Bob      | QA       |    NULL | NULL    |  NULL |
+--------+----------+----------+--------+----------+-------+

Inner Join

Returns only rows where the join condition is satisfied in both tables.

SELECT * FROM X INNER JOIN Y ON X.user_id = Y.user_id;

Result:

+--------+----------+----------+--------+----------+-------+
| user_id | username | profile  | user_id | username | score |
+--------+----------+----------+--------+----------+-------+
|       1 | Alice    | Dev      |       1 | Alice   |    85 |
+--------+----------+----------+--------+----------+-------+

---

Views

Views are virtual tables derived from query results. They simplify complex queries and provide abstraction over underlying tables.

Creating a View

CREATE VIEW V AS 
SELECT * FROM X NATURAL LEFT OUTER JOIN Y;

Querying a View

SELECT * FROM V;

Result:

+--------+----------+----------+-------+
| user_id | username | profile  | score |
+--------+----------+----------+-------+
|       1 | Alice    | Dev      |    85 |
|       2 | Bob      | QA       |  NULL |
+--------+----------+----------+-------+

Explicit Column Names in Views

You can alias column names during view creation:

CREATE VIEW W(user_id, alias_name) AS 
SELECT user_id, username 
FROM X NATURAL LEFT OUTER JOIN Y;

Query result:

+--------+-------------+
| user_id | alias_name  |
+--------+-------------+
|       1 | Alice       |
|       2 | Bob         |
+--------+-------------+

---

Transactions

Transactions ensure data integrity by grouping operations that either all succeed or all fail together.

Commit Mode Configuration

SET autocommit = 0;  -- Manual commit mode
SET autocommit = 1;  -- Auto-commit mode (each statement commits immediately)

Transaction with Rollback

SET autocommit = 0;
START TRANSACTION;

INSERT INTO X VALUES(3, 'Charlie', 'PM');

SELECT * FROM X;
-- Row with ID=3 appears

ROLLBACK;

SELECT * FROM X;
-- Row with ID=3 is removed

Transaction with Commit

START TRANSACTION;

INSERT INTO X VALUES(3, 'Charlie', 'PM');

SELECT * FROM X;
-- Row with ID=3 appears

COMMIT WORK;

ROLLBACK;  -- Has no effect after commit

SELECT * FROM X;
-- Row with ID=3 still exists

---

Integrity Constraints

Unique Constraint

ALTER TABLE X MODIFY user_id INT UNIQUE;

Custom Constraints via Triggers

MySQL does not support CHECK constraints directly. Use triggers to enforce domain rules:

CREATE TRIGGER EnforceIdLimit 
BEFORE INSERT ON X
FOR EACH ROW
BEGIN
    IF NEW.user_id > 100 THEN
        SET NEW.user_id = 0;
    END IF;
END;

Testing the trigger:

INSERT INTO X VALUES(150, 'Test', 'Test');

SELECT * FROM X;
-- New row has user_id = 0 instead of 150

---

Indexes

Indexes improve query performance by creating data structures that allow faster lookups.

Creating an Index

CREATE INDEX idx_user_id ON X(user_id);

SELECT * FROM X WHERE user_id = 1;
-- Query now executes faster on indexed column

Indexes are particularly beneficial for columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY operations.