Типы соединений SQL

1. Введение

В этом руководстве мы покажем различные типы соединений SQL и то, как их можно легко реализовать в Java.

2. Определение модели

Начнем с создания двух простых таблиц:

CREATE TABLE AUTHOR
(
  ID int NOT NULL PRIMARY KEY,
  FIRST_NAME varchar(255),
  LAST_NAME varchar(255)
);

CREATE TABLE ARTICLE
(
  ID int NOT NULL PRIMARY KEY,
  TITLE varchar(255) NOT NULL,
  AUTHOR_ID int,
  FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(ID)
);

И заполните их тестовыми данными:

INSERT INTO AUTHOR VALUES 
(1, 'Siena', 'Kerr'),
(2, 'Daniele', 'Ferguson'),
(3, 'Luciano', 'Wise'),
(4, 'Jonas', 'Lugo');

INSERT INTO ARTICLE VALUES
(1, 'First steps in Java', 1),
(2, 'SpringBoot tutorial', 1),
(3, 'Java 12 insights', null),
(4, 'SQL JOINS', 2),
(5, 'Introduction to Spring Security', 3);

Обратите внимание, что в нашем выборочном наборе данных не у всех авторов есть статьи, и наоборот. Это сыграет большую роль в наших примерах, которые мы увидим позже.

Давайте также определим POJO, который мы будем использовать для хранения результатов операций JOIN на протяжении всего нашего руководства:

class ArticleWithAuthor {

    private String title;
    private String authorFirstName;
    private String authorLastName;

    // standard constructor, setters and getters
}

В наших примерах мы будем извлекать заголовок из таблицы ARTICLE и данные об авторах из таблицы AUTHOR.

3. Конфигурация

В наших примерах мы будем использовать внешнюю базу данных PostgreSQL, работающую на порту 5432. Кроме FULL JOIN, которое не поддерживается ни в MySQL, ни в H2, все предоставленные фрагменты должны работать с любым провайдером SQL.

Для нашей реализации Java нам понадобится драйвер PostgreSQL :

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.5</version>
    <scope>test</scope>
</dependency>

Давайте сначала настроим java.sql.Connection для работы с нашей базой данных:

Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.
  getConnection("jdbc:postgresql://localhost:5432/myDb", "user", "pass");

Далее создадим класс DAO и несколько служебных методов:

class ArticleWithAuthorDAO {

    private final Connection connection;

    // constructor

    private List<ArticleWithAuthor> executeQuery(String query) {
        try (Statement statement = connection.createStatement()) {
            ResultSet resultSet = statement.executeQuery(query);
            return mapToList(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return new ArrayList<>();
    }

    private List<ArticleWithAuthor> mapToList(ResultSet resultSet) throws SQLException {
        List<ArticleWithAuthor> list = new ArrayList<>();
        while (resultSet.next()) {
            ArticleWithAuthor articleWithAuthor = new ArticleWithAuthor(
              resultSet.getString("TITLE"),
              resultSet.getString("FIRST_NAME"),
              resultSet.getString("LAST_NAME")
            );
            list.add(articleWithAuthor);
        }
        return list;
    }
}

В этой статье мы не будем вдаваться в подробности использования ResultSet , Statement и Connection. Эти темы рассматриваются в наших статьях, связанных с JDBC .

Давайте начнем изучать соединения SQL в разделах ниже.

4. Внутреннее соединение

Начнем, пожалуй, с самого простого типа соединения. INNER JOIN — это операция, которая выбирает строки, соответствующие заданному условию, из обеих таблиц. Запрос состоит как минимум из трех частей: выбор столбцов, объединение таблиц и условие соединения.

Принимая это во внимание, сам синтаксис становится довольно простым:

SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME
  FROM ARTICLE INNER JOIN AUTHOR 
  ON AUTHOR.ID=ARTICLE.AUTHOR_ID

Мы также можем проиллюстрировать результат INNER JOIN как общую часть пересекающихся множеств:

Давайте теперь реализуем метод INNER JOIN в классе ArticleWithAuthorDAO :

List<ArticleWithAuthor> articleInnerJoinAuthor() {
    String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
      + "FROM ARTICLE INNER JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
    return executeQuery(query);
}

И протестируйте его:

@Test
public void whenQueryWithInnerJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleInnerJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(4);
    assertThat(articleWithAuthorList)
      .noneMatch(row -> row.getAuthorFirstName() == null || row.getTitle() == null);
}

Как мы упоминали ранее, INNER JOIN выбирает только общие строки по заданному условию. Глядя на наши вставки, мы видим, что у нас одна статья без автора и один автор без статьи. Эти строки пропускаются, поскольку они не удовлетворяют заданному условию. В результате мы получаем четыре объединенных результата, и ни один из них не имеет пустых данных об авторах или пустого названия.

5. Левое соединение

Далее, давайте сосредоточимся на ЛЕВОМ СОЕДИНЕНИИ. Этот тип объединения выбирает все строки из первой таблицы и сопоставляет соответствующие строки из второй таблицы. При отсутствии совпадения столбцы заполняются нулевыми значениями .

Прежде чем мы углубимся в реализацию Java, давайте взглянем на графическое представление LEFT JOIN:

В этом случае результат LEFT JOIN включает каждую запись из набора, представляющего первую таблицу, с пересекающимися значениями из второй таблицы.

Теперь давайте перейдем к реализации Java:

List<ArticleWithAuthor> articleLeftJoinAuthor() {
    String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
      + "FROM ARTICLE LEFT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
    return executeQuery(query);
}

Единственное отличие от предыдущего примера состоит в том, что мы использовали ключевое слово LEFT вместо ключевого слова INNER.

Прежде чем мы проверим наш метод LEFT JOIN, давайте еще раз взглянем на наши вставки. В этом случае мы получим все записи из таблицы ARTICLE и соответствующие им строки из таблицы AUTHOR. Как мы упоминали ранее, не у каждой статьи есть автор, поэтому мы ожидаем, что вместо данных об авторе будут нулевые значения:

@Test
public void whenQueryWithLeftJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}

