MySQL connection query and its principle

1. Content of this article

Cartesian product

Inner connection

External connection

Left connection

Right connection

Principle of meter connection

Use java to realize connection query and deepen understanding

2. Prepare data

2 sheets:

t_team: group table.

t_employee: employee table. There is a team in it_ id refers to the id of the group table.

drop table if exists t_team;
create table t_team(
  id int not null AUTO_INCREMENT PRIMARY KEY comment 'group id',
  team_name varchar(32) not null default '' comment 'name'
) comment 'Group table';

drop table if exists t_employee;
create table t_employee(
  id int not null AUTO_INCREMENT PRIMARY KEY comment 'department id',
  emp_name varchar(32) not null default '' comment 'Employee name',
  team_id int not null default 0 comment 'Employee's group id'
) comment 'Employee list';

insert into t_team values (1,'Schema group'),(2,'Test group'),(3,'java group'),(4,'Front end group');
insert into t_employee values (1,'Passerby a Java',1),(2,'Zhang San',2),(3,'Li Si',3),(4,'Wang Wu',0),(5,'Zhao Liu',0);

t_ There are 4 records in team table, as follows:

mysql> select * from t_team;
+----+-----------+
| id | team_name |
+----+-----------+
|  1 | Schema group   |
|  2 | Test group   |
|  3 | java group   |
|  4 | Front end group   |
+----+-----------+
4 rows in set (0.00 sec)

t_ There are 5 records in the employee table, as follows:

mysql> select * from t_employee;
+----+---------------+---------+
| id | emp_name    | team_id |
+----+---------------+---------+
|  1 | Passerby a Java   |    1 |
|  2 | Zhang San      |    2 |
|  3 | Li Si      |    3 |
|  4 | Wang Wu      |    0 |
|  5 | Zhao Liu      |    0 |
+----+---------------+---------+
5 rows in set (0.00 sec)

3. Cartesian product

Before introducing join queries, we need to look at Cartesian products.

Simple understanding of Cartesian product: there are two sets A and B. Cartesian product represents all possible results produced by arbitrary correlation between elements in A set and elements in B set.

If there are m elements in A and n elements in B, the Cartesian product of A and B produces m*n results, which is equivalent to circular traversal of the elements in the two sets.

The java pseudo code is represented as follows:

for(Object eleA : A){
	for(Object eleB : B){
		System.out.print(eleA+","+eleB);
	}
}

Process: take the first row in set A to match all rows in set B, and then take the second row in set A to match all rows in set B. the final result quantity is m*n.

Cartesian product syntax in sql

select field from Table 1,Table 2[,surface N];
perhaps
select field from Table 1 join Table 2 [join surface N];

Example:

mysql> select * from t_team,t_employee;
+----+-----------+----+---------------+---------+
| id | team_name | id | emp_name    | team_id |
+----+-----------+----+---------------+---------+
|  1 | Schema group   |  1 | Passerby a Java   |    1 |
|  2 | Test group   |  1 | Passerby a Java   |    1 |
|  3 | java group   |  1 | Passerby a Java   |    1 |
|  4 | Front end group   |  1 | Passerby a Java   |    1 |
|  1 | Schema group   |  2 | Zhang San      |    2 |
|  2 | Test group   |  2 | Zhang San      |    2 |
|  3 | java group   |  2 | Zhang San      |    2 |
|  4 | Front end group   |  2 | Zhang San      |    2 |
|  1 | Schema group   |  3 | Li Si      |    3 |
|  2 | Test group   |  3 | Li Si      |    3 |
|  3 | java group   |  3 | Li Si      |    3 |
|  4 | Front end group   |  3 | Li Si      |    3 |
|  1 | Schema group   |  4 | Wang Wu      |    0 |
|  2 | Test group   |  4 | Wang Wu      |    0 |
|  3 | java group   |  4 | Wang Wu      |    0 |
|  4 | Front end group   |  4 | Wang Wu      |    0 |
|  1 | Schema group   |  5 | Zhao Liu      |    0 |
|  2 | Test group   |  5 | Zhao Liu      |    0 |
|  3 | java group   |  5 | Zhao Liu      |    0 |
|  4 | Front end group   |  5 | Zhao Liu      |    0 |
+----+-----------+----+---------------+---------+
20 rows in set (0.00 sec)

t_team Table 4 records, t_ There are 5 records in the employee table, and 20 rows of records are output from the Cartesian product result.

4. Inner connection

Syntax:

