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.