sql json brief introduction

Preface

This article still adheres to the previous writing style, using simple and easy-to-understand examples to help everyone understand the differences between various joins.

　Why do we need to join

　Why do we need to join? Join in Chinese means connection, and connection means association, which is to associate one table with multiple tables. When dealing with database tables, we often find that we need to obtain information from multiple tables, assemble multiple field data from multiple tables, and then return it to the caller. Therefore, the premise of join is that there must be related fields between these tables.

Classification of join

Join is divided into two types, inner join and outer join. Outer join is divided into three types, left outer join, right outer join, full outer join. In addition, left outer join is also referred to as left join for short. That is, the well-known left join.

Join is divided into two types, inner join and outer join. Outer join is divided into three types, left outer join, right outer join, full outer join. In addition, left outer join is also known as left join for short. left join.

The differences between various joins

Before introducing the differences between various joins, let’s take a look at a simple example:

Scene description:

In the Internet era, everyone likes to shop online, especially Taobao and JD.com. So the scene we chose is also familiar to everyone in online shopping. This is a story about a person and what products he bought in the mall;

In response to the above requirements, we created two tables, tb_person and tb_order, where tb_person is a description of this person, and tb_order is about his purchase A description of the product.

Our table structure is very simple. tb_person only needs to know who the person is, so there are only three fields id, firstname (first name) and lastname (surname). Similarly, tb_order is also very simple. We only need to know who bought it. What product, so only 3 fields are needed, namely oid, oname (product name), pid (buyer number).

　tb_person:

+-----------+-------------+------+-----+---- -----+----------------+

| Field | Type | Null | Key | Default | Extra |

+--------- --+-------------+------+-----+---------+---------- ------+

| pid | int(11) | NO | PRI | NULL | auto_increment |

| firstname | varchar(50) | YES | | NULL |

| lastname | varchar(50) | YES | | NULL | --------+----------------+

tb_order:

+------+--------- ----+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-------------+-------+------+-- -------+----------------+

| oid | int(11) | NO | PRI | NULL | auto_increment |

| oname | varchar(50) | YES | | int(11) | YES | | NULL |

+-------+-------------+------+-----+---------+--- -------------+

Next, we write some sample data to the above two tables:

　data in tb_person:

+-----+----- ------+----------+

| pid | firstname | lastname |

+-----+-----------+-- --------+

| 1 | andy | chen |

| 2 | irri | wan |

| 3 | abby | sun |

+-----+----- ------+----------+

There are three people in the tb_person table, namely andy Chen, irri Wan, abby Sun;

　　data in tb_order:

+--- --+----------+------+

| oid | oname | pid |

+-----+----------+ ------+

| 1 | book | 1 |

| 2 | phone | 1 |

| 3 | computer | 4 |

+-----+------- ---+------+

tb_order table records 3 pieces of data, the person number is 1, that is, Andy Chen bought two items, namely book and phone, and there is another person number 4. A person bought a product, computer. You may have questions about this, why is there no person with personnel number 4 in the tb_person table? Here we assume that because there are many registered users, we have adopted the user table strategy, so the user with personnel number 4 may be in another personnel table.

　From the previous description, we know that if you want to join between tables, there must be an associated field. In the above example, we see that the associated field is pid.

　According to the two tables tb_person and tb_order, we can see three situations:

The person in the person table purchased the product, that is, there is a record of the user’s product purchase in the order table, and we can query it from this table Find out which products the user has purchased. For example, Andy Chen purchased two products: book and phone. That is, the pid exists in both the tb_person and tb_order tables. People in the

person table have not purchased the products, such as irri Wan and abby Sun. The two users did not purchase any goods, that is, the pid only exists in the tb_person table;

The user who purchased the goods in the order table cannot find a record in the person table. For example, the user with pid 4 purchased a computer but in the tb_person table There is no record of the user in the tb_order table, that is, the pid only exists in the tb_order table;

Understanding the above three situations is very helpful for us to understand the join. Next, we will analyze the differences of each join in detail:

