Mastering SQL: Joins, Normal Forms, Indexes, and Optimization Techniques

1. SQL Join Types

SQL joins are divided by result set into inner join, outer join, and cross join.

Inner Join: inner join on returns rows where both tables satisfy the condition. It includes equi‑join, non‑equi‑join, and natural join.

Equi‑join: rows with equal values in the joined columns appear in the result.

Natural Join: columns with the same name are merged into a single column.

Outer Join: includes left outer join, right outer join, and full outer join.

Left Outer Join: all rows from the left table are kept; matching rows from the right table are added, non‑matching rows contain NULL.

Right Outer Join: analogous with the right table as the base.

Full Outer Join: combines all rows from both tables, filling missing matches with NULL.

Cross Join: produces the Cartesian product of the two tables.

2. Three Normal Forms

1NF: each field contains atomic, indivisible values.

2NF: in addition to 1NF, every non‑key attribute must be fully dependent on the whole candidate key, not just part of it.

3NF: in addition to 2NF, non‑key attributes must not depend on other non‑key attributes (no transitive dependencies).

3. Table Operations

Create Table:

create table 表名 (列名1 类型 约束, 列名2 类型 约束, ...)

Drop Table: drop table 表名 Alter Table (structure): alter table 表名 add|drop 列名|约束名 Insert Record: insert into 表名 … values … Update Record: update 表名 set 列名=值 where 条件 Delete Record:

delete from 表名 where 条件

4. Data Integrity

Data integrity ensures consistency and accuracy of stored data.

Types of integrity:

Entity integrity – primary key must be unique and not null.

Referential integrity – foreign key must be null or reference an existing primary key.

User‑defined integrity – custom constraints defined for specific business rules.

5. SQL Query Optimization

Prefer inner joins over outer joins because they process only matching rows.

Use stored procedures instead of ad‑hoc SQL to reduce compilation overhead.

Create indexes on frequently queried columns to enable index scans.

Select only required columns instead of select *.

Place the most selective where conditions early in the statement.

6. Indexes: Clustered vs Non‑Clustered

An index is a database object that allows the engine to locate rows without scanning the whole table; it is typically implemented with a B‑tree.

Clustered Index: stores the table rows physically in the order of the index key.

Non‑Clustered Index: maintains a separate structure that contains the key values and pointers to the actual rows.

Differences:

Clustered indexes sort the data physically; non‑clustered indexes do not.

Insert and update operations are slower on clustered indexes, while single‑row queries are faster.

Leaf nodes of a clustered index contain the data rows; leaf nodes of a non‑clustered index contain pointers to the data rows.

Each table can have only one clustered index but multiple non‑clustered indexes.

7. Stored Procedures vs Functions

Functions return a value; stored procedures do not.

Because stored procedures have no return value, their result cannot be assigned to a variable, whereas functions can be used in expressions and SELECT statements.