Join statements in a database are used to combine two or more tables based on join conditions. The set generated by a "join" can be saved as a table or used as a table.
A join statement combines the attributes of two tables based on their values. The main types of joins in databases include inner join, outer join, semi join, and anti join. Semi joins and anti joins are both rewritten using subqueries; SQL itself does not define the syntax for these operations.
Join conditions
Join conditions can be classified into equality joins (for example, t1.a = t2.b) and inequality joins (for example, t1.a < t2.b). Compared with inequality join conditions, equality join conditions allow the database to use efficient join algorithms, such as hash join and merge-sort join.
Self-Join
A self-join is a join operation where a table is joined with itself. The following example illustrates a self-join.
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t1 AS ta, t1 AS tb WHERE ta.b = tb.b;
Inner join
An inner join is the most fundamental join operation in a database. It combines columns from two tables, such as A and B, based on join conditions to produce a new result table. The query compares each row of table A with each row of table B and identifies the combinations that meet the join conditions. When a join condition is satisfied, the matching rows from A and B are combined (side by side) into a row in the result set. The resulting set can be defined as first performing a Cartesian product (cross join) on the two tables—combining every row from A with every row from B—and then returning only the records that satisfy the join conditions.
obclient> CREATE TABLE t1(c1 INT,c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT,c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t1 JOIN t2 USING(c1);
Outer join
An outer join does not require each record in the joined tables to have a matching record in the other table. The table that retains all records, even those without matches, is called the retaining table.
Outer joins can be further divided into left outer join, right outer join, and full outer join based on whether they retain rows from the left table, right table, or both tables.
In a left outer join, if a row in the left table does not exist in the right table,
NULLis automatically filled in for that row in the right table.In a right outer join, if a row in the right table does not exist in the left table,
NULLis automatically filled in for that row in the left table.A full join automatically fills in
NULLfor unmatched rows in either the left or right table.
obclient> CREATE TABLE t1(c1 INT,c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT,c2 INT);
Query OK, 0 rows affected
obclient> SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
obclient> SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1;
obclient> SELECT * FROM t1 FULL JOIN t2 ON t1.c1 = t2.c1;
Semi-join
When performing a left/right semi-join between table A and table B, it returns only the rows from table A that have a match in table B, or the rows from table B that have a match in table A.
A semi-join can only be obtained by expanding subqueries, as shown below.
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES (1, 1, 1),(2, 2, 2);
obclient> INSERT INTO t2 VALUES (1, 1, 1),(2, 2, 2);
obclient> SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
When you use the EXPLAIN statement to view a query plan, you can see that subqueries with dependencies are expanded and rewritten into semi joins.
obclient> EXPLAIN SELECT * FROM t1 WHERE t1.a IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
| ========================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
----------------------------------------
|0 |MERGE SEMI JOIN| |2 |76 |
|1 | TABLE SCAN |t1 |2 |37 |
|2 | SORT | |2 |38 |
|3 | TABLE SCAN |t2 |2 |37 |
========================================
...
Anti-Join
When performing a left/right Anti-Join between tables A and B, it returns only the rows from table A that have no match in table B, or the rows from table B that have no match in table A.
Similar to a Semi-Join, an Anti-Join can also be expanded using subqueries, as shown below.
obclient> CREATE TABLE t1(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(a INT PRIMARY KEY, b INT, c INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES (1, 1, 1),(2, 2, 2);
obclient> INSERT INTO t2 VALUES (1, 1, 1),(2, 2, 2);
obclient> SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
When using EXPLAIN to view the query plan, you can see that subqueries with dependencies are rewritten as Anti-Joins:
obclient> EXPLAIN SELECT * FROM t1 WHERE t1.a NOT IN (SELECT t2.b FROM t2 WHERE t2.c = t1.c);
| =============================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
---------------------------------------------
|0 |HASH RIGHT ANTI JOIN| |0 |77 |
|1 | TABLE SCAN |t2 |2 |37 |
|2 | TABLE SCAN |t1 |2 |37 |
=============================================
...