25.MySQL中的交叉连接CROSS JOIN

1.简介

交叉连接返回的结果是被连接的两个表中所有数据行的笛卡尔积。需要注意的是，交叉连接产生的结果是笛卡尔积，并没有实际应用的意义。

例如，班级表中有3个字段，4条记录，学生表中有5个字段，10条记录，那么交叉连接后的笛卡尔积就等于4*10条记录数，每条记录中含有3+5个字段。

语法

SELECT 查询字段 FROM 表1 CROSS JOIN 表2;

CROSS JOIN用于连接两个要查询的表。

2.准备

CREATE DATABASE mahaiwuji;
USE mahaiwuji;

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;