mysql/mariadb learning record -- JOIN

//Data tables used in this article
//ID ﹣ P is the primary key in the persons table
//In the orders table, ID ﹣ o is the primary key, and ID ﹣ P is the foreign key. Refer to ID ﹣ P in the persons table
mysql> select * from persons;select * from orders; +------+----------+-----------+----------------+----------+ | 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 | +------+----------+-----------+----------------+----------+ 3 rows in set (0.05 sec) +------+---------+------+ | id_o | orderNo | id_p | +------+---------+------+ | 1 | 77895 | 3 | | 2 | 44678 | 3 | | 3 | 22456 | 1 | | 4 | 24562 | 1 | | 5 | 34764 | 65 | +------+---------+------+ 5 rows in set (0.05 sec)

 

1. Inner join:

Internal connection is also called connection, or natural connection:

//inner Deleting
mysql> select lastname,firstname,orderNo from persons 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 Achieve the same effect
mysql> select lastname,firstname,orderNo from persons,orders where persons.id_p=orders.id_p;
+----------+-----------+---------+
| lastname | firstname | orderNo |
+----------+-----------+---------+
| Carter   | Thomas    | 77895   |
| Carter   | Thomas    | 44678   |
| Adams    | John      | 22456   |
| Adams    | John      | 24562   |
+----------+-----------+---------+

2. left [outer] join:

First look at the left connected statements and query results

//outer May be deleted
mysql> select lastname,firstname,orderNo from persons 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   |
+----------+-----------+---------+

Through this result, we can find that lastname is a Bush and there is no orderNo, while lastname and firstname attributes are from the persons table and orderNo attributes are from the orders table. When the left join is visible, all the information in the persons table on the left side of the left join will be displayed, even if there is no table on the right side of the left join The matching information of the fields in orders is also displayed, and the value of orderNo is NULL.

If the positions of persons and orders on both sides of the left join are interchanged, the result shows all the information of the orders table on the left side of the left join. At the same time, the lastname and firstname values in the persons table on the right side of the left join that are not matched are set to NULL. This result is consistent with the result of the right connection of the names of the two tables of the left join that are not exchanged.

//take left join Bilateral persons and orders change position
mysql> select lastname,firstname,orderNo 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 [outer] join:

//outer May be deleted
mysql> select lastname,firstname,orderNo from persons right 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, for left join, the result shows all the information in the persons table on the left of the right join, and sets the orderNo value in the orders table on the right join right that does not match to NULL.

4. full join (not supported by mysql/mariadb)

Full join is to display all the information of the two tables. No matter whether the information of the two tables matches or not, the information on the unmatched table is set to NULL.

Tags: MySQL MariaDB

Posted on Fri, 20 Mar 2020 08:38:13 -0700 by vickytam