MYSQL/MARIADB Learning Record-connection query (join)

//The data tables used in this article
Id_p primary key in Persons table
Id_o primary key in the Orders table, id_p as foreign key reference persons id_p in table
Mysql>Select* fromPersonsSelect* fromorders;+------+----------+-----------+----------------+----------+| id_p | LastName | FirstName | Address | City |+------+----------+-----------+----------------+----------+|1| Adams | John | Oxford Street | London | |2| Bush | George | Fifth Avenue | New York | |3| Carter | Thomas | Changan Street | BEIJING |+------+----------+-----------+----------------+----------+3Rowsinch Set(0.05sec)+------+---------+------+| Id_o | OrderNo | Id_p |+------+---------+------+|1|77895|3||2|44678|3||3|22456|1||4|24562|1||5|34764| $|+------+---------+------+5Rowsinch Set(0.05Sec

1. Inner connection [inner] join:

An inner connection is also called a connection, or a natural connection:

//inner can be deletedMysql>SelectLastname,firstname,orderno fromPersons inner join orders on persons.id_p=orders.id_p ORDER BY LastName;+----------+-----------+---------+| LastName | FirstName | OrderNo |+----------+-----------+---------+| Adams | John |24562|| Adams | John |22456|| Carter | Thomas |77895|| Carter | Thomas |44678|+----------+-----------+---------+//use where to achieve the same effectMysql>SelectLastname,firstname,orderno fromPersons,orderswherepersons.id_p=orders.id_p;+----------+-----------+---------+| LastName | FirstName | OrderNo |+----------+-----------+---------+| Carter | Thomas |77895|| Carter | Thomas |44678|| Adams | John |22456|| Adams | John |24562|+----------+-----------+---------+

2. Left connection [outer] join:

Look at the left connected statement and query results first

// outer can be deleted. Select  from persons the left join orders on persons.id_p=orders.id_p ORDER by LastName; +----------+-----------+---------+| LastName | FirstName | OrderNo |+----------+-----------+---------+| Adams    | John      24562   | | Adams    | John      22456   | | Bush     | George    | NULL    | | Carter   | Thomas    77895   | | Carter   | Thomas    44678   |+----------+-----------+---------+

With this result we can find that LastName is not orderno for Bush, while the Lastname,firstname property is from the persons table, the OrderNo property is from the Orders table visible when the left join function is put in All the information on the left persons table is displayed, even if there is no information that matches the field in the table orders on the right side of the leave join, and the value of OrderNo is filled with null.

If you swap the persons and orders positions on both sides of the left join, the result shows all the information of the Table Orders table on the right side of the The LastName and FirstName values in the right table persons of the join are set to NULL, which is consistent with the result of a right join of the non-swapped join two table name locations.

// swaps the persons and orders positions on both sides of the left join Select  from orders left join persons on persons.id_p=orders.id_p ORDER by LastName; +----------+-----------+---------+| LastName | FirstName | OrderNo |+----------+-----------+---------+| NULL     | NULL      34764   | | Adams    | John      24562   | | Adams    | John      22456   | | Carter   | Thomas    44678   | | Carter   | Thomas    77895   |+----------+-----------+---------+

3. Right-connect [outer] join:

// outer can be deleted. Select  from persons the outer join orders on persons.id_p=orders.id_p ORDER by LastName; +----------+-----------+---------+| LastName | FirstName | OrderNo |+----------+-----------+---------+| NULL     | NULL      34764   | | Adams    | John      22456   | | Adams    | John      24562   | | Carter   | Thomas    77895   | | Carter   | Thomas    44678   |+----------+-----------+---------+

Similarly to the left connection, the result shows all the information in the Table Persons table on the left side of the right join, and the OrderNo value in the table orders to the rights of the non-match to null.

4. Fully connected full join (however MYSQL/MARIADB not supported)

The full connection is to display the information of all two tables, regardless of whether the information of the two tables matches or not, the information on the mismatch is set to null.

MYSQL/MARIADB Learning Record-connection query (join)