How to realize multi-table join query by Mariadb 02/23 Update SLTechnology News&Howtos

How to realize multi-table join query by Mariadb

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Development >

Shulou(Shulou.com)06/01 Report--

This article mainly introduces the relevant knowledge of "how to achieve multi-table join query in Mariadb". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "how to achieve multi-table join query in Mariadb" can help you solve the problem.

Concept

Because we are using a relational database, each table represents a separate unit (object), and the other information involved in that unit (object) is usually stored in other tables, such as:

MariaDB [world] > DESC city +-+ | Field | Type | Null | Key | Default | Extra | +- -+ | ID | int (11) | NO | PRI | NULL | auto_increment | | Name | char (35) | NO | | CountryCode | char (3) | NO | MUL | | District | char (20) | NO | | Population | int (11) | NO | | 0 | | +-+-+ 5 rows in set (0.61 sec) MariaDB [world] > DESC countrylanguage | +-+ | Field | Type | Null | Key | Default | Extra | +-- -- + | CountryCode | char (3) | NO | PRI | | Language | char (30) | NO | PRI | | IsOfficial | enum ('T') 'F') | NO | | F | | Percentage | float (4 sec) | NO | | 0.0 | +-+-+ 4 sec)

For example, in the top two tables, if we want to know the language used in a city, it can be divided into two steps:

1. Query the city's CountryCode in the City table.

two。 Use the CountryCode you query to query the language used in the country in the CountryLanguage table.

Although it can be done in two steps, it requires two queries and two transmissions, and in terms of bandwidth and performance, we prefer Mysql (MariaDB) to help us do this, don't we?

JOIN: also called join, refers to joining two tables into a table according to certain rules, and concatenating different data (rows) in the two tables into a row.

Connections can also be divided into the following categories:

Internal connection

External connection

Left external connection and right external connection

Cross connection

In a join query, a column may appear in multiple tables. In order to avoid ambiguity, the column name is usually prefixed with a table name or table alias (for example: s.sid, x.sid)-using a table alias as a prefix can make the SQL code shorter and use less memory (for example: stu xuanke as x).

Internal connection

The inner join syntax is as follows:

SELECT tb1_name.column,tb2_name.column FROM tb1 INNER JOIN tb2 ON constraint condition

Query what languages each city may use:

How does the internal connection work

Let's take a look at how the data are connected, as shown in the following picture (enlarged):

So the so-called inner join is to join only the qualified rows in multiple tables and return the results.

For example, three tables are joined together:

MariaDB [world] > SELECT * FROM city INNER JOIN countrylanguage INNER JOIN country ON city.CountryCode = countrylanguage.CountryCode AND city.CountryCode = country.Code WHERE city.Name='Kabul'\ G * * 1. Row * * ID: 1 Name: Kabul CountryCode: AFG District: Kabol Population: 1780000 CountryCode: AFG Language: Balochi IsOfficial: F Percentage: 0.9 Code: AFG Name: Afghanistan Continent: Asia Region: Southern and Central Asia SurfaceArea: 652090.00 IndepYear: 1919 Population: 22720000LifeExpectancy: 45.9 GNP: 5976.00 GNPOld: NULL LocalName: Afganistan/AfqanestanGovernmentForm: Islamic Emirate HeadOfState: Mohammad Omar Capital: 1 Code2: AF.... Only the first record 5 rows in set (0.01 sec) was intercepted

Here, we recommend the standard writing method of SQL, that is, the following format:

SELECT tb1_name.column,tb2_name.column FROM tb1 INNER JOIN tb2 ON constraint condition

Why? Because after the ON clause, you can filter the tables connected by the WHERE clause, and the syntax structure is clearer, isn't it?

External connection

Using an inner join joins qualified rows in multiple tables, while ineligible rows are ignored, while outer joins output some unqualified rows as well.

For example, we have the following data:

We have the following requirements to display the user and the name of the user's department. According to the internal connection we mentioned above, we can write the following statement:

But is the result right? Although our Mr. mark has not been assigned to any department, we have to show him, right?

At this point, external connections come in handy:

The table on the left side of JOIN is called the left table, while the table on the right side is called the right table.

Left outside connection, FROM tb1_name LEFT OUTER JOIN tb2_name

* * in addition to displaying the rows that meet the criteria, all the rows in the left table are displayed, while the fields in the right table are all NULL. * * as follows:

As the name implies, all rows in the right table that do not meet the join criteria are displayed. The fields in the left table are all NULL, as shown below:

Cross connection and Cartesian product

When the join result of a table with no join condition is a Cartesian product, the number of rows retrieved will be the number of rows in the first table multiplied by the number of rows in the second table, as shown in the following figure:

Mariadb Learning Summary (9): multi-table join query Mariadb Learning Summary (9): multi-table join query

If it is necessary to use Cartesian products, you can use cross connections (CROSS JOIN) as shown in the following example:

Conditional filtering for multi-table joins

When we want to filter the results of a multi-table join query, we can put the filter condition on the ON clause or the WHERE clause, and the results may be different between the ON clause and the WHERE clause.

* * ON clause for filter condition: use AND logic and operation to put the filter condition before or after the connection condition-> filter the condition before the connection. * filter conditions put WHERE clause: use a separate WHERE clause for data filtering-> conditional filtering after connection. **

For inner joins, the filtering conditions are the same in the ON clause or the WHERE clause, and it is recommended to filter in the on clause.

For external connections, there are the following references:

/ / filter condition before or after connection condition MariaDB [world] > SELECT user.name,department.name FROM user LEFT OUTER JOIN department ON user.name='mars' AND user.deptid = department.id;MariaDB [world] > SELECT user.name,department.name FROM user LEFT OUTER JOIN department ON user.deptid = department.id AND user.name='mars' +-+-+ | name | name | +-+-+ | test | NULL | | test1 | NULL | | lucy | NULL | | mars | administra | | mark | NULL | +-+-+ 5 rows in set (0.00 sec) / / because ON user.name= 'mars' turns the left table into a piece of data But AND requires that the second expression is also true, user.deptid = department.id This only filters the deptid of mars and the corresponding id in its department table, but the left connection requires the left table to display all the data, so the field in the right table puts the WHERE clause for the NULL// filter condition, because it is filtered after the connection, that is, the new table generated by the connection is filtered, so only the data that meets the criteria will be displayed. MariaDB [world] > SELECT user.name,department.name FROM user LEFT OUTER JOIN department ON user.deptid = department.id WHERE user.name = 'mars';+-+-+ | name | name | +-+ | mars | administra | +-+-+ 1 row in set (0.00 sec)

Renaming of tables

In multi-table join queries, tables are usually renamed, using the AS keyword like column renaming, and table renaming is mainly convenient when referencing tables.

Rename the user table to U and department to D as shown below:

The use of multi-table join and aggregate function

To put it bluntly, a multi-table join query is to generate a temporary new table, so using grouping and aggregation functions is as simple as usual, see the following example:

Count the number of people in each department:

Count the number of official languages that each city can speak:

MariaDB [world] > SELECT C. name count (CL.Language) FROM city AS C INNER JOIN countrylanguage AS CL ON C.CountryCode = CL.CountryCode AND CL.IsOfficial ='T 'GROUP BY C.Name +-+ | Name | COUNT (CL.Language) | +-+-+ | A Coru ñ a (La Coru ñ a) | | 1 | | Aachen | 1 |. | Alicante [Alacant] | 1 | | Aligarh | 1 | +-| -this is the end of the introduction to "how to implement multi-table join query by Mariadb". Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.