Multiple table joint query
Table connection
When you need to query fields in multiple tables, This can be achieved using table joins. Table joins are divided into inner joins and outer joins.
1. Inner join: The records whose fields in the two tables have a connection relationship and match the connection relationship form a connection of the record set.
2. Outer join: Other unmatched records will be selected, divided into outer left join and outer right join.
Before learning the experiment, I prepared two simulated data tables for everyone:
1. User table, which stores user information
2. Order table, which store Which product has the user purchased?
User table creation statement
CREATE TABLE IF NOT EXISTS user ( uid int(11) NOT NULL, username varchar(30) NOT NULL, password char(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;rrree
The user table data is as follows:
Note: In the above table, the uid in the order_goods table refers to the uid field in the user table. In the above table, the data row with oid is 1 and the user with uid is 10. For the user with uid 10 in the user table: Gao Xiaofeng. The user purchased an Apple mouse. The purchase time buytime is a unix timestamp.
Inner join
Note: In the following example, the from table uses a table alias.
Because the table name is too long, it is easy to make mistakes every time you write it. We can directly follow the table with an abbreviated English string. When splicing fields earlier, just use the abbreviation string.field.
CREATE TABLE IF NOT EXISTS order_goods ( oid int(11) NOT NULL, uid int(11) NOT NULL, name varchar(50) NOT NULL, buytime int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Basic Grammar 2:
The result is consistent with Basic Grammar 1.
mysql> select u.uid ,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o where u.uid = o.uid; +-----+-----------+-----+-----+---------------+ | uid | username | oid | uid | shopname | +-----+-----------+-----+-----+---------------+ | 10 | 高小峰 | 1 | 10 | 苹果鼠标 | | 3 | 李文凯 | 2 | 3 | iphone 12s | | 12 | 李小超 | 3 | 12 | 雪碧 | | 15 | 佟小刚 | 4 | 15 | | | 3 | 李文凯 | 5 | 3 | iphone 键盘 | +-----+-----------+-----+-----+---------------+ 5 rows in set (0.00 sec)
Outer join
mysql> select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on user.uid = order_goods.uid; +-----+-----------+-----+-----+---------------+ | uid | username | oid | uid | shopname | +-----+-----------+-----+-----+---------------+ | 10 | 高小峰 | 1 | 10 | 苹果鼠标 | | 3 | 李文凯 | 2 | 3 | iphone 12s | | 12 | 李小超 | 3 | 12 | 雪碧 | | 15 | 佟小刚 | 4 | 15 | | | 3 | 李文凯 | 5 | 3 | iphone 键盘 | +-----+-----------+-----+-----+---------------+ 5 rows in set (0.00 sec)Right join: contains all the records in the right table even the right table There is no matching record in
mysql> select * from user left join order_goods on user.uid = order_goods.uid; +-----+-----------+------------+------+------+---------------+-----------+ | uid | username | password | oid | uid | name | buytime | +-----+-----------+------------+------+------+---------------+-----------+ | 10 | 高小峰 | 3124qwqw | 1 | 10 | 苹果鼠标 | 1212313 | | 3 | 李文凯 | 1235531 | 2 | 3 | iphone 12s | 123121241 | | 12 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 | | 15 | 佟小刚 | 3cxvdfs | 4 | 15 | | 34242123 | | 3 | 李文凯 | 1235531 | 5 | 3 | iphone 键盘 | 12123413 | | 1 | 景甜 | 123456 | NULL | NULL | NULL | NULL | | 2 | 王小二 | 245667 | NULL | NULL | NULL | NULL | | 4 | 井柏然 | 123455 | NULL | NULL | NULL | NULL | | 5 | 范冰冰 | 5abcwa | NULL | NULL | NULL | NULL | | 6 | 黄晓明 | abcdeef | NULL | NULL | NULL | NULL | | 7 | anglebaby | caption | NULL | NULL | NULL | NULL | | 8 | TFBOYS | abcdwww | NULL | NULL | NULL | NULL | | 9 | 安小超 | 12tfddwd | NULL | NULL | NULL | NULL | | 11 | 李小强 | 323fxfvdvd | NULL | NULL | NULL | NULL | | 13 | 韩小平 | 121rcfwrfq | NULL | NULL | NULL | NULL | | 14 | 宋小康 | 123123tcsd | NULL | NULL | NULL | NULL | +-----+-----------+------------+------+------+---------------+-----------+ 16 rows in set (0.00 sec)
Subquery
Sometimes, when we query, the required condition is the result of another select statement, then we need to use a subquery. Keywords used for subqueries include in, not in, =, !=, exists, not exists, etc.
mysql> select * from user right join order_goods on user.uid = order_goods.uid; +------+-----------+----------+-----+-----+---------------+-----------+ | uid | username | password | oid | uid | name | buytime | +------+-----------+----------+-----+-----+---------------+-----------+ | 10 | 高小峰 | 3124qwqw | 1 | 10 | 苹果鼠标 | 1212313 | | 3 | 李文凯 | 1235531 | 2 | 3 | iphone 12s | 123121241 | | 12 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 | | 15 | 佟小刚 | 3cxvdfs | 4 | 15 | | 34242123 | | 3 | 李文凯 | 1235531 | 5 | 3 | iphone 键盘 | 12123413 | +------+-----------+----------+-----+-----+---------------+-----------+ 5 rows in set (0.00 sec)Example 2:
mysql> select * from user where uid in (1,3,4); +-----+-----------+----------+ | uid | username | password | +-----+-----------+----------+ | 1 | 景甜 | 123456 | | 3 | 李文凯 | 1235531 | | 4 | 井柏然 | 123455 | +-----+-----------+----------+ 3 rows in set (0.00 sec)
mysql> select * from user where uid in (select uid from order_goods); +-----+-----------+----------+ | uid | username | password | +-----+-----------+----------+ | 10 | 高小峰 | 3124qwqw | | 3 | 李文凯 | 1235531 | | 12 | 李小超 | 311aqqee | | 15 | 佟小刚 | 3cxvdfs | +-----+-----------+----------+ 4 rows in set (0.00 sec)
Record union
Use the union and union all keywords to query the data from the two tables according to certain query conditions, and then merge the results together for display. The main difference between the two is that the results are directly merged together, while union is the result of performing a distinct operation on the results after union all and removing duplicate records.
mysql> select * from emp where deptno in (select deptno from dept);
