Соединение связанных таблиц в Java

Когда данные разнесены по таблицам, для отчёта нужны поля с обеих сторон. INNER JOIN оставляет только те пары, где ключ ссылки совпал.

Выборка отделов и сотрудников с равенством идентификаторов выглядит так:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
	public static void main(String[] args) throws Exception {
				try (Connection connection = DriverManager.getConnection("jdbc:sqlite:join_staff.db")) {
						connection.createStatement().execute(
							"PRAGMA foreign_keys = ON"
						);
						try (Statement statement = connection.createStatement()) {
							statement.execute(
					"""
		CREATE TABLE IF NOT EXISTS departments (
											id INTEGER PRIMARY KEY,
											title TEXT NOT NULL
										)
		"""
							);
							statement.execute(
					"""
		CREATE TABLE IF NOT EXISTS staff (
											id INTEGER PRIMARY KEY,
											dept_id INTEGER NOT NULL,
											full_name TEXT NOT NULL,
											FOREIGN KEY (dept_id)
												REFERENCES departments (id)
										)
		"""
							);
						}
						PreparedStatement dept = connection.prepareStatement(
							"INSERT INTO departments (title) VALUES (?)"
						);
						dept.setString(1, "Support");
						dept.executeUpdate();
						PreparedStatement member = connection.prepareStatement(
					"""
		INSERT INTO staff (dept_id, full_name)
									VALUES (?, ?)
		"""
						);
						member.setInt(1, 1);
						member.setString(2, "Alice Gray");
						member.executeUpdate();
						member.setInt(1, 1);
						member.setString(2, "Ben Holt");
						member.executeUpdate();
						try (Statement statement = connection.createStatement()) {
							ResultSet result = statement.executeQuery(
					"""
		SELECT s.full_name, d.title
										FROM staff s
										INNER JOIN departments d
											ON s.dept_id = d.id
										ORDER BY s.id
		"""
							);
							while (result.next()) {
								System.out.println(result.getString("full_name"));
								System.out.println(result.getString("title"));
							}
						}
					}
	}
}

Псевдонимы s и d сокращают имена столбцов. Фильтр по отделу задают в WHERE после соединения:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
	public static void main(String[] args) throws Exception {
				try (Connection connection = DriverManager.getConnection("jdbc:sqlite:join_filter.db")) {
						connection.createStatement().execute(
							"PRAGMA foreign_keys = ON"
						);
						try (Statement statement = connection.createStatement()) {
							statement.execute(
					"""
		CREATE TABLE IF NOT EXISTS departments (
											id INTEGER PRIMARY KEY,
											title TEXT NOT NULL
										)
		"""
							);
							statement.execute(
					"""
		CREATE TABLE IF NOT EXISTS staff (
											id INTEGER PRIMARY KEY,
											dept_id INTEGER NOT NULL,
											full_name TEXT NOT NULL,
											FOREIGN KEY (dept_id)
												REFERENCES departments (id)
										)
		"""
							);
						}
						PreparedStatement dept = connection.prepareStatement(
							"INSERT INTO departments (title) VALUES (?)"
						);
						dept.setString(1, "Support");
						dept.executeUpdate();
						dept.setString(1, "Archive");
						dept.executeUpdate();
						PreparedStatement member = connection.prepareStatement(
					"""
		INSERT INTO staff (dept_id, full_name)
									VALUES (?, ?)
		"""
						);
						member.setInt(1, 1);
						member.setString(2, "Alice Gray");
						member.executeUpdate();
						member.setInt(1, 2);
						member.setString(2, "Cara Mint");
						member.executeUpdate();
						try (Statement statement = connection.createStatement()) {
							ResultSet result = statement.executeQuery(
					"""
		SELECT s.full_name
										FROM staff s
										INNER JOIN departments d
											ON s.dept_id = d.id
										WHERE d.title = 'Support'
		"""
							);
							while (result.next()) {
								System.out.println(result.getString("full_name"));
							}
						}
					}
	}
}

В join_staff.db добавьте отдел "Sales" и сотрудника в нём. Выведите имя и название отдела одной строкой на человека.

Создайте join_fleet.db с таблицами garages и vehicles. Выведите номер машины и код гаража для каждой пары.

В join_fleet.db оставьте в выводе только машины гаража с кодом "G-7".