深入理解SQL中的INNER JOIN操作

-- 创建示例表
CREATE TABLE Employees (
    EmployeeID int NOT NULL,
    Name varchar(255) NOT NULL,
    DepartmentID int,
    PRIMARY KEY (EmployeeID)
);
 
CREATE TABLE Departments (
    DepartmentID int NOT NULL,
    Name varchar(255) NOT NULL,
    PRIMARY KEY (DepartmentID)
);
 
-- 插入示例数据
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'Alice', 100);
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (2, 'Bob', 200);
INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (3, 'Charlie', NULL);
 
INSERT INTO Departments (DepartmentID, Name) VALUES (100, 'HR');
INSERT INTO Departments (DepartmentID, Name) VALUES (200, 'R&D');
 
-- 使用INNER JOIN查询Employees和Departments的交集
SELECT e.EmployeeID, e.Name, d.Name AS DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;
 
-- 结果将是Alice和Bob的记录，因为他们有对应的DepartmentID
-- 结束后，可以删除示例表
DROP TABLE Employees;
DROP TABLE Departments;

这段代码首先创建了两个简单的表Employees和Departments，然后插入了一些示例数据。接着使用INNER JOIN查询来显示所有在两个表中都有对应DepartmentID的Employees记录。最后，代码展示了如何删除创建的示例表。这个例子有助于理解INNER JOIN的工作原理和在实际数据库操作中的应用。