Mysql syntax leftjoin, rightjoin, innerjoin bitsCN.com
Mysql syntax: left join, right join, inner join
Test table user:
Test table password table pwd:
1. create a 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 enter passwd in the pwd table into the password column (based on userid) in the user table. the SQL statement is as follows:
1
UPDATE 'user' U
2
Inner join 'pwd' P
3
On p. userid = U. id
4
Set u. password = P. passwd;
4. left join queries information in the user.
1
SELECT us. *, pw. * FROM 'user' us
2
Left join pwd pw
3
ON pw. userid = us. id;
Result:
Note:
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 ).
All records in Table B are NULL.
5. left join queries information in pwd.
1
SELECT us. *, pw. * FROM 'user' us
2
Right join pwd pw
3
ON pw. userid = us. id;
Result:
Note:
The result of the left join operation is the opposite. this operation is based on the right table (pwd) and 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 pw
3
ON pw. userid = us. id;
Result:
Note:
Obviously, only records of pw. userid = us. id are displayed. This indicates that inner join is not based on who, and only records meeting the conditions are displayed.
BitsCN.com