PostgreSQL  JOIN 联表查询实战演练(内连接 / 外连接 / 交叉连接)

select columns
from table1
join table2 on table1.column = table2.column
where conditions;

-- 创建部门表
create table departments (
    dept_id serial primary key,
    dept_name varchar(100) not null
);
-- 创建员工表
create table employees (
    emp_id serial primary key,
    emp_name varchar(100) not null,
    dept_id int, -- 外键关联到 departments 表
    salary decimal(10, 2),
    foreign key (dept_id) references departments(dept_id)
);
-- 插入部门数据
insert into departments (dept_name) values
('human resources'),
('engineering'),
('marketing'),
('finance');
-- 插入员工数据
insert into employees (emp_name, dept_id, salary) values
('alice johnson', 1, 75000.00),
('bob smith', 2, 85000.00),
('carol davis', 2, 90000.00),
('david wilson', 3, 65000.00),
('eve brown', 1, 70000.00),
('frank miller', 4, 80000.00),
('grace lee', null, 55000.00); -- grace 没有分配部门

select e.emp_name, e.salary, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id
order by e.emp_name;

select e.emp_name, e.salary, d.dept_name
from employees e
left join departments d on e.dept_id = d.dept_id
order by e.emp_name;

-- 创建项目表 (假设项目属于部门)
create table projects (
    project_id serial primary key,
    project_name varchar(100) not null,
    dept_id int, -- 外键
    budget decimal(12, 2)
);
-- 插入项目数据
insert into projects (project_name, dept_id, budget) values
('website redesign', 1, 50000.00),
('mobile app', 2, 100000.00),
('market research', 3, 25000.00),
('new office setup', 5, 75000.00); -- 部门id 5 在 departments 表中不存在
-- 查询项目及其所属部门 (使用 right join)
select p.project_name, p.budget, d.dept_name
from projects p
right join departments d on p.dept_id = d.dept_id
order by d.dept_name;

select e.emp_name, e.salary, d.dept_name
from employees e
full outer join departments d on e.dept_id = d.dept_id
order by e.emp_name, d.dept_name;

select e.emp_name, d.dept_name
from employees e
cross join departments d
order by e.emp_name, d.dept_name;

<dependency>
    <groupid>org.postgresql</groupid>
    <artifactid>postgresql</artifactid>
    <version>42.6.0</version> <!-- 请检查最新版本 -->
</dependency>

import java.sql.connection;
import java.sql.drivermanager;
import java.sql.sqlexception;
public class databaseconnection {
    private static final string url = "jdbc:postgresql://localhost:5432/your_database_name"; // 替换为你的数据库名
    private static final string user = "your_username"; // 替换为你的用户名
    private static final string password = "your_password"; // 替换为你的密码
    public static connection getconnection() throws sqlexception {
        return drivermanager.getconnection(url, user, password);
    }
}

import java.sql.*;
import java.util.arraylist;
import java.util.list;

// 用于存储查询结果的简单类
class employeewithdept {
    private string employeename;
    private double salary;
    private string departmentname;

    public employeewithdept(string employeename, double salary, string departmentname) {
        this.employeename = employeename;
        this.salary = salary;
        this.departmentname = departmentname;
    }

    // getters and setters
    public string getemployeename() { return employeename; }
    public void setemployeename(string employeename) { this.employeename = employeename; }
    public double getsalary() { return salary; }
    public void setsalary(double salary) { this.salary = salary; }
    public string getdepartmentname() { return departmentname; }
    public void setdepartmentname(string departmentname) { this.departmentname = departmentname; }

    @override
    public string tostring() {
        return "employeewithdept{" +
                "employeename='" + employeename + '\'' +
                ", salary=" + salary +
                ", departmentname='" + departmentname + '\'' +
                '}';
    }
}

public class employeereportservice {

    public list<employeewithdept> getemployeeswithdepartments() throws sqlexception {
        list<employeewithdept> results = new arraylist<>();
        string sql = """
            select e.emp_name, e.salary, d.dept_name
            from employees e
            inner join departments d on e.dept_id = d.dept_id
            order by e.emp_name
            """;

        try (connection conn = databaseconnection.getconnection();
             preparedstatement pstmt = conn.preparestatement(sql);
             resultset rs = pstmt.executequery()) {

            while (rs.next()) {
                string empname = rs.getstring("emp_name");
                double salary = rs.getdouble("salary");
                string deptname = rs.getstring("dept_name");
                results.add(new employeewithdept(empname, salary, deptname));
            }
        }
        return results;
    }

