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);

샘플 데이터 세트에서 모든 저자가 기사를 가지고있는 것은 아니며 그 반대도 마찬가지입니다. 이것은 나중에 보게 될 예제에서 큰 역할을 할 것입니다.

또한 자습서 전체에서 JOIN 작업의 결과를 저장하는 데 사용할 POJO를 정의 해 보겠습니다.

class ArticleWithAuthor { private String title; private String authorFirstName; private String authorLastName; // standard constructor, setters and getters }

이 예에서는 ARTICLE 테이블에서 제목을 추출하고 AUTHOR 테이블에서 작성자 데이터를 추출합니다.

3. 구성

예제에서는 포트 5432 에서 실행되는 외부 PostgreSQL 데이터베이스를 사용 합니다. MySQL 또는 H2에서 지원되지 않는 FULL JOIN을 제외하고 제공된 모든 스 니펫은 모든 SQL 공급자에서 작동해야합니다.

Java 구현을 위해서는 PostgreSQL 드라이버가 필요합니다.

 org.postgresql postgresql 42.2.5 test 

먼저 데이터베이스와 함께 작동 하도록 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 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 mapToList(ResultSet resultSet) throws SQLException { List 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, StatementConnection 사용에 대해 자세히 설명하지 않습니다 . 이러한 주제는 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 의 결과를 설명 할 수 있습니다 .

이제 ArticleWithAuthorDAO 클래스 에서 INNER JOIN에 대한 메서드를 구현해 보겠습니다 .

List 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() 

앞서 언급했듯이 INNER JOIN은 제공된 조건에 따라 공통 행만 선택합니다. 삽입물을 보면 저자가없는 기사가 하나 있고 기사가없는 저자가 하나 있음을 알 수 있습니다. 이러한 행은 제공된 조건을 충족하지 않기 때문에 건너 뜁니다. 결과적으로 우리는 4 개의 결합 된 결과를 검색하고, 그들 중 누구도 빈 저자 데이터 나 빈 제목을 가지고 있지 않습니다.

5. 왼쪽 조인

다음으로 LEFT JOIN에 집중하겠습니다. 이러한 종류의 조인은 첫 번째 테이블의 모든 행을 선택하고 두 번째 테이블의 해당 행과 일치합니다. 일치하는 항목이 없으면 열이 null 값 으로 채워집니다 .

Java 구현에 대해 알아보기 전에 LEFT JOIN의 그래픽 표현을 살펴 보겠습니다.

이 경우 LEFT JOIN 의 결과 에는 두 번째 테이블의 교차 값이있는 첫 번째 테이블을 나타내는 집합의 모든 레코드가 포함됩니다.

이제 Java 구현으로 이동해 보겠습니다.

List 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); }

이전 예제와의 유일한 차이점은 INNER 키워드 대신 LEFT 키워드를 사용했다는 것입니다.

LEFT JOIN 메서드를 테스트하기 전에 삽입물을 다시 살펴 보겠습니다. 이 경우 ARTICLE 테이블의 모든 레코드와 AUTHOR 테이블의 일치하는 행을받습니다. 앞서 언급했듯이 모든 기사에 아직 작성자가있는 것은 아니므 로 작성자 데이터 대신 null 값 이있을 것으로 예상합니다 .

@Test public void whenQueryWithLeftJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleLeftJoinAuthor(); assertThat(articleWithAuthorList).hasSize(5); assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null); }

6. 오른쪽 조인

The RIGHT JOIN is much like the LEFT JOIN, but it returns all rows from the second table and matches rows from the first table. Like in case of the LEFT JOIN, empty matches are replaced by null values.

The graphical representation of this kind of join is a mirror reflection of the one we've illustrated for the LEFT JOIN:

Let's implement the RIGHT JOIN in Java:

List 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); }

Again, let's look at our test data. Since this join operation retrieves all records from the second table we expect to retrieve five rows, and because not every author has already written an article, we expect some null values in the TITLE column:

@Test public void whenQueryWithRightJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleRightJoinAuthor(); assertThat(articleWithAuthorList).hasSize(5); assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null); }

7. Full Outer Join

This join operation is probably the most tricky one. The FULL JOIN selects all rows from both the first and the second table regardless of whether the condition is met or not.

We can also represent the same idea as all values from each of the intersecting sets:

Let's have a look at the Java implementation:

List 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); }

Now, we can test our method:

@Test public void whenQueryWithFullJoin_thenShouldReturnProperRows() { List articleWithAuthorList = articleWithAuthorDAO.articleOuterJoinAuthor(); assertThat(articleWithAuthorList).hasSize(6); assertThat(articleWithAuthorList).anyMatch(row -> row.getTitle() == null); assertThat(articleWithAuthorList).anyMatch(row -> row.getAuthorFirstName() == null); }

다시 한번 테스트 데이터를 살펴 보겠습니다. 우리는 5 개의 다른 기사를 가지고 있는데, 그 중 하나는 저자가없고, 4 명의 저자는 할당 된 기사가 없습니다. FULL JOIN의 결과로 6 개의 행을 검색 할 수 있습니다. 그중 4 개는 서로 일치하고 나머지 2 개는 일치하지 않습니다. 그 때문에, 우리는 또한 적어도 하나 개의 행이있을 것이라고 가정 모두 AUTHOR 데이터 항목의 값 및 하나의 표제 열의 값.

8. 결론

이 기사에서는 SQL 조인의 기본 유형을 살펴 보았습니다. 네 가지 유형의 조인의 예와 이들이 Java에서 구현되는 방법을 살펴 보았습니다.

항상 그렇듯이이 기사에 사용 된 전체 코드는 GitHub에서 사용할 수 있습니다.