MySQL DBA system Learning (23) must understand the SQL knowledge

Source: Internet
Author: User
Tags dba join

Must understand the SQL knowledge

One, two table outer join query

The existing two table A,b contents are as follows

Mysql> select * from A;
+------+------+
| ID   | Col1 |
+------+------+
|    1 | AA   |
|    2 | BB   |
|    3 | CC   |
+------+------+
3 rows in Set (0.00 sec)
mysql> select * from B;
+------+------+
| ID   | Col2 |
+------+------+
|    2 | DD   |
|    3 | EE   |
|    4 | FF   |
+------+------+
3 rows in Set (0.01 sec)

1,a table and B table left connection

The left table (A) data is identified, and then according to the conditions on the back, the right table in the normal ID and left table ID is equal to the records are found, and the matching left table records in turn row or multiple lines, if no matching records, display null.

Mysql> SELECT * from A LEFT join B on A.id=b.id;
+------+------+------+------+
| ID   | Col1 | ID   | Col2 |
+------+------+------+------+
|    1 | AA   | NULL | NULL |
|    2 | BB   |    2 | DD   |
|    3 | CC   |    3 | EE   |
+------+------+------+------+
3 rows in Set (0.00 sec)
mysql> select a.ID ID, a.col1 C1, B.col2 C2 from  A left join B on A.id=b.id;
+------+------+------+
| ID   | C1   | C2   |
+------+------+------+
|    1 | AA   | NULL |
|    2 | BB   | DD   |
|    3 | CC   | EE   |
+------+------+------+
3 rows in Set (0.00 sec)

The following results are the same

2,a table and B table right connection

The right table (B) data are identified, and then according to the following conditions, the left table in the General ID and right table ID is equal to the records are found, and the matching left table record in order to row or multiple rows, if no matching records, display null

Mysql> SELECT * from A right join B on a.id=b.id;
+------+------+------+------+
| ID   | Col1 | ID   | Col2 |
+------+------+------+------+
|    2 | BB   |    2 | DD   |
|    3 | CC   |    3 | EE   |
| NULL | NULL |    4 | FF   |
+------+------+------+------+
3 rows in Set (0.07 sec)
mysql> select a.ID ID, a.col1 C1, B.col2 C2 from  A right  join B on a.id=b.id;
+------+------+------+
| ID   | C1   | C2   |
+------+------+------+
|    2 | BB   | DD   |
|    3 | CC   | EE   |
| NULL | NULL | FF   |
+------+------+------+
3 rows in Set (0.00 sec)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.