SQL All connections Explained

Source: Internet
Author: User
Tags joins

--The inner connection is left to match the right table, and there is a corresponding display
--left join is the left table as the Datum match to the right table, if the right table does not have corresponding null
--The right connection is the table that has the right side as the Datum match to the left table, or null if the left table does not correspond
--The full connection is the left table that matches the right table, and if there is no display is null
--Cross Join returns all rows from the left table, with each row in the left table combined with all rows in the right table. Cross joins are also called Cartesian product.

--1) Internal Connection
Select a.*,b.* from ATABLE a inner joins btable B on a.id=b.parent_id;
--2) Left Connection
Select a.*,b.* from ATABLE a LEFT join btable B on a.id=b.parent_id;
--3) Right Connection
Select a.*,b.* from ATABLE a right joins btable B on a.id=b.parent_id;
--4) Fully connected
Select a.*,b.* from ATABLE a full join btable B on a.id=b.parent_id;
--Cross Join
SELECT a.*,b.* from ATABLE a cross JOIN btable B;
--Self-join
SELECT a.*,b.* from ATABLE a,btable b WHERE a.id=b.parent_id;
--2) LEFT outer connection
Select a.*,b.* from ATABLE a left OUTER joins btable B on a.id=b.parent_id;
--3) Right outer connection
Select a.*,b.* from ATABLE a right OUTER joins btable B on a.id=b.parent_id;
--4) Full outer connection
Select a.*,b.* from ATABLE a full OUTER joins btable B on a.id=b.parent_id;

A table ID name B table ID job parent_id
1 Sheets 3 1 23 1
2 Lee 42 34 2
3 Wang Wu 3 34 4
Relationship between a.ID and parent_id

SQL All connections Explained

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.