mysql 左连接

select u.*,uii1.info_value as idCard,uii2.info_value as stuNo ,d.department_name,d.grade_id  
from sys_users u left join  sys_user_info_input uii1 on u.user_uid = uii1.user_uid and uii1.user_info_type_id ='P1'
left join  sys_user_info_input uii2 on u.user_uid = uii2.user_uid and uii2.user_info_type_id ='I6',re_dept_user du,sys_department d
where u.user_uid = du.user_uid and du.department_uid = d.department_uid and du.has_checked = 1 
and u.user_status_id=1 and du.user_type_id=1 and d.dept_type_id='07'
and d.department_uid in(73)

左连接，拿到的左边要求属性的值，右表对应的属性值如果不存在，以null值显示在所求的结果中。

右连接，拿到右边要求属性的值，左表对应的属性如果不存在，以null值显示在所求结果中。

select s.*,a1.uid from sys_users  s,(select a.department_uid as uid from  sys_department a) a1 where s.sex_id=1

  from后的select语句查询的结果作为了一个临时表，第一个select语句查出a1中别名作为的属性值。

 

转载于:https://www.cnblogs.com/moonlignt/p/7524554.html