6. Правильное соединение

ПРАВОЕ СОЕДИНЕНИЕ очень похоже на ЛЕВОЕ СОЕДИНЕНИЕ, но оно возвращает все строки из второй таблицы и сопоставляет строки из первой таблицы. Как и в случае LEFT JOIN, пустые совпадения заменяются нулевыми значениями.

Графическое представление этого типа соединения является зеркальным отражением того, что мы проиллюстрировали для ЛЕВОГО СОЕДИНЕНИЯ:

Давайте реализуем RIGHT JOIN в Java:

List<ArticleWithAuthor> articleRightJoinAuthor() {
    String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
      + "FROM ARTICLE RIGHT JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
    return executeQuery(query);
}

Опять же, давайте посмотрим на наши тестовые данные. Поскольку эта операция соединения извлекает все записи из второй таблицы, мы ожидаем, что получим пять строк, и поскольку не каждый автор уже написал статью, мы ожидаем, что в столбце TITLE будут пустые значения :

@Test
public void whenQueryWithRightJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(5);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
}

7. Полное внешнее соединение

Эта операция соединения, вероятно, самая сложная. FULL JOIN выбирает все строки как из первой, так и из второй таблицы независимо от того, выполняется ли условие или нет.

Мы также можем представить ту же идею в виде всех значений из каждого из пересекающихся наборов:

Давайте посмотрим на реализацию Java:

List<ArticleWithAuthor> articleOuterJoinAuthor() {
    String query = "SELECT ARTICLE.TITLE, AUTHOR.LAST_NAME, AUTHOR.FIRST_NAME "
      + "FROM ARTICLE FULL JOIN AUTHOR ON AUTHOR.ID=ARTICLE.AUTHOR_ID";
    return executeQuery(query);
}

Теперь мы можем протестировать наш метод:

@Test
public void whenQueryWithFullJoin_thenShouldReturnProperRows() {
    List<ArticleWithAuthor> articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor();

    assertThat(articleWithAuthorList).hasSize(6);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null);
    assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null);
}

Еще раз давайте посмотрим на тестовые данные. У нас есть пять разных статей, у одной из которых нет автора, и четыре автора, у одной из которых нет назначенной статьи. В результате FULL JOIN мы ожидаем получить шесть строк. Четыре из них сопоставляются друг с другом, а остальные два нет. По этой причине мы также предполагаем, что будет по крайней мере одна строка с нулевыми значениями в обоих столбцах данных AUTHOR и одна с нулевым значением в столбце TITLE.

8. Заключение

В этой статье мы рассмотрели основные типы соединений SQL. Мы рассмотрели примеры четырех типов соединений и то, как их можно реализовать в Java.

Как всегда, полный код, использованный в этой статье, доступен на GitHub .