Using connection queries in MySQL

Connection query: A connection that records multiple tables (which can be more than 2 sheets) (data stitching according to a specified condition);

The end result: the number of records is likely to change and the number of fields will increase (at least two tables are merged)!

The meaning of a connection query: When the user views the data, the data that needs to be displayed comes from multiple tables.

SQL divides connection queries into four categories: cross-joins , inner joins , outer joins , and Natural joins.

Create two tables as a contact before introduction (My_stu,my_class)

1. Cross-Connect

Cross join, which loops out each record from a table, each record goes to another table to match: The match is reserved (no conditional match), and the connection itself is incremented (reserved), and the resulting result is called Cartesian product.

Select *  from  Cross join My_class;

Attention:

The Cartesian product has no meaning: it should be avoided (cross-linking is useless). The value of cross-linking: ensuring the integrity of the connection structure

2. Internal connection

[Inner] Join, remove each record from the left table, and go to the right table to match all records: match must be a condition in the left table with the same in the right table will eventually retain the results, otherwise it is not preserved!

Basic syntax

The left table [inner] joins the right table on the left table. field = Right table. field; On indicates the connection condition: The condition field represents the same business meaning (such as my_stu.c_id and My_class.id).

Select *  from Inner Join  on = my_class.id;

Attention:

The inner connection can have no connection condition: There is no post on the content, this time the system will keep all the results (Cartesian product)!

Inner joins can also use where to replace the ON keyword (where no on efficiency is high)!

3. External connection

Outer JOIN, taking a table as the main, take out all the records inside, then each with another table to connect: No matter can match the conditions, will eventually retain: can match the correct reservation ; cannot match , the fields of the other tables are empty null.

There are two types of outer joins: One is the main table

Left JOIN: Outer join (left connection), left table to main table

Right join: Left OUTER join (right connection), right table for main table

Basic syntax:

Left table Left/right join right table on left table. field = Right table. field;

3.1 Left Connection

Select *  from  Left Join  on my_stu.c_id=my_class.id;

3.2 Right Connection

Select S.*as from and rightjoinsas on  = c.id;

Attention:

Although the left and right connections have the main table differences, the results are displayed: The data on the left table is on the left and the right table data is on the right.

The use of field aliases and table aliases: When querying data, different tables have the same name field, which needs to be distinguished by a table name, and the table name is too long, and you can usually use aliases.

4. Natural connection

Natural connection: Natural join, natural connection, is automatically match the connection condition: The system takes the field name as the match pattern (the same name segment as the condition , multiple same name fields as the condition, the connection will merge the same name field).

Natural connection: It can be divided into natural inner connection and natural outer connection .

4.1 Natural internal connections:

Left table natural join right table;

Select *  from join My_class;

4.2 Natural external connections

Left table natural left/right join right Table

Select *  from  Left join My_class;

Multi-table Connection: A table inner join B table on condition left join C table on condition ...

Execution order: A table is connected to the B table, to get a two-dimensional table, left to join the C table to form a two-dimensional table ...

Using connection queries in MySQL