    public static void main(string[] args) {
        employeereportservice service = new employeereportservice();
        try {
            list<employeewithdept> employees = service.getemployeeswithdepartments();
            system.out.println("=== 员工及其部门 (inner join) ===");
            for (employeewithdept emp : employees) {
                system.out.println(emp);
            }
        } catch (sqlexception e) {
            e.printstacktrace(); // 在实际应用中，应该使用更健壮的日志记录
        }
    }
}

public class employeereportservice {
    // ... (前面的方法)
    public list<employeewithdept> getallemployeeswithdepartments() throws sqlexception {
        list<employeewithdept> results = new arraylist<>();
        string sql = """
            select e.emp_name, e.salary, d.dept_name
            from employees e
            left join departments d on e.dept_id = d.dept_id
            order by e.emp_name
            """;
        try (connection conn = databaseconnection.getconnection();
             preparedstatement pstmt = conn.preparestatement(sql);
             resultset rs = pstmt.executequery()) {
            while (rs.next()) {
                string empname = rs.getstring("emp_name");
                double salary = rs.getdouble("salary");
                string deptname = rs.getstring("dept_name");
                results.add(new employeewithdept(empname, salary, deptname));
            }
        }
        return results;
    }
    public static void main(string[] args) {
        employeereportservice service = new employeereportservice();
        try {
            list<employeewithdept> employees = service.getallemployeeswithdepartments();
            system.out.println("=== 所有员工及其部门 (left join) ===");
            for (employeewithdept emp : employees) {
                system.out.println(emp);
            }
        } catch (sqlexception e) {
            e.printstacktrace();
        }
    }
}

=== 所有员工及其部门 (left join) ===
employeewithdept{employeename='alice johnson', salary=75000.0, departmentname='human resources'}
employeewithdept{employeename='bob smith', salary=85000.0, departmentname='engineering'}
employeewithdept{employeename='carol davis', salary=90000.0, departmentname='engineering'}
employeewithdept{employeename='david wilson', salary=65000.0, departmentname='marketing'}
employeewithdept{employeename='eve brown', salary=70000.0, departmentname='human resources'}
employeewithdept{employeename='frank miller', salary=80000.0, departmentname='finance'}
employeewithdept{employeename='grace lee', salary=55000.0, departmentname='null'}

public class employeereportservice {
    // ... (前面的方法)
    public list<employeewithdept> getemployeesbydepartmentid(int deptid) throws sqlexception {
        list<employeewithdept> results = new arraylist<>();
        string sql = """
            select e.emp_name, e.salary, d.dept_name
            from employees e
            inner join departments d on e.dept_id = d.dept_id
            where d.dept_id = ?
            order by e.emp_name
            """;
        try (connection conn = databaseconnection.getconnection();
             preparedstatement pstmt = conn.preparestatement(sql)) {
            pstmt.setint(1, deptid); // 设置参数 ? 为传入的部门 id
            try (resultset rs = pstmt.executequery()) {
                while (rs.next()) {
                    string empname = rs.getstring("emp_name");
                    double salary = rs.getdouble("salary");
                    string deptname = rs.getstring("dept_name");
                    results.add(new employeewithdept(empname, salary, deptname));
                }
            }
        }
        return results;
    }
    public static void main(string[] args) {
        employeereportservice service = new employeereportservice();
        try {
            // 查询 engineering 部门的员工 (假设 dept_id = 2)
            list<employeewithdept> engineeringemployees = service.getemployeesbydepartmentid(2);
            system.out.println("=== engineering 部门员工 ===");
            for (employeewithdept emp : engineeringemployees) {
                system.out.println(emp);
            }
        } catch (sqlexception e) {
            e.printstacktrace();
        }
    }
}

select e.emp_name, d.dept_name, e.salary
from employees e
inner join departments d on e.dept_id = d.dept_id
where e.salary > 75000;

select e.emp_name, d.dept_name, p.project_name
from employees e
inner join departments d on e.dept_id = d.dept_id
inner join project_assignments pa on e.emp_id = pa.emp_id
inner join projects p on pa.project_id = p.project_id;

select e.emp_name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id;

-- ❌ 错误示例
select e.emp_name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id
where dept_id = 1; -- 错误！应为 e.dept_id 或 d.dept_id
-- ✅ 正确示例
select e.emp_name, d.dept_name
from employees e
inner join departments d on e.dept_id = d.dept_id
where e.dept_id = 1;