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 |

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

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

| 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);