Mysql multi-table joint query

Many times in actual business we don’t just query a table.

In the e-commerce system, query which users have not purchased products.
The bank may query violation records, and at the same time query the user's
Query the winning information and the basic information of the winner.

The above is just a column situation, so we need to query the two tables together.

In the above business, multiple tables need to be joined together to query to get results, and the essence of multi-table joint query is: table connection.

Table connection

When you need to query fields in multiple tables, you can use table connection to achieve it. Table joins are divided into inner joins and outer joins.

Inner join: Join those records whose fields in the two tables have a join relationship that match the join relationship to form a record set.
Outer join: Other unmatched records will be selected and divided into outer left join and outer right join.

Before learning the experiment, I prepared two simulated data tables for everyone:

User table to store user information
Order table, which stores which user purchased which product

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

user table data is as follows:

##uid##1京天1234562 王小二2456673王宝强12355314Jing Boran123455Fan Bingbing黄晓明anglebabyTFBOYS##9An Xiaochao 12tfddwd10Gao Xiaofeng3124qwqw11李小强323fxfvdvd12李小超311aqqee13汉小平121rcfwrfq123123tcsd3cxvdfs

username

password

##5

5abcwa

abcdeef

caption

abcdwww

##14

宋小康

Tong Xiaogang

order_goods data is as follows:

##uidnamebuytime110Apple Mouse121231323iphone 12s123121241##3453iphone keyboard12123413##Note: is above 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.

oid


12	Sprite	13232333
15		##34242123

Inner connectionBasic syntax 1:

CategoryDetailed explanationBasic syntaxselect table 1.field [as alias], table n. field from table 1 [alias], table n where condition;select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user,order_goods where user.uid = order_goods.uid ;Query which users in the product table have purchased products and display the user information

Example

Example description

Note: In the following example, the from table uses table aliases.

Because the table name is too long, it is easy to make mistakes every time you write it. We can follow the table directly with an abbreviated English string. When splicing fields earlier, just use the abbreviation string.field.

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 | Gao Xiaofeng | 1 | 10 | Apple Mouse |
| 3 | Wang Baoqiang | 2 | 3 | iphone 12s |
| 12 | Li Xiaochao | 3 | 12 | Sprite |
| 15 | Tong Xiaogang | 4 | 15 | |
| 3 | Li Wenkai | 5 | 3 | iphone keyboard |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)

Basic syntax 2:

Category	Detailed explanation
Basic syntax	select table 1. field [as alias], table n. field from table 1 INNER JOIN table n on condition;
Example	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;
Example description	Query which users in the product table have purchased products and display the user information

The result is consistent with Basic Grammar 1.

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 | Gao Xiaofeng | 1 | 10 | Apple Mouse |
| 3 | Wang Baoqiang | 2 | 3 | iphone 12s |
| 12 | Li Xiaochao | 3 | 12 | Sprite |
| 15 | Tong Xiaogang | 4 | 15 | |
| 3 | Wang Baoqiang | 5 | 3 | iphone keyboard |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)

Outer connection

##ExplanationDetailed explanationBasic syntaxselect table 1. field [as alias], table n. field from table 1 LEFT JOIN table n on conditions;Exampleselect * from user left join order_goods on user.uid = order_goods.uid;Example description Take the left as Mainly, check which users have not purchased goods and display the user information

Outer connections are divided into left connections and right links. The specific definitions are as follows.

Left join: Contains all records in the left table even if there are no matching records in the right table

