A case study of join query for MySQL data query 04/05 Update SLTechnology News&Howtos

A case study of join query for MySQL data query

2025-04-05 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you the case of MySQL data query connection query, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Connection relationship is the main feature of relational database model. It is also the most important query, including inner join, outer join and so on.

Multiple table queries can be implemented through the join operator.

In the relational database management system, the relationship between the data does not need to be determined when the table is established, and all the information of an entity is often stored in a table. When querying data, the join operation is used to query the information of different entities stored in multiple tables. When there are fields with the same meaning in two or more tables, you can use these fields to make join queries on different tables.

Here are three ways to connect multiple tables:

(1) Internal join query

(2) external join query

(3) compound conditional join query

(1), inner join query (inner join)

The inner join uses the comparison operator to compare the data of certain columns between tables, and lists the data rows in these tables that match the join condition direction to form a new record, that is, in the inner join query, only records that meet the conditions can appear in the result relationship.

The following data table suppliers is created for demonstration:

Mysql > create table suppliers-> (- > s_id int not null auto_increment,-> s_name char (50) not null,-> s_city char (50) null,-> s_zip char (10) null,-> s_call char (50) not null,-> primary key (s_id)->) Query OK, 0 rows affected (0.17 sec) mysql > insert into suppliers-> values (101 sec FastFruit Inc.','Tianjin','300000','48075')-> FastFruit Inc.','Zhongshan'),-> (102 Inc.','Zhongshan' Inc.','Tianjin','300000','48075'),-> (103 camera Inc.','Zhongshan' 90046') 528437),-> (105 ~ good Set','Taiyuan','030000','22222'),-> (106 ~ ~ just Eat Ours','Beijing','010','45678'),-> (107 ~ ~ DK Inc','Zhengzhou','450000','33332') Query OK, 7 rows affected (0.07 sec) Records: 7 Duplicates: 0 Warnings: 0

[example 1] use inner join queries between the fruits table and the suppliers table.

You can see that both the fruits and suppliers tables have the same data type field s_id, and the two tables are linked through the s_id field.

Next, query the f_name and f_price fields from the fruits table, and query the s_id and sroomname _ SQL statements from the suppliers table as follows:

Note: because there is the same field s_id in the fruits table and the suppliers table, you need to fully qualify the table name in the format "table name. Column name" when comparing. If you only give the table name, MySQL will not know which field MySQL is referring to and return an error message.

[example 2] use inner join syntax to query the inner join between fruits table and suppliers table. The SQL statement is as follows:

Using the where clause to define the join condition is relatively simple and straightforward, while the inner join syntax is the standard specification of ANSI SQL, using the inner join join syntax ensures that the join condition is not forgotten, and the where clause can affect the performance of the query at some point.

If two tables involved in a join query are the same table, the name query is called a self-join query. Self-join is a special inner join, which means that the tables connected to each other are physically the same table, but can be logically divided into two tables.

[example 3] to inquire about the types of fruits provided by the fruit suppliers supplying fanciidfruit A1', the SQL statement is as follows:

Mysql > select f1.fprincipidreignedf1.ftreasname-> from fruits as f1gradefruits as f2-> where f1.s_id = f2.s_id and f2.f_id = 'a1' +-+-+ | f_id | f_name | +-+-+ | A1 | apple | | b1 | blackberry | | c0 | cherry | +-+-+ 3 rows in set (0.00 sec) (2), external connection query

The outer join query queries the associated rows in multiple tables.

When you join within, only the rows that meet the query and join criteria are returned in the query result set. However, sometimes you need to include data in rows that are not associated, that is, the query result set returned contains not only rows that meet the join criteria, but also all rows in the left table (left outer join or left join), right table (right outer join or right join), or two edge tables (full outer join). The outer connection is divided into left outer connection or left connection and right outer connection or right connection:

① left join: returns records that include all records in the left table and records with equal join fields in the right table.

② right join: returns records that include records in the right table that are equal to the join fields in the left table.

1. Connect to left join on the left

First create the table orders,SQL statement as follows

Mysql > create table orders-> (- > o_num int not null auto_increment,-> o_date datetime not null,-> c_id int not null,-> primary key (o_num)->) Query OK, 0 rows affected (0.11 sec) mysql > insert into orders-> values (30001-09-01),-> (30002-09-12) 10003)-> (30003-09-30) 10004),-> (30004-10-03)-> (30005) Query OK, 5 rows affected (0.06 sec) Records: 5 Duplicates: 0 Warnings: 0

[example] in customers table and orders table, query all customers, including those without orders. The SQL statement is as follows:

Mysql > select * from customers +-+ | c_id | c_name | c_address | c_city | c_zip | c _ contact | c_email | +-+ | 10001 | redhool | 200 Street | Tianjin | 300000 | LiMing | | LMing@163.com | | 10002 | Stars | 333 Fromage Lane | Dalian | 116000 | Zhangbo | Jerry@hotmail.com | | 10003 | Netbhood | 1 Sunny Place | Qingdao | 266000 | LuoCong | NULL | | 10004 | JOTO | 829 Riverside Drive | Haikou | 570000 | YangShan | sam@hotmail.com | +-| -- + 4 rows in set (0.00 sec) mysql > select * from orders +-+ | o_num | o_date | c_id | +-+ | 30001 | 2008-09-01 00:00:00 | 10001 | 30002 | 2008-09-12 00:00 : 00 | 10003 | | 30003 | 2008-09-3000: 00:00 | 10004 | | 30004 | 2008-10-03 00:00:00 | 10005 | 30005 | 10003-08 00:00:00 | 10001 | +-+ 5 rows in set (30003 sec) mysql > select customers.c_id Orders.o_num-> from customers left outer join orders-> on customers.c_id = orders.c_id +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | 10003 | 30002 | 10004 | 30003 | 10001 | 30005 | | 10002 | NULL | +-+-+ 5 rows in set (0.00 sec)

two。 Connect right join on the right

The right join is the reverse join of the left join, which returns all rows of the right table. If a row in the right table does not match in the left table, the left table returns a null value.

[example] in customers table and orders table, query all orders, including those without customers. The SQL statement is as follows:

Mysql > select customers.c_id, orders.o_num-> from customers right outer join orders-> on customers.c_id = orders.c_id +-+-+ | c_id | o_num | +-+-+ | 10001 | 30001 | 10003 | 30002 | 10004 | 30003 | NULL | 30004 | | 10001 | 30005 | +-+ 5 rows in set (0.00 sec) (3), compound conditional connection query

Compound conditional join query is to add filter conditions to limit the query results in the process of join query, so as to make the query results more accurate.

[example 1] in the customers table and orders table, use inner join syntax to query the customers table to summarize the order information of the customer whose ID is 10001. The SQL statement is as follows:

Mysql > select customers.c_id,orders.o_num-> from customers inner join orders-> on customers.c_id = orders.c_id and customers.c_id = 10001 sec + | c_id | o_num | +-+-+ | 10001 | 30001 | | 10001 | 30005 | +-+-+ 2 sec)

[example 2] use inner join syntax to query the inner join between fruits table and suppliers table, and sort the query results. The SQL statement is as follows:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.