Mysql Syntax: left join, right join, inner join

Source: Internet
Author: User

Mysql Syntax: left join, right join, inner join
Test TABLE user: www.2cto.com test TABLE password TABLE pwd: www.2cto.com 1. CREATE user TABLE: 1 create table 'user' (2 'id' int (11) not null auto_increment, 3 'name' varchar (255) default NULL, 4 'Password' varchar (255) default NULL, 5 primary key ('id') 6) ENGINE = InnoDB default charset = utf8; 2. CREATE a pwd TABLE: 1 create table 'pwd' (2 'id' int (11) not null auto_increment, 3 'userid' int (11) default NULL, 4 'passwd' varchar (255) default NULL, 5 primary key ('id') 6) ENGINE = InnoDB default charset = utf8; 3. Use inner join to fill in passwd in pwd table to the password column in user table (based on userid). SQL statement: 1 UPDATE 'user' U2INNER JOIN 'pwd' P3ON P. userid = U. id4SET U. password = P. passwd; 4. left join (left join) queries information in user 1 SELECT us. *, pw. * FROM 'user' us 2 left join pwd pw3ON pw. userid = us. result: Description:
Left join is based on the records of the User table. The User can be viewed as the left table, pwd can be viewed as the right table, and left join is based on the left table.
In other words, all the records in the left table (User) are displayed, while the right table (pwd) only displays records that meet the search criteria (pw in this example. userid = us. id ).
Where the records in Table B are insufficient, they are NULL. 5. left join (left join) queries information in pwd.
1 SELECT us. *, pw. * FROM 'user' us 2 right join pwd pw3ON pw. userid = us. id; Result: The result is the opposite of the result of left join. This time, the right table (pwd) is used as the basis, and the user table is filled with NULL when the user table is insufficient. 6. inner join (equal join or inner join)

1 SELECT us. *, pw. * FROM 'user' us 2 inner JOIN pwd pw3ON pw. userid = us. result: Note: Obviously, only pw is displayed here. userid = us. id. this indicates that inner join is not based on WHO, and only displays records that meet the conditions.

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.