To query data from multiple tables, add the JOIN...ON clause to the FROM keyword in the SELECT statement to join these tables. Generally, these tables must have some related fields. For example, they may have some fields that share the same definition and data. These fields can be used as a join condition. The join condition is enclosed by parentheses () following the ON clause in which you can also pass a filter condition. Syntax for the SQL statement:
SELECT select_list FROM table_name1 [INNER] JOIN table_name2 ON ( join_condition )
WHERE query_condition
ORDER BY column_list
Return data from multiple tables that satisfy the join condition
By default, a join only returns results that satisfy a join condition following the ON clause. In this case, the join is called an INNER JOIN. Generally, if no other JOIN type is specified, JOIN means INNER JOIN. The tables that precede and follow the JOIN keyword are respectively called the left and right tables. The conditions specified in the ON clause describe the join and filter conditions of the left and right tables. Without the ON clause, the INNER JOIN clause returns all data from the left and right tables. This result is called the Cartesian Product.
The results returned by an SQL statement are filtered by the join condition specified in the JOIN clause and query conditions specified in the WHERE clause. Then, the filtered results are sorted by the column specified in the ORDER BY clause.
Example: Using the JOIN clause to query data from multiple tables
obclient> create table t1(id number not null primary key, name varchar(50));
Query OK, 0 rows affected (0.08 sec)
obclient> create table t2(id number not null primary key, name varchar(50));
Query OK, 0 rows affected (0.06 sec)
obclient> insert into t1 values(1,'A1'),(2,'B1'),(4,'D1'),(6,'F1'),(8,'H1'),(10,'J1');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
obclient> insert into t2 values(1,'B2'),(3,'C2'),(6,'F2'),(9,'I2');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
obclient> select t1.id, t1.name, t2.id, t2.name from t1 join t2 on (t1.id=t2.id) ;
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 6 | F1 | 6 | F2 |
+----+------+----+------+
2 rows in set (0.01 sec)
Return data from multiple tables that do not satisfy the join condition
To use a JOIN clause to return data that satisfies the join and filter conditions and data from the left table that satisfies the filter condition but does not satisfy the join condition specified in the left table, you can use a LEFT OUTER JOIN, or LEFT JOIN for short. If the results returned by a LEFT JOIN do not contain data from the right table, the column returns NULL.
To use a JOIN clause to return data that satisfies the join and filter conditions and data from the right table that satisfies the filter condition but does not satisfy the join condition specified in the right table, you can use RIGHT OUTER JOIN, or RIGHT JOIN for short. If the results returned by a RIGHT JOIN do not contain data from the left table, the column returns NULL.
Example: Using a LEFT JOIN and a RIGHT JOIN
obclient> select t1.id, t1.name, t2.id, t2.name from t1 left join t2 on (t1.id=t2.id);
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| 2 | B1 | NULL | NULL |
| 4 | D1 | NULL | NULL |
| 6 | F1 | 6 | F2 |
| 8 | H1 | NULL | NULL |
| 10 | J1 | NULL | NULL |
+----+------+----+------+
6 rows in set (0.01 sec)
obclient> select t1.id, t1.name, t2.id, t2.name from t1 right join t2 on (t1.id=t2.id);
+----+------+----+------+
| ID | NAME | ID | NAME |
+----+------+----+------+
| 1 | A1 | 1 | B2 |
| NULL | NULL | 3 | C2 |
| 6 | F1 | 6 | F2 |
| NULL | NULL | 9 | I2 |
+----+------+----+------+
4 rows in set (0.00 sec)