• MySql常用 join 详解


    虽然这类资料比较多....我觉得还是有必要记下来,新手可以看看吧。。。老司机可以一眼飘过那。。。

    常用SQL JOINS方式

    • 1.SELECT select_list FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key

    • 2.SELECT select_list FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key=B.Key

    • 3.SELECT select_list FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key
      WHERE B.key is NULL

    • 4.SELECT select_list FROM TABLEA A RIGHT JOIN TABLEB B ON A.Key=B.key
      WHERE A.Key is null

    • 5.SELECT select_list FROM TABLE A INNER JOIN TABLEB B ON A.Key=B.Key

    • 6.SELECT select_list FROM TABLE A FULL OUTER JOIN TABLEB B ON A.Key=B.Key(Oracle支持)

    • 7.SELECT select_list FROM TABLE A FULL OUTER JOIN TABLEB B ON
      WHERE A.Key IS NULL OR B.Key IS NULL(Oracle支持)

    运行效果
    create table tbl_dept(
      id int(11) not null auto_increment,
      deptName varchar(30) default null,
      locAdd varchar(40) default null,
      primary key(id)
    )engine=innodb auto_increment=1 default charset=utf8;
    
    
    create table tbl_emp(
      id int(11) not null auto_increment,
      name varchar(20) default null,
      deptId int(11) default null,
      primary key(id),
      key fk_dept_id (deptId)
      #constraint fk_dept_id foreign key (deptId) references tbl_dept (id)
    )engine=innodb auto_increment=1 default charset=utf8;
    
    insert into tbl_dept(deptName,locAdd)values('RD',11);
    insert into tbl_dept(deptName,locAdd)values('HR',12);
    insert into tbl_dept(deptName,locAdd)values('MK',13);
    insert into tbl_dept(deptName,locAdd)values('MIS',14);
    insert into tbl_dept(deptName,locAdd)values('FD',16);
    
    insert into tbl_emp(name,deptId)values('z2',1);
    insert into tbl_emp(name,deptId)values('z3',1);
    insert into tbl_emp(name,deptId)values('z4',1);
    insert into tbl_emp(name,deptId)values('z4',1);
    insert into tbl_emp(name,deptId)values('z6',1);
    insert into tbl_emp(name,deptId)values('w5',2);
    insert into tbl_emp(name,deptId)values('s7',3);
    insert into tbl_emp(name,deptId)values('s8',4);
    insert into tbl_emp(name,deptId)values('s9',51)

    • select * from tbl_emp;

    • select * from tbl_emp;

    • 两个集合笛卡尓积 select * from tbl_emp,tbl_dept;

    • select * from tbl_emp a inner join tbl_dept b on a.deptId=b.id;

    • select * from tbl_emp a left join tbl_dept b on a.deptId=b.id;

    • select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

    • select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null;

    • select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;

    • select * from tbl_emp a left join tbl_dept b on a.deptId=b.id
      union
      select * from tbl_emp a right join tbl_dept b on a.deptId=b.id;

    • select * from tbl_emp a left join tbl_dept b on a.deptId=b.id where b.id is null
      union
      select * from tbl_emp a right join tbl_dept b on a.deptId=b.id where a.deptId is null;

     
  • 相关阅读:
    razor在App_Code中使用ActionLink无效的解决方案
    科技的进步会给人带来幸福么?
    C6000系列之C6455 DSP的EMIFA接口
    C6000系列之C6455DSP的GPIO模块
    C语言文件操作与例子
    C语言中fscanf函数读取double型浮点数的问题
    MATLAB读取CCS保存的数据
    CCS 3.3 操作C函数读写文件
    复数矩阵乘法C语言实现
    C6000系列之C6455DSP的中断系统
  • 原文地址:https://www.cnblogs.com/phpper/p/7338783.html
Copyright © 2020-2023  润新知