select field from Table 1 inner join Table 2 on Connection conditions;
or
select field from Table 1 join Table 2 on Connection conditions;
or
select field from Table 1, Table 2 [where Association condition];

Inner connection is equivalent to adding connection conditions on the basis of Cartesian product.

When there is no connection condition, the inner connection rises to Cartesian product.

The java pseudo code used in the process is as follows:

for(Object eleA : A){
	for(Object eleB : B){
		if(Whether the connection condition is true){
			System.out.print(eleA+","+eleB);
		}
	}
}

Example 1: with connection conditions

Query employees and departments

mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Zhang San      | Test group   |
| Li Si      | java group   |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> select t1.emp_name,t2.team_name from t_employee t1 join t_team t2 on t1.team_id = t2.id;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Zhang San      | Test group   |
| Li Si      | java group   |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Zhang San      | Test group   |
| Li Si      | java group   |
+---------------+-----------+
3 rows in set (0.00 sec)

The above is equivalent to obtaining the intersection of two tables and querying the data of both tables.

Example 2: no connection condition

Unconditional inner connection, rising to Cartesian product, as follows:

mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Passerby a Java   | Test group   |
| Passerby a Java   | java group   |
| Passerby a Java   | Front end group   |
| Zhang San      | Schema group   |
| Zhang San      | Test group   |
| Zhang San      | java group   |
| Zhang San      | Front end group   |
| Li Si      | Schema group   |
| Li Si      | Test group   |
| Li Si      | java group   |
| Li Si      | Front end group   |
| Wang Wu      | Schema group   |
| Wang Wu      | Test group   |
| Wang Wu      | java group   |
| Wang Wu      | Front end group   |
| Zhao Liu      | Schema group   |
| Zhao Liu      | Test group   |
| Zhao Liu      | java group   |
| Zhao Liu      | Front end group   |
+---------------+-----------+
20 rows in set (0.00 sec)

Example 3: query by combining criteria

There are three ways to query the employees of the schema group

mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id and t2.team_name = 'Schema group';
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> select t1.emp_name,t2.team_name from t_employee t1 inner join t_team t2 on t1.team_id = t2.id where t2.team_name = 'Schema group';
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> select t1.emp_name,t2.team_name from t_employee t1, t_team t2 where t1.team_id = t2.id and t2.team_name = 'Schema group';
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
+---------------+-----------+
1 row in set (0.00 sec)

The above three methods explain:

• Combination condition is used in mode 1: on. (select field from table 1 inner join table 2 on join condition and condition)
• Method 2: filter after the connection results, which is equivalent to obtaining the connection results first, and then filtering the connection results using the conditions in where. (select field from table 1 inner join table 2 on connection condition where Association condition)
• Method 3: filter directly behind where. (select field from table 1, table 2, where Association condition and condition)

summary

It is recommended to use the third syntax for inner join, which is concise: (but it is often used: table 1inner join table 2 on join conditions)

select field from Table 1, Table 2 [where Association condition];

5. External connection

The external connection involves two tables, which are divided into master table and slave table. The information to be queried mainly comes from which table is the master table.

External connection query results are all records in the main table. If there are matching values from the table, the matching values will be displayed, which is equivalent to the results of the inner join query; If there is no matching from the table, null is displayed.

Final: external connection query result = internal connection result + records in the main table but not in the internal connection result.

There are two types of external connections:

• Left outer link: use the left join keyword. The main table is on the left of left join.
• Right outer join: use the right join keyword. The main table is on the right of right join.

Left connection

Syntax:

select column from Main table left join From table on Connection conditions;

Example 1:

Query all employee information and display the employee's group as follows:

mysql> SELECT
    t1.emp_name,
    t2.team_name
  FROM
    t_employee t1
  LEFT JOIN
    t_team t2
  ON
    t1.team_id = t2.id;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Zhang San      | Test group   |
| Li Si      | java group   |
| Wang Wu      | NULL    |
| Zhao Liu      | NULL    |
+---------------+-----------+
5 rows in set (0.00 sec)

All employees and employee teams are queried above_ Team with id = 0_ Name is NULL.

Example 2:

Query employee name and group name, and return the records whose group name is not empty, as follows:

mysql> SELECT
    t1.emp_name,
    t2.team_name
  FROM
    t_employee t1
  LEFT JOIN
    t_team t2
  ON
    t1.team_id = t2.id
  WHERE
    t2.team_name IS NOT NULL;
+---------------+-----------+
| emp_name    | team_name |
+---------------+-----------+
| Passerby a Java   | Schema group   |
| Zhang San      | Test group   |
| Li Si      | java group   |
+---------------+-----------+
3 rows in set (0.00 sec)