mysql> select * from user left join order_goods on user.uid = order_goods.uid;
+-----+-----------+------------+------+ ------+---------------+-----------+
| uid | username | password | oid | uid | name | buytime |
+-----+-----------+------------+------+----- -+---------------+----------+
| 10 | Gao Xiaofeng | 3124qwqw | 1 | 10 | Apple Mouse | 1212313 |
| 3 | Wang Baoqiang | 1235531 | 2 | 3 | iphone 12s | 123121241 |
| 12 | Li Xiaochao | 311aqqee | 3 | 12 | Sprite | 13232 333 |
| 15 | Tong Xiaogang | 3cxvdfs | 4 | 15 | 34242123 |
| 3 | Wang Baoqiang | 1235531 | 5 | 3 | iphone keyboard | 12123413 |
| 1 | Jing Tian | 123456 | NULL | NULL ULL 二 | 245667 | NULL | NULL | NULL | 4 | Jing Boran | 123455 | NULL | NULL | NULL | 5 | Fan Bingbing | 5ab cwa | NULL | NULL | NULL | | NULL |
| 6 | 黄晓明 | abcdeef | NULL | NULL | NULL | 7 | anglebaby | caption | NULL | NULL | NULL | 8 | TFBOYS abcd | www | NULL | NULL | NULL | NULL |
| 9 | An Xiaochao | 12tfddwd | NULL | NULL | NULL | 3 | Han Xiaoping | 121rcfwrfq | NULL | NULL | NULL | | NULL |
| 14 | Song Xiaokang | 123123tcsd | NULL | NULL | NULL | ------------+------+------+---------------+------- ----+
16 rows in set (0.00 sec)

Right join: contains all the records in the right table even the records that do not match it in the right table

Category	Detailed explanation
Basic syntax	select table 1.field [as alias], table n.field from table 1 right JOIN table n on condition;
Example	select * from user right join order_goods on user.uid = order_goods.uid;
Example description	Query which users in the product table have purchased products and display the user information

##mysql> select * from user right join order_goods on user.uid = order_goods.uid;

+------+-----------+----------+-----+ -----+---------------+----------+
| uid | username | password | oid | uid | name | buytime |
+------+-----------+----------+-----+-----+-- -------------+----------+
| 10 | Gao Xiaofeng | 3124qwqw | 1 | 10 | Apple Mouse | 1212313 |
| 3 | Wang Baoqiang | 1235531 | 2 | 3 | iphone 12s | 123121241 |
| 12 | Li Xiaochao | 311aqqee | 3 | 12 | Sprite | 13232333 |
| 15 | Tong Xiaogang | 3cxvdfs | 4 | 15 | 34242123 |
| 3 | Wang Baoqiang | 1235531 | 5 | 3 | iphone keyboard | 12123413 |
+-------+----------+-------- --+-----+-----+---------------+-----------+
5 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.

CategoryDetailed explanationBasic syntaxselect field from table where field in (condition)Example 1select * from user where uid in (1,3,4);Example 1 DescriptionQuery the specified user according to idExample 2select * from user where uid in ( select uid from order_goods);Example 2 DescriptionDisplay user information that has purchased goods##

Example 1:

mysql> select * from user where uid in (1,3,4);
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 1 | Jing Tian | 123456 |
| 3 | Wang Baoqiang | 1235531 |
| 4 | Jing Boran | 123455 |
+-----+-----------+----------+
3 rows in set (0.00 sec)

Example 2:

mysql> select * from user where uid in (select uid from order_goods) ;
+-----+-----------+----------+
| uid | username | password |
+-----+-----------+----------+
| 10 | Gao Xiaofeng | 3124qwqw |
| 3 | Wang Baoqiang | 1235531 |
| 12 | Li Xiaochao | 311aqqee |
| 15 | Tong Xiaogang | 3cxvdfs |
+-----+-----------+----------+
4 rows in set (0.00 sec)

##mysql> select * from emp where deptno in (select deptno from dept);

Record union

Use union and The union all keyword is used to query the data from two tables according to certain query conditions, and then merge the results and display them together. 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.

CategoryDetailed explanationBasic syntaxselect statement 1 union[all] select statement 2Exampleselect * from user where uid in (1,3,4);Example descriptionCombine the results of user information in the product table and user information in the user table

mysql> select uid from user union select uid from order_goods;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+
15 rows in set (0.00 sec)

Next Section

new file

<?php
echo "Hello Mysql";
?>

submit Reset Code

Automatic operation

Full Screen

None