SQL JOIN TEST

-- 说明:
-- left/right join查询必须指定查询条件
-- left/right join查询的实质区别是，以jion关键字左边还是右边的表为主表， 查询结果集以主表符合条件行数为标准行数
-- using 与 on 的实质区别是，using关键字内地字段只会存在一列结果集，而on条件索引的，如果是默认字段列，两个表的条件字段都会显示出来
-- inner join 内联可以不指定查询条件; 如果不指定查询条件，则会返回 m*n行记录.
-- natural join 其实自然连接就和内连接结果集然后过滤相应的一个条件，使形成的表不会有空字段或者冗余字段。
-- natural join 如果两个表是某一对象的分拆表，表行数高度一致时，查询该对象的对应信息，建议用自然连接, 且表不会有冗余字段
-- Cross Join 交叉连接，比较废柴，开发这么多年，项目中没有用过交叉连接。如果不是测试，估计一辈子用不到。
-- Outer Join 外连接，必读指定查询条件. 其查询实质与左右联接查询刚好相反，主表是反过来的。其余都一样。
-- FULL OUTER JOIN 全外连接，其查询结果相当于是合并了左右连接。

-- 测试用例:
-- select * from test_idx right join test_ref using(RoleID);

-- select * from test_idx left join test_ref using(RoleID);
-- select * from test_idx left join test_ref using(RoleID) WHERE RoleName='ken15';
-- select * from test_idx left join test_ref ON test_ref.RoleID = test_idx.RoleID  WHERE test_idx.RoleName='ken4';

-- select * from test_idx inner join test_ref on test_ref.RoleID = test_idx.RoleID;
-- select * from test_idx inner join test_ref using(RoleID);
-- select * from test_idx inner join test_ref;

-- select * from test_idx natural join test_ref;
-- select * from test_ref natural join test_idx;
-- select * from test_ref natural join test_idx where RoleID=15;

-- select * from test_idx cross join test_ref;
-- select * from test_ref cross join test_idx;

-- select * from test_idx left OUTER join test_ref ON test_ref.RoleID = test_idx.RoleID;
-- select * from test_idx RIGHT OUTER join test_ref ON test_ref.RoleID = test_idx.RoleID;
-- select * from test_idx left OUTER join test_ref USING(RoleID);
-- select * from test_idx RIGHT OUTER join test_ref USING(RoleID);

-- 语法错误 select * from test_idx  FULL OUTER JOIN test_ref ON test_idx.RoleID = test_ref.RoleID;

delimiter $$

CREATE TABLE `test_idx` (
  `RoleID` int(10) unsigned NOT NULL,
  `RoleName` varchar(20) NOT NULL,
  KEY `test_idx_index` (`RoleID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='索引测试用例'$$


delimiter $$

CREATE TABLE `test_ref` (
  `RoleID` int(10) unsigned NOT NULL,
  `age` int(3) NOT NULL DEFAULT '0',
  `gold` int(10) NOT NULL DEFAULT '10',
  `silve` int(10) NOT NULL DEFAULT '7',
  PRIMARY KEY (`RoleID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='外键测试'$$


-- ----------------------------
-- Records of test_idx
-- ----------------------------
INSERT INTO `test_idx` VALUES ('99', 'KEN99');
INSERT INTO `test_idx` VALUES ('23', 'ken3');
INSERT INTO `test_idx` VALUES ('24', 'ken4');
INSERT INTO `test_idx` VALUES ('25', 'ken5');
INSERT INTO `test_idx` VALUES ('15', 'ken15');
INSERT INTO `test_idx` VALUES ('14', 'ken14');

-- ----------------------------
-- Records of test_ref
-- ----------------------------
INSERT INTO `test_ref` VALUES ('14', '13', '130', '130');
INSERT INTO `test_ref` VALUES ('15', '15', '150', '150');
INSERT INTO `test_ref` VALUES ('23', '23', '230', '230');
INSERT INTO `test_ref` VALUES ('24', '24', '240', '240');
INSERT INTO `test_ref` VALUES ('25', '25', '258', '258');