结果根据连接语法而变化

JOIN ON JOIN USING 在相同的领域产生相同的结果?

SELECT * FROM racks r join rack_positions p on (r.rack_id=p.rack_id);//46 rows
SELECT * FROM racks r join rack_positions p using (rack_id);//zero rows

那么,为什么上面的查询不能产生相同的结果呢?(我只是打赌我会为自己没有弄明白这一点而自责。)

创建表语句

CREATE TABLE  `racks` (
  `rack_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `watersystem_id` int(10) unsigned NOT NULL,
  `pgroup_id` int(10) unsigned DEFAULT NULL,
  `rack_name` varchar(20) NOT NULL,
  `barcode_id` int(10) unsigned DEFAULT NULL,
  `row_max` int(10) unsigned NOT NULL DEFAULT '0',
  `spigot_max` int(10) unsigned NOT NULL DEFAULT '0',
  `added_by` int(10) unsigned DEFAULT NULL,
  `added_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modded_by` int(10) unsigned DEFAULT NULL,
  `modded_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rack_id`),
  UNIQUE KEY `watersystem_id` (`watersystem_id`,`rack_name`) USING BTREE,
  UNIQUE KEY `FK_racks_2` (`barcode_id`) USING BTREE,
  KEY `pgroup_id` (`pgroup_id`),
  CONSTRAINT `FK_racks_2` FOREIGN KEY (`barcode_id`) REFERENCES `barcodes` (`barcode_id`),
  CONSTRAINT `racks_ibfk_1` FOREIGN KEY (`watersystem_id`) REFERENCES `watersystems` (`watersystem_id`),
  CONSTRAINT `racks_ibfk_2` FOREIGN KEY (`pgroup_id`) REFERENCES `perm_groups` (`pgroup_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;


CREATE TABLE  `rack_positions` (
  `pos_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rack_id` int(10) unsigned NOT NULL,
  `row_num` tinyint(3) unsigned NOT NULL,
  `spigot_num` tinyint(3) unsigned NOT NULL,
  `operating` tinyint(1) DEFAULT '1' COMMENT 'set to false if location cannot be used',
  `notes` text COMMENT 'optional note about why a location might be non-operational',
  `barcode_id` int(10) unsigned DEFAULT NULL,
  `added_by` int(10) unsigned DEFAULT NULL,
  `added_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `modded_by` int(10) unsigned DEFAULT NULL,
  `modded_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`pos_id`),
  UNIQUE KEY `FK_locations_2` (`barcode_id`) USING BTREE,
  KEY `rack_id` (`rack_id`),
  KEY `FK_rack_positions_3` (`added_by`),
  KEY `FK_rack_positions_4` (`modded_by`),
  CONSTRAINT `FK_rack_positions_4` FOREIGN KEY (`modded_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `FK_locations_2` FOREIGN KEY (`barcode_id`) REFERENCES `barcodes` (`barcode_id`),
  CONSTRAINT `FK_rack_positions_3` FOREIGN KEY (`added_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL,
  CONSTRAINT `rack_positions_ibfk_1` FOREIGN KEY (`rack_id`) REFERENCES `racks` (`rack_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=98 DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;