MariaDB SQL INNER JOIN

Basic concept

The basic concept of relational tables and relational database design.

The next section does not cover all of the above topics, but it is enough to get you into the back of the study.

Suppose you have a database table that contains a product category, and each row contains a category item. The information that can be stored in each category includes product introductions and prices, as well as vendor information for the company that produces the product.

Where are the suppliers ' information stored in many different categories of products provided by the same supplier? You will not store supplier information and product information together for the following reasons.

1. Because for each product, the supplier information is the same, repeating the same information for each product is a waste of time and storage space.
2. If the supplier's information changes, you will have to update each stored vendor information.
3. When data is duplicated (that is, supplier information and product information are mixed together), the probability of entering data in exactly the same way each time is very low. Inconsistent data cannot be used in a report.

When you design a relational table, you split the information into multiple tables, each containing a data type that is linked to the table by the same value (that is, relationship design "relationships").

The above?? Example can create two tables, one to store supplier information vendors, and another to store product information products.

The Vendors table contains all the information about the vendor, each row in the table represents a vendor, and there is a unique identifier that identifies the vendor. This value is called the primary key (primary key), which can be called a vendor ID, or other unique value.

The Products table stores only product information, and there is no vendor other information other than the vendor ID. The vendor ID is called a foreign key, which, through it, correlates the Venders table box Products table, and this vendor ID allows you to find the appropriate vendor details through the Vendor table.

The foreign key (Foreign key) contains the primary key values from other tables in one column of the table, which defines the relationship between the table and tables.

The advantages of this are:

• Supplier information is never duplicated, so no time and space is wasted
• If the vendor information changes, you can update the records in the Vendors table. The data in the corresponding table does not change
• Because there is no duplication of data, the data used is clearly consistent, making data reports and operations easier

Scalability (scale) can be processed as the amount of data grows, without failure. A well-designed relational database or application is known as extensibility.

Create a connection

Creating a connection is simple, you must include all of the specified tables, and explain how they relate .

SELECT  vend_nameprod_name, Prod_price   from  Vendors  Products WHERE vendors.vend_id = products.vend_id ORDER  by Vend_name Prod_name;

Examples (the following are two different tables in different libraries):

Mysql> SelectAppkeyinfo.appkey, Businessid,appname, InfoType, Appkeyinfo.remark fromAppkeyinfo,crowdrewards.businessinfowhereAppkeyinfo.appkey=Businessinfo.appkey andInfoType=4 Order  byAppkeyinfo.iddescLimitTen;+------------+------------+---------+----------+-----------------------------------------+|AppKey|Businessid|AppName|InfoType|Remark|+------------+------------+---------+----------+-----------------------------------------+| 1194641763 | 133        |         | 4        |Zhiyonginfom|| 3217703708 |  the        |         | 4        |Test using|| 1029667166 | 0          |         | 4        |Sichuanyinyue|| 3927050004 |  the        |         | 4        | 546546546                               || 1966753355 | 129        |         | 4        |King|| 2740425196 |  -        |         | 4        | 666663321                               || 2496853641 | 127        |         | 4        | 6666633                                 || 751377797  | 126        |         | 4        | 66666                                   || 1031297745 |  the        |         | 4        |Qqqaaa|| 2061454479 | 124        |         | 4        |Qqq|+------------+------------+---------+----------+-----------------------------------------+TenRowsinch Set(0.08Sec

which

desc appkeyinfo;

+-----------------+---------------------+------+-----+---------+----------------+|Field|Type| Null | Key | Default |Extra|+-----------------+---------------------+------+-----+---------+----------------+|Id| int(Ten) unsigned|NO|Pri| NULL    |Auto_increment||AppKey| int(Ten) unsigned|NO|UNI| 0       |                ||AppName| varchar( +)|NO|     |         |                ||Remark| varchar( +)|NO|     |         |                ||InfoType| tinyint(4) unsigned|NO|     | 0       |                ||Applogo| varchar( -)|NO|     |         |                |+-----------------+---------------------+------+-----+---------+----------------+6 rowsinch Set(0.06Sec

desc Businessdemo;

+-------------------+------------------------+------+-----+---------+----------------+|Field|Type| Null | Key | Default |Extra|+-------------------+------------------------+------+-----+---------+----------------+|Id| int(Ten) unsigned|NO|Pri| NULL    |Auto_increment||Name| varchar( -)|NO|     |         |                ||AppKey| int(Ten) unsigned|NO|     | 0       |                ||Businessid| int(Ten) unsigned|NO|     | 0       |                ||Remark| varchar( -)|NO|     |         |                |+-------------------+------------------------+------+-----+---------+----------------+5 rowsinch Set(0.04Sec

It may seem strange to use where to set up the connection relationship, but there is a good reason to support it in practice. Remember that when a table is connected by a SELECT statement, the organization of the relationship is dynamic. ... In fact, you compare each row in the first table to each row in the second table, where in fact it is a filter that only contains rows that match the criteria for making the filter (the join condition). there is no WHERE clause, and each row of the first table matches each of the rows in the second table, regardless of whether they are logical or not.

The Cartesian product (Cartesian product) does not return results for table relationships that use join conditions. The number of rows returned when the first table is multiplied by the number of rows in the second table.

Do not forget the WHERE clause, and make sure that all connections contain a WHERE clause, otherwise MARIADB will return more data than you need. Similarly, make sure that the WHERE clause is used correctly, and that the wrong filter condition will cause MARIADB to return incorrect data.

Cross-Joins This is the connection type of the Cartesian product.

SELECT Vend_name, Prod_name, Prod_price  from Vendors, products ORDER  by Vend_name, Prod_name;

Internal connection

SELECT Vend_name, Prod_name, Prod_price  from INNER JOIN  Products  on = products.vend_id;

In addition, cross-Library connections

Select
 from Inner  databasename2.tablename2
 on Appkeyinfo.appkey=DataBaseName2.tableName2.appKey;

equivalent Connection (equiljoin) a connection that is based on a test that is equal to two tables. This connection is also referred to as an inner connection.

The two table relationships here are specified as inner joins. When using this syntax, the join condition uses on instead of the WHERE clause, and the conditions passed to on are the same as those passed to the WHERE clause.

Connecting multiple tables

SQL does not explicitly limit the number of table joins in a SELECT statement.

The basic rules for creating a connection are still the same, listing all the tables first, and then defining the relationships for the tables.

SELECT Prod_name, Vend_name, Prod_price, quantity  from OrderItems, products, vendors WHERE = vendors.vend_id      and = products.prod_id      and = 20005;

MariaDB SQL INNER JOIN