First, use the internal connection to obtain the connection results, and then use where to filter the connection results.

Right connection

Syntax:

select column from From table right join Main table on Connection conditions;

Example:

We use the right connection to realize the above functions realized by the left connection, as follows:

mysql> SELECT
    t2.team_name,
    t1.emp_name
  FROM
    t_team t2
  RIGHT JOIN
    t_employee t1
  ON
    t1.team_id = t2.id;
+-----------+---------------+
| team_name | emp_name    |
+-----------+---------------+
| Schema group   | Passerby a Java   |
| Test group   | Zhang San      |
| java group   | Li Si      |
| NULL    | Wang Wu      |
| NULL    | Zhao Liu      |
+-----------+---------------+
5 rows in set (0.00 sec)

mysql> SELECT
    t2.team_name,
    t1.emp_name
  FROM
    t_team t2
  RIGHT JOIN
    t_employee t1
  ON
    t1.team_id = t2.id
  WHERE
    t2.team_name IS NOT NULL;
+-----------+---------------+
| team_name | emp_name    |
+-----------+---------------+
| Schema group   | Passerby a Java   |
| Test group   | Zhang San      |
| java group   | Li Si      |
+-----------+---------------+
3 rows in set (0.00 sec)

6. Understand the principle of table connection

Prepare data:

drop table if exists test1;
create table test1(
  a int
);
drop table if exists test2;
create table test2(
  b int
);
insert into test1 values (1),(2),(3);
insert into test2 values (3),(4),(5);
mysql> select * from test1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select * from test2;
+------+
| b    |
+------+
|    3 |
|    4 |
|    5 |
+------+
3 rows in set (0.00 sec)

Let's write some connections to see the effect.

Example 1: internal connection

mysql> select * from test1 t1,test2 t2;
+------+------+
| a   | b   |
+------+------+
|   1 |   3 |
|   2 |   3 |
|   3 |   3 |
|   1 |   4 |
|   2 |   4 |
|   3 |   4 |
|   1 |   5 |
|   2 |   5 |
|   3 |   5 |
+------+------+
9 rows in set (0.00 sec)

mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
+------+------+
| a   | b   |
+------+------+
|   3 |   3 |
+------+------+
1 row in set (0.00 sec)

9 pieces of data are normal.

Example 2: left connection

mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
+------+------+
| a   | b   |
+------+------+
|   3 |   3 |
|   1 | NULL |
|   2 | NULL |
+------+------+
3 rows in set (0.00 sec)
  
mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
+------+------+
| a   | b   |
+------+------+
|   1 | NULL |
|   2 | NULL |
|   3 | NULL |
+------+------+
3 rows in set (0.00 sec)
  
mysql> select * from test1 t1 left join test2 t2 on 1=1;
+------+------+
| a   | b   |
+------+------+
|   1 |   3 |
|   2 |   3 |
|   3 |   3 |
|   1 |   4 |
|   2 |   4 |
|   3 |   4 |
|   1 |   5 |
|   2 |   5 |
|   3 |   5 |
+------+------+
9 rows in set (0.00 sec)

The left connection above is easy to understand.

The second sql connection condition T1 a> 10. This condition is only associated with the test1 table. Can you understand the results? If you don't understand, continue to look down. We use java code to implement connection query.

The connection condition 1 = 1 in the third sql is true, and the returned result is Cartesian product.

7. java code to realize connection query

The following is a simplified implementation

package com.itsoku.sql;

import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

public class Test1 {
    public static class Table1 {
        int a;

        public int getA() {
            return a;
        }

        public void setA(int a) {
            this.a = a;
        }

        public Table1(int a) {
            this.a = a;
        }

        @Override
        public String toString() {
            return "Table1{" +
                    "a=" + a +
                    '}';
        }

        public static Table1 build(int a) {
            return new Table1(a);
        }
    }

    public static class Table2 {
        int b;

        public int getB() {
            return b;
        }

        public void setB(int b) {
            this.b = b;
        }

        public Table2(int b) {
            this.b = b;
        }

        public static Table2 build(int b) {
            return new Table2(b);
        }

        @Override
        public String toString() {
            return "Table2{" +
                    "b=" + b +
                    '}';
        }
    }

    public static class Record<R1, R2> {
        R1 r1;
        R2 r2;

        public R1 getR1() {
            return r1;
        }

        public void setR1(R1 r1) {
            this.r1 = r1;
        }

        public R2 getR2() {
            return r2;
        }

        public void setR2(R2 r2) {
            this.r2 = r2;
        }

        public Record(R1 r1, R2 r2) {
            this.r1 = r1;
            this.r2 = r2;
        }

        @Override
        public String toString() {
            return "Record{" +
                    "r1=" + r1 +
                    ", r2=" + r2 +
                    '}';
        }

        public static <R1, R2> Record<R1, R2> build(R1 r1, R2 r2) {
            return new Record(r1, r2);
        }
    }

    public static enum JoinType {
        innerJoin, leftJoin
    }


    public static interface Filter<R1, R2> {
        boolean accept(R1 r1, R2 r2);
    }

    public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
        if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
            return new ArrayList<>();
        }

        List<Record<R1, R2>> result = new CopyOnWriteArrayList<>();

        for (R1 r1 : table1) {
            List<Record<R1, R2>> onceJoinResult = joinOn(r1, table2, onFilter);
            result.addAll(onceJoinResult);
        }

        if (joinType == JoinType.leftJoin) {
            List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
            List<Record<R1, R2>> leftAppendList = new ArrayList<>();
            for (R1 r1 : table1) {
                if (!r1Record.contains(r1)) {
                    leftAppendList.add(Record.build(r1, null));
                }
            }
            result.addAll(leftAppendList);
        }
        if (Objects.nonNull(whereFilter)) {
            for (Record<R1, R2> record : result) {
                if (!whereFilter.accept(record.r1, record.r2)) {
                    result.remove(record);
                }
            }
        }
        return result;
    }

    public static <R1, R2> List<Record<R1, R2>> joinOn(R1 r1, List<R2> table2, Filter<R1, R2> onFilter) {
        List<Record<R1, R2>> result = new ArrayList<>();
        for (R2 r2 : table2) {
            if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
                result.add(Record.build(r1, r2));
            }
        }
        return result;
    }

    @Test
    public void innerJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
        System.out.println("-----------------");
        join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
    }

    @Test
    public void leftJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
        System.out.println("-----------------");
        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
    }

}

The innerJoin() method in the code simulates the following sql:

mysql> select * from test1 t1,test2 t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
+------+------+
9 rows in set (0.00 sec)

mysql> select * from test1 t1,test2 t2 where t1.a = t2.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
+------+------+
1 row in set (0.00 sec)

Run innerJoin() and the output is as follows:

Record{r1=Table1{a=1}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=Table2{b=4}}
Record{r1=Table1{a=1}, r2=Table2{b=5}}
Record{r1=Table1{a=2}, r2=Table2{b=3}}
Record{r1=Table1{a=2}, r2=Table2{b=4}}
Record{r1=Table1{a=2}, r2=Table2{b=5}}
Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=3}, r2=Table2{b=4}}
Record{r1=Table1{a=3}, r2=Table2{b=5}}
\-----------------
Record{r1=Table1{a=3}, r2=Table2{b=3}}

Compare the results of sql and java. The number of output results and data are basically the same. The only difference is that the order is different. Why the order is different is described below.

The leftJoin() method in the code simulates the following sql:

mysql> select * from test1 t1 left join test2 t2 on t1.a = t2.b;
+------+------+
| a   | b   |
+------+------+
|   3 |   3 |
|   1 | NULL |
|   2 | NULL |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from test1 t1 left join test2 t2 on t1.a>10;
+------+------+
| a   | b   |
+------+------+
|   1 | NULL |
|   2 | NULL |
|   3 | NULL |
+------+------+
3 rows in set (0.00 sec)

Run leftJoin() and the result is as follows:

Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
-----------------
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
Record{r1=Table1{a=3}, r2=null}

The effect is exactly the same as that of sql, and can be matched.

Now let's discuss why the order of java output is inconsistent with sql?

Nested loops are used for the join query of the two tables in the above java code. Every time the outer loop is executed, all the tables in the inner loop will be traversed once. If it is placed in mysql, It is equivalent to scanning all the internal tables (driven tables) once (a full table io read operation), and the main table (outer loop) if there are n pieces of data, you need to scan the whole table n times from the table. The data of the table is stored in the disk. io operation is required for each full table scan. io operation is the most time-consuming. If MySQL is implemented according to the above Java method, the efficiency must be very low.

How is mysql optimized?