INNER JOIN

　The so-called inner join means the situation 1 we mentioned earlier, pid must exist in both tables tb_person and tb_order;

MariaDB [demo]> SELECT p.pid, p.firstname, o.oname
    -> FROM tb_person p
    -> INNER JOIN tb_order o
    -> ON p.pid=o.pid;

+-----+-----------+-- -----+

| pid | firstname | oname |

+-----+-----------+-------+

| 1 | andy | book |

| 1 | andy | phone |

+-----+----------+-------+

LEFT JOIN

　tb_person LEFT JOIN tb_order means the union of case 1 and case 2 above. The result set of LEFT JOIN not only contains the results of INNER JOIN, but also contains the set of all users in tb_person who did not purchase any goods.

MariaDB [demo]> SELECT p.pid, p.firstname, o.oname
    -> FROM tb_person p
    -> LEFT JOIN tb_order o
    -> ON p.pid=o.pid;

+-----+-----------+-------+

| pid | firstname | oname |

+-----+-- ---------+-------+

| 1 | andy | book |

|   1 | andy      | phone |

|   2 | irri      | NULL  |

|   3 | abby      | NULL  |

+-----+-----------+-------+

　RIGHT JOIN

　　tb_person RIGHT JOIN tb_order的意思是上述情形1和情形3的并集。RIGHT JOIN的结果集不仅包含INNER JOIN的结果，而且还包含所有tb_order中所有已经购买商品的用户但该用户记录不存在于tb_person表。

MariaDB [demo]> SELECT p.pid, p.firstname, o.oname
    -> FROM tb_person p
    -> RIGHT JOIN tb_order o
    -> ON p.pid=o.pid;

+------+-----------+----------+

| pid  | firstname | oname    |

+------+-----------+----------+

|    1 | andy      | book     |

|    1 | andy      | phone    |

| NULL | NULL      | computer |

+------+-----------+----------+

FULL JOIN

　　故名思议，FULL JOIN就是上述情形1，2，3的并集了，但是mysql数据库不支持full join查询，所以我们只能LEFT JOIN union RIGHT JOIN，才能得到FULL JOIN的结果。

MariaDB [demo]> SELECT p.pid, p.firstname, o.oname
    -> FROM tb_person p
    -> LEFT JOIN tb_order o
    -> ON p.pid=o.pid
    -> UNION
    -> SELECT p.pid, p.firstname, o.oname
    -> FROM tb_person p
    -> RIGHT JOIN tb_order o
    -> ON p.pid=o.pid;

+------+-----------+----------+

| pid  | firstname | oname    |

+------+-----------+----------+

|    1 | andy      | book     |

|    1 | andy      | phone    |

|    2 | irri      | NULL     |

|    3 | abby      | NULL     |

| NULL | NULL      | computer |

+------+-----------+----------+

　注：我们上述的sql语句全部基于mysql数据库执行。

　总结

　　本文主要描述了sql join的分类以及各种join的区别，通过简单的示例，让大家更清晰的去了解他们。至于什么时候使用join要视具体的情况而定，根据不同的需求采用不同的策略。

　　非常感谢大家的热心回复，可能有些问题的探讨超出了本文的范畴，但是非常乐意大家提出问题，然后大家一起去探索去发现。

　引用

　　NULL

　附件

　demo.sql文件

create database demo;
use demo;
 
create table tb_person (
    pid int(11) auto_increment,
    firstname varchar(50),
    lastname varchar(50),
    primary key(pid)
);
 
create table tb_order (
    oid int(11) auto_increment,
    oname varchar(50),
    pid int(11),
    primary key(oid)
);
 
insert into tb_person(firstname, lastname) values('andy','chen');
insert into tb_person(firstname, lastname) values('irri','wan');
insert into tb_person(firstname, lastname) values('abby','sun');
 
insert into tb_order(oname, pid) values('book', 1);
insert into tb_order(oname, pid) values('phone', 1);
insert into tb_order(oname, pid) values('computer', 4);