-- 提示：文章本身就是一个sql脚本，可以直接执行。 -- 该文章参考了 http://lavasoft.blog.51cto.com/62575/38929/，对文章内容做了精简。 -- 文章主要针对MYSQL，目的是以简明的方式说明各种查询。 use test; -- CUSTOEMRS 表现客户信息 -- ORDERS 表现客户所下订单信息，其中CUSTOMER_ID描述该订单为哪一个客户所下。 DROP TABLE IF EXISTS CUSTOMERS; DROP TABLE IF EXISTS ORDERS; CREATE TABLE CUSTOMERS (ID BIGINT NOT NULL,NAME VARCHAR(15) NOT NULL,AGE INT, PRIMARY KEY (ID)); CREATE TABLE ORDERS (ID BIGINT NOT NULL,ORDER_NUMBER VARCHAR(15) NOT NULL, PRICE DOUBLE PRECISION,CUSTOMER_ID BIGINT,PRIMARY KEY (ID)); -- ================================================================================== INSERT INTO CUSTOMERS(ID,NAME,AGE) VALUES(1,'TOM',21); INSERT INTO CUSTOMERS(ID,NAME,AGE) VALUES(2,'MIKE',24); INSERT INTO CUSTOMERS(ID,NAME,AGE) VALUES(3,'JACK',30); INSERT INTO CUSTOMERS(ID,NAME,AGE) VALUES(4,'LINDA',25); INSERT INTO CUSTOMERS(ID,NAME,AGE) VALUES(5,'DAMA',NULL); -- 大妈，年龄不详 -- ================================================================================= INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(1,'TOM_ORDER001',100,1); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(2,'TOM_ORDER002',200,1); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(3,'TOM_ORDER003',300,1); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(4,'MIKE_ORDER001',100,2); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(5,'JACK_ORDER001',200,3); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(6,'LINDA_ORDER001',100,4); INSERT INTO ORDERS(ID,ORDER_NUMBER,PRICE,CUSTOMER_ID) VALUES(7,'不明订单',200,NULL); -- 该订单没有客户 -- 5号大妈没有下订单 -- ================================================================================== -- 各种查询示例： -- =================交叉连接==================================================== -- 隐式交叉连接，将会形成两个集合的迪卡尔积（请注意观察结果） SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME FROM ORDERS O , CUSTOMERS C ; -- 显式交叉连接 SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME FROM ORDERS O CROSS JOIN CUSTOMERS C ; -- 该转接可以会形成一个m*n行的中间表，它无实际意义，但可以做为后面的查询的基础。 -- =============================================================================== -- ===================内连接(观察结果)============================================ SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME FROM CUSTOMERS C,ORDERS O WHERE C.ID=O.CUSTOMER_ID; -- 隐式 SELECT O.ID,O.ORDER_NUMBER, C.ID, C.NAME FROM ORDERS O INNER JOIN CUSTOMERS C ON O.CUSTOMER_ID=C.ID; -- 显示 /* +----+----------------+----+-------+ | ID | ORDER_NUMBER | ID | NAME | +----+----------------+----+-------+ | 1 | TOM_ORDER001 | 1 | TOM | | 2 | TOM_ORDER002 | 1 | TOM | | 3 | TOM_ORDER003 | 1 | TOM | | 4 | MIKE_ORDER001 | 2 | MIKE | | 5 | JACK_ORDER001 | 3 | JACK | | 6 | LINDA_ORDER001 | 4 | LINDA | +----+----------------+----+-------+ */ -- 实质上对“交叉连接”的中间表进行了过滤(ON O.CUSTOMER_ID=C.ID)， -- 该查询描述出：每个下过订单的客户，所下订单的情况。注意：7号订单，5号客户并未出现在结果中。 -- ===================左外连接============================================================ SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID; /* +----+----------------+-------------+------+-------+ | ID | ORDER_NUMBER | CUSTOMER_ID | ID | NAME | +----+----------------+-------------+------+-------+ | 1 | TOM_ORDER001 | 1 | 1 | TOM | | 2 | TOM_ORDER002 | 1 | 1 | TOM | | 3 | TOM_ORDER003 | 1 | 1 | TOM | | 4 | MIKE_ORDER001 | 2 | 2 | MIKE | | 5 | JACK_ORDER001 | 3 | 3 | JACK | | 6 | LINDA_ORDER001 | 4 | 4 | LINDA | | 7 | 不明订单 | NULL | NULL | NULL | --》多出的行 +----+----------------+-------------+------+-------+ */ -- 观察结果：实质上是在内连接的基础上，增加了左表（ORDERS）中无法与右表相连的记录，如：7号记录。 -- =========================================================================================== -- ===================右外连接================================================================ SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID; /* +------+----------------+-------------+----+-------+ | ID | ORDER_NUMBER | CUSTOMER_ID | ID | NAME | +------+----------------+-------------+----+-------+ | 1 | TOM_ORDER001 | 1 | 1 | TOM | | 2 | TOM_ORDER002 | 1 | 1 | TOM | | 3 | TOM_ORDER003 | 1 | 1 | TOM | | 4 | MIKE_ORDER001 | 2 | 2 | MIKE | | 5 | JACK_ORDER001 | 3 | 3 | JACK | | 6 | LINDA_ORDER001 | 4 | 4 | LINDA | | NULL | NULL | NULL | 5 | DAMA | --》多出的行 +------+----------------+-------------+----+-------+ */ -- 观察结果：质上是在内连接的基础上，增加了右表（CUSTOMERS）中无法与左表相连的记录，如：5号记录。 -- ============================================================================================= -- ===================全外连接================================================================== -- SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID; /* +------+----------------+-------------+----+-------+ | ID | ORDER_NUMBER | CUSTOMER_ID | ID | NAME | +------+----------------+-------------+----+-------+ | 1 | TOM_ORDER001 | 1 | 1 | TOM | | 2 | TOM_ORDER002 | 1 | 1 | TOM | | 3 | TOM_ORDER003 | 1 | 1 | TOM | | 4 | MIKE_ORDER001 | 2 | 2 | MIKE | | 5 | JACK_ORDER001 | 3 | 3 | JACK | | 6 | LINDA_ORDER001 | 4 | 4 | LINDA | | 7 | 不明订单 | NULL |NULL| NULL | --》多出的行 | NULL | NULL | NULL | 5 | DAMA | --》多出的行 +------+----------------+-------------+----+-------+ */ -- 观察结果：质上是在内连接的基础上，增加了右表（CUSTOMERS）中无法与左表相连的记录， -- 如：5号记录,增加了左表（ORDERS）中无法与右表相连的记录，如：7号记录。。 -- 该连接在MySQL中是不支持的，只有对Oracle和SQL Server支持。