msql uses a memory cache space internally, which is called join_buffer. First put the data of the outer loop into the join_buffer, and then traverse the slave table to get a piece of data and join from the table_ Compare the data of buffer, and then take item 2 and join from the table_ The buffer data is compared until the traversal from the table is completed. This method is used to reduce the number of io scans from the table_ When the buffer is large enough to store all the data in the master table, the slave table only needs to be scanned once (that is, only one full table io read operation is required).

This method in mysql is called Block Nested Loop.

Improve the java code to implement join_buffer process.

8. Improved version of java code

package com.itsoku.sql;

import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.stream.Collectors;

import com.itsoku.sql.Test1.*;

public class Test2 {

    public static int joinBufferSize = 10000;
    public static List<?> joinBufferList = new ArrayList<>();

    public static <R1, R2> List<Record<R1, R2>> join(List<R1> table1, List<R2> table2, JoinType joinType, Filter<R1, R2> onFilter, Filter<R1, R2> whereFilter) {
        if (Objects.isNull(table1) || Objects.isNull(table2) || joinType == null) {
            return new ArrayList<>();
        }

        List<Test1.Record<R1, R2>> result = new CopyOnWriteArrayList<>();

        int table1Size = table1.size();
        int fromIndex = 0, toIndex = joinBufferSize;
        toIndex = Integer.min(table1Size, toIndex);
        while (fromIndex < table1Size && toIndex <= table1Size) {
            joinBufferList = table1.subList(fromIndex, toIndex);
            fromIndex = toIndex;
            toIndex += joinBufferSize;
            toIndex = Integer.min(table1Size, toIndex);

            List<Record<R1, R2>> blockNestedLoopResult = blockNestedLoop((List<R1>) joinBufferList, table2, onFilter);
            result.addAll(blockNestedLoopResult);
        }

        if (joinType == JoinType.leftJoin) {
            List<R1> r1Record = result.stream().map(Record::getR1).collect(Collectors.toList());
            List<Record<R1, R2>> leftAppendList = new ArrayList<>();
            for (R1 r1 : table1) {
                if (!r1Record.contains(r1)) {
                    leftAppendList.add(Record.build(r1, null));
                }
            }
            result.addAll(leftAppendList);
        }
        if (Objects.nonNull(whereFilter)) {
            for (Record<R1, R2> record : result) {
                if (!whereFilter.accept(record.r1, record.r2)) {
                    result.remove(record);
                }
            }
        }
        return result;
    }

    public static <R1, R2> List<Record<R1, R2>> blockNestedLoop(List<R1> joinBufferList, List<R2> table2, Filter<R1, R2> onFilter) {
        List<Record<R1, R2>> result = new ArrayList<>();
        for (R2 r2 : table2) {
            for (R1 r1 : joinBufferList) {
                if (Objects.nonNull(onFilter) ? onFilter.accept(r1, r2) : true) {
                    result.add(Record.build(r1, r2));
                }
            }
        }
        return result;
    }

    @Test
    public void innerJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.innerJoin, null, null).forEach(System.out::println);
        System.out.println("-----------------");
        join(table1, table2, JoinType.innerJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
    }

    @Test
    public void leftJoin() {
        List<Table1> table1 = Arrays.asList(Table1.build(1), Table1.build(2), Table1.build(3));
        List<Table2> table2 = Arrays.asList(Table2.build(3), Table2.build(4), Table2.build(5));

        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a == r2.b, null).forEach(System.out::println);
        System.out.println("-----------------");
        join(table1, table2, JoinType.leftJoin, (r1, r2) -> r1.a > 10, null).forEach(System.out::println);
    }
}

Execute innerJoin() and output:

Record{r1=Table1{a=1}, r2=Table2{b=3}}
Record{r1=Table1{a=2}, r2=Table2{b=3}}
Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=Table2{b=4}}
Record{r1=Table1{a=2}, r2=Table2{b=4}}
Record{r1=Table1{a=3}, r2=Table2{b=4}}
Record{r1=Table1{a=1}, r2=Table2{b=5}}
Record{r1=Table1{a=2}, r2=Table2{b=5}}
Record{r1=Table1{a=3}, r2=Table2{b=5}}
-----------------
Record{r1=Table1{a=3}, r2=Table2{b=3}}

Execute leftJoin() and output:

Record{r1=Table1{a=3}, r2=Table2{b=3}}
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
-----------------
Record{r1=Table1{a=1}, r2=null}
Record{r1=Table1{a=2}, r2=null}
Record{r1=Table1{a=3}, r2=null}

The result is completely consistent with that of sql.

8. Expand

You can also use group by, having, order by, and limit in the table connection.

These keywords are equivalent to operating on the results of table connection. You can practice it.