I. An Overview of Join's Grammar

join is used to link fields in multiple tables. The grammar is as follows:

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

Table 1: left table; table 2: right table.

JOIN can be roughly divided into three categories according to its functions:

INNER JOIN (inner join, or equivalent join): Gets records of connection matching relationships in two tables.

LEFT JOIN (Left Connection): Gets a complete record of the left table (table1), that is, the right table (table2) has no matching record.

RIGHT JOIN (Right Connection): Contrary to LEFT JOIN, a complete record of the right table (table2) is obtained, that is, the left table (table1) has no matching corresponding record.

Note: mysql does not support Full join, but you can combine LEFT JOIN with RIGHT JOIN to simulate FULL join by using UNION keywords.

Next, a column is given to explain the following categories. The following two tables (A,B)

mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
+----+-----------+-------------+
| id | name       | name             |
+----+-----------+-------------+
|  1 | Pirate       | Rutabaga      |
|  2 | Monkey    | Pirate            |
|  3 | Ninja         | Darth Vader |
|  4 | Spaghetti  | Ninja             |
+----+-----------+-------------+
4 rows in set (0.00 sec)

II. Inner join

Internal join, also known as equivalent join, produces a set of data that simultaneously conforms to A and B.

mysql> select * from A inner join B on A.name = B.name;
+----+--------+----+--------+
| id | name   | id | name   |
+----+--------+----+--------+
|  1 | Pirate |  2 | Pirate |
|  3 | Ninja  |  4 | Ninja  |
+----+--------+----+--------+

III. Left join

mysql> select * from A left join B on A.name = B.name;
#Or:select * from A left outer join B on A.name = B.name;

+----+-----------+------+--------+
| id | name      | id   | name   |
+----+-----------+------+--------+
|  1 | Pirate    |    2 | Pirate |
|  2 | Monkey    | NULL | NULL   |
|  3 | Ninja     |    4 | Ninja  |
|  4 | Spaghetti | NULL | NULL   |
+----+-----------+------+--------+
4 rows in set (0.00 sec)

Left join (or left outer join: Both are equivalent in Mysql. It is recommended to use left join.) Left join generates a complete set of records from the left table (A) and matches the records (right table (B). If there is no match, the right side will contain null.

If you want to generate only a set of records from the left table (A), but not from the right table (B), you can do so by setting where statement, as follows:

mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
+----+-----------+------+------+
| id | name      | id   | name |
+----+-----------+------+------+
|  2 | Monkey    | NULL | NULL |
|  4 | Spaghetti | NULL | NULL |
+----+-----------+------+------+
2 rows in set (0.00 sec)

Similarly, inner join can also be simulated.

mysql> select * from A left join B on A.name=B.name where A.id is not null and B.id is not null;
+----+--------+------+--------+
| id | name   | id   | name   |
+----+--------+------+--------+
|  1 | Pirate |    2 | Pirate |
|  3 | Ninja  |    4 | Ninja  |
+----+--------+------+--------+
2 rows in set (0.00 sec)

Difference sets:

According to the example above, we can find the difference set as follows:

SELECT * FROM A LEFT JOIN B ON A.name = B.name
WHERE B.id IS NULL
union
SELECT * FROM A right JOIN B ON A.name = B.name
WHERE A.id IS NULL;
# Result
    +------+-----------+------+-------------+
| id   | name      | id   | name        |
+------+-----------+------+-------------+
|    2 | Monkey    | NULL | NULL        |
|    4 | Spaghetti | NULL | NULL        |
| NULL | NULL      |    1 | Rutabaga    |
| NULL | NULL      |    3 | Darth Vader |
+------+-----------+------+-------------+

IV. Right join

mysql> select * from A right join B on A.name = B.name;
+------+--------+----+-------------+
| id   | name   | id | name        |
+------+--------+----+-------------+
| NULL | NULL   |  1 | Rutabaga    |
|    1 | Pirate |  2 | Pirate      |
| NULL | NULL   |  3 | Darth Vader |
|    3 | Ninja  |  4 | Ninja       |
+------+--------+----+-------------+
4 rows in set (0.00 sec)

With left join.

V. Cross join

cross join: cross join, the result is the product of two tables, namely Cartesian product

Descartes product is also called direct product. Assuming that set A={a,b} and set B={0,1,2}, the Cartesian product of the two sets is {(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}. It can be extended to multiple sets. Similarly, if A represents a collection of students in a school and B represents a collection of all courses in that school, then the Cartesian product of A and B represents all possible courses.

mysql> select * from A cross join B;
+----+-----------+----+-------------+
| id | name      | id | name        |
+----+-----------+----+-------------+
|  1 | Pirate    |  1 | Rutabaga    |
|  2 | Monkey    |  1 | Rutabaga    |
|  3 | Ninja     |  1 | Rutabaga    |
|  4 | Spaghetti |  1 | Rutabaga    |
|  1 | Pirate    |  2 | Pirate      |
|  2 | Monkey    |  2 | Pirate      |
|  3 | Ninja     |  2 | Pirate      |
|  4 | Spaghetti |  2 | Pirate      |
|  1 | Pirate    |  3 | Darth Vader |
|  2 | Monkey    |  3 | Darth Vader |
|  3 | Ninja     |  3 | Darth Vader |
|  4 | Spaghetti |  3 | Darth Vader |
|  1 | Pirate    |  4 | Ninja       |
|  2 | Monkey    |  4 | Ninja       |
|  3 | Ninja     |  4 | Ninja       |
|  4 | Spaghetti |  4 | Ninja       |
+----+-----------+----+-------------+
16 rows in set (0.00 sec)

#Re-execution: MySQL > select * from A inner join B; Have a try

#Execute MySQL > select * from A cross join B on A. name = B. name; try

In fact, CROSS JOIN and INNER JOIN perform the same in MySQL (MySQL only). The results obtained without specifying ON conditions are Cartesian products, and vice versa, the results of two tables are exactly matched.
INNER JOIN and CROSS JOIN can omit INNER or CROSS keywords, so the following SQL effect is the same:

... FROM table1 INNER JOIN table2
... FROM table1 CROSS JOIN table2
... FROM table1 JOIN table2

6. Full join

mysql> select * from A left join B on B.name = A.name 
    -> union 
    -> select * from A right join B on B.name = A.name;
+------+-----------+------+-------------+
| id   | name      | id   | name        |
+------+-----------+------+-------------+
|    1 | Pirate    |    2 | Pirate      |
|    2 | Monkey    | NULL | NULL        |
|    3 | Ninja     |    4 | Ninja       |
|    4 | Spaghetti | NULL | NULL        |
| NULL | NULL      |    1 | Rutabaga    |
| NULL | NULL      |    3 | Darth Vader |
+------+-----------+------+-------------+
6 rows in set (0.00 sec)

All records generated by full connection (matching records of both sides) are shown in tables A and B. If there is no match, null will be included opposite.

Original address: http://www.cnblogs.com/BeginMan/p/3754322.html