1.简介
交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积,但是并没有实际应用的意义。
例如,班级表中有3个字段,4条记录,学生表中有5个字段,10条记录,那么交叉连接后的笛卡尔积就等于4*10条记录数,每条记录中含有3+5个字段。
语法:
SELECT 查询字段 FROM 表1 CROSS JOIN 表2;
2.准备
CREATE TABLE grade(
gid INT(4) PRIMARY KEY,
gname VARCHAR(36),
tehcher VARCHAR(36)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO grade VALUES (1,'一班','一班老师');
INSERT INTO grade VALUES (2,'二班','二班老师');
INSERT INTO grade VALUES (3,'三班','三班老师');
INSERT INTO grade VALUES (4,'四班','四班老师');
CREATE TABLE student(
sid INT(4) PRIMARY KEY,
sname VARCHAR(36),
sex VARCHAR(10),
score FLOAT,
height FLOAT
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO student VALUES (1,'a1','男',95.6,172.5);
INSERT INTO student VALUES (2,'a2','女',84.6,172.5);
INSERT INTO student VALUES (3,'a3','男',94.6,172.5);
INSERT INTO student VALUES (4,'a4','女',95.6,172.5);
INSERT INTO student VALUES (5,'a5','男',55.6,172.5);
INSERT INTO student VALUES (6,'a6','女',25.6,172.5);
INSERT INTO student VALUES (7,'a7','男',35.6,172.5);
INSERT INTO student VALUES (8,'a8','女',89.6,172.5);
INSERT INTO student VALUES (9,'a9','男',95.6,172.5);
INSERT INTO student VALUES (10,'a10','男',95.6,172.5);
3.查询结果
SELECT * FROM grade CROSS JOIN student;