What are the four connection methods of the database?

The four connection methods of the database are: 1. inner join; 2. outer join; 3. cross join; 4. natural join.

Inner join

Inner join ---The spliced ​​query results are internally connected, and the inner join format is used: [ inner] join ----inner can save

Result: Take out each record from the left table and match all records in the right table; the match must be a certain condition The result will be retained only if the left table is the same as the right table, otherwise it will not be retained;

Basic syntax: Left table [inner] join Right table on Left table. Field = Right table .Field;

Notes:

1. on represents the connection condition; the condition field represents the same business meaning (such as stu.cid and cla.id) student stu The class cid in the table and the id in the class cla table

2. Inner joins can have no connection conditions and no content after on. In this case, the system will retain all results (Cartesian set)

3. Where can be used instead of on for inner connections. Where is usually not used, because where is not as efficient as on. On means that the match ends when the first successful one is matched, and the others do not match; if not, there will be no matching and where will always match, and the judgment will be made.

4. Field aliases and table aliases are usually used when querying data. Different tables have fields with the same name. At this time, you need to add the table name to distinguish. If the table name is too long, use table aliases if you do not want to use the default. The field name can be set as a field alias via [as].

Outer join

Outer join--Using a certain table as the base table to perform related queries outer join

Result: Focus on a certain table, take out all the records in it, and then connect each record to another table. Regardless of whether it can match the conditions, it will be retained in the end. If it can match, it will be retained correctly; the fields that cannot match other tables will be set. Empty null

Classification:It is divided into two types: left join, right join, left join, left outer join---left join, with the left table as the main one, right join, right outer join---right Connection, mainly right table

Basic syntax: Left table left/right join Right table on Left table.Field = Right table.Field; ------Conditions must be present

Cross join

Cross join Connection query cross join:

Cross join does not have a WHERE clause, it returns all of the two tables being joined Cartesian product of data rows,

The number of data rows returned in the result set is equal to the number of data rows in the first table that meet the query conditions multiplied by the number of data rows in the second table that meet the query conditions.

natural join

natural join natural join

It is the system that automatically matches the connection conditions. The system uses the field name as the matching pattern (the field with the same name as the field , multiple fields with the same name are used as conditions)

Natural join: can be divided into natural inner join and natural outer join inner join natural left join natural left outer join

In fact: inner join and outer join All can simulate natural joins,

Use fields with the same name, merge fields left table left/right inner join right table on using (field name) ----- Use fields with the same name, merge fields

--Natural inner join select * from stu natural join cla;

-Natural left outer join select * from stu natural left join cla;

--Outer join simulates natural left outer joinselect * from stu left join cla using(id);

The above is the detailed content of What are the four connection methods of the database?. For more information, please follow other related articles on the PHP Chinese website!