Understanding Various SQL Join Types and Their Differences

Multiple‑table queries frequently rely on joins, and it is important to understand the distinctions among the various join types.

Overview of Join Relationships

Joins can be expressed either as join predicates in the WHERE clause using comparison operators or by using the JOIN keyword, which T‑SQL extends to three main categories: inner join, outer join, and cross join (Cartesian product).

Detailed Analysis of Join Types

1. Equi‑join (Equality Join)

SELECT XSB.*, CJB.*
FROM XSB, CJB
WHERE XSB.学号 = CJB.学号

An equi‑join uses the "=" operator to connect tables, returning all columns from both tables, including duplicate column names.

2. Natural Join

The most commonly used join in database applications; it removes duplicate column names in the result.

SELECT XSB.*, CJB.课程号, CJB.成绩
FROM XSB, CJB
WHERE XSB.学号 = CJB.学号

Natural join requires the two tables to share common attributes; after performing an equality join on those attributes, duplicate columns are eliminated.

Differences between Equi‑join and Natural Join

1) Equi‑join does not require the attribute names to be identical, while natural join does.

2) Equi‑join retains duplicate columns; natural join removes them, effectively being a duplicate‑column‑free equi‑join.

3. Inner Join

An inner join (specified with the INNER keyword or omitted) merges rows that satisfy the ON condition and can be combined with additional WHERE filters.

SELECT *
FROM XSB INNER JOIN CJB ON XSB.学号 = CJB.学号

SELECT 姓名, 成绩
FROM XSB JOIN CJB ON XSB.学号 = CJB.学号
WHERE 课程号 = '206' AND 成绩 >= 80

4. Self Join

A self‑join treats a table as two separate instances, allowing comparison of rows within the same table. Aliases are required for each instance.

SELECT a.学号, a.课程号, b.课程号, a.成绩
FROM CJB a JOIN CJB b
ON a.成绩 = b.成绩 AND a.学号 = b.学号 AND a.课程号 != b.课程号

5. Outer Joins (Left, Right, Full)

Outer joins (specified with the OUTER keyword, which can be omitted) return all rows that satisfy the join condition plus all rows from the designated side(s) that have no match.

Left Outer Join (LEFT OUTER JOIN) : includes all rows from the left table.

SELECT XSB.*, 课程号
FROM XSB LEFT OUTER JOIN CJB ON XSB.学号 = CJB.学号

If a student has not selected any course, the 课程号 column will be NULL .

Right Outer Join (RIGHT OUTER JOIN) : includes all rows from the right table.

Full Outer Join (FULL OUTER JOIN) : includes all rows from both tables.

6. Cross Join (Cartesian Product)

A cross join produces the Cartesian product of two tables, pairing each row of the first table with every row of the second.

SELECT * FROM 表1 JOIN 表2;

Because the result size equals the product of the two tables' row counts, cross joins on large tables can consume excessive memory. Therefore, when joining large tables, it is advisable to use the FROM table1 JOIN table2 ON ... syntax rather than the comma‑separated FROM table1, table2 WHERE ... form, which first creates a large Cartesian product in memory.

Understanding these join types helps write more efficient SQL queries and avoid unnecessary memory overhead.