Join语句

      

       一、Join语句几种方式

 

       1.内连接(INNER)

 

SELECT a.username,a.over,b.over
FROM user1 a
INNER JOIN user2 b
ON a.username = b.username;

 

 

          查询结果：a、b两表的公共数据

         

        2.左外连接（LEFT OUTER）

 

SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b
ON a.username = b.username
WHERE b.username IS NOT NULL;

 

 

             查询结果：a表的所有数据 

 

         3.右外连接(RIGHT OUTER )

 

SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL;

 

 

 

              查询结果：b表的所有数据 - ab两个表的公共数据

 

         4.全外连接(FULL OUTER)

 

SELECT a.username,a.over,b.over
FROM user1 a
FULL JOIN user2 b
ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;

 

  

                MySQL中不支持FULL OUTER JOIN 连接,故上面查询会出现错误

    

                   

SELECT a.username,a.over,b.over
FROM user1 a
LEFT JOIN user2 b  ON a.username = b.username
UNION  ALL
SELECT b.username,b.over,a.over
FROM user1 a
RIGHT JOIN user2 b  ON a.username = b.username
WHERE a.username IS NULL OR b.username IS NULL;

  

               查询结果：a,b表的所有数据 - a,b表的公共数据

 

          5.交叉连接(CROSS)或笛卡尔连接（cartesian）或叉乘（Product）

                   

SELECT a.username,a.over,b.over
FROM user1 a
CROSS JOIN user2 b
ON a.username = b.username

                 

                    一般要避免使用此查询

 

 

               

            二、Join语句的使用

               

             1.使用join更新表

                    

UPDATE  user1
SET over = '齐天大圣'
WHERE user1.username IN(
   SELECT b.username
   FROM user1 a  
   JOIN user2 b 
   ON a.username = b.username
);

  

                  MySQL不支持UPDATE操作，故上面更新操作会出现错误

                

                      

UPDATE user1  a   JOIN (
   SELECT b.username    
   FROM user1 a  JOIN user2 b  
   ON a.username = b.username
   ) b    ON  a.username = b.username
SET  a.over='齐天大圣';

                    

                    更新结果：将over字段的数据更新为‘齐天大圣’

 

              2.使用join优化子查询

                      

SELECT a.username,a.over,
(
   SELECT over FROM user2 b
   WHERE  a.username = b.username
   ) AS  over2  
 FROM user1 a;

               

                       查询结果：对a表中的每一条数据都进行子查询，很慢

                    

                  优化后：

                          

SELECT a.username,a.over,b.over  AS over2
FROM user1 a
LEFT JOIN user2 b  ON 
a.username = b.username

                         

                         优化后和优化前结果相同则为有效优化

                         查询结果：对a表中的每一条数据都进行子查询，较快

            

                 3.使用join优化聚合子查询

                         

SELECT a.username,b.timestr,b.kills
FROM user1 a  JOIN userkills b
ON a.id = b.userid
WHERE b.kills = (
   SELECT MAX(c.kills)
   FROM userkills c   WHERE c.userid = b.userid
);

                        

                          查询结果：各个username打怪（kills）最多的日期

 

                     避免子查询：

                               

SELECT a.username,b.timestr,b.kills
FROM user1 a
JOIN userkills b ON a.id = b.userid
JOIN userkills c ON c.userid = b.userid
GROUP BY a.username,b.timestr,b.kills
HAVING b.kills = MAX(c.kills);