SQL join

#### oracle join 测试 ####
## SQL join 用于把来自两个或多个表的行结合起来。
## 重要信息：例子来源于runoob，并且https://www.runoob.com/sql/sql-join.html 图示解释这7种用法，很直观并便于比较。
准备环境创建表,因为需要，对表做了一些简单调整。具体建表语句在后边。

### INNER JOIN 取交集
## INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "join_Websites" 表中的行在 "join_access_log" 中没有匹配，则不会列出这些行

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
INNER JOIN join_access_log
ON join_Websites.id=join_access_log.site_id
ORDER BY join_access_log.contact_info;

### LEFT JOIN /LEFT OUTER JOIN 左全集+交集
## LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
LEFT JOIN join_access_log
ON join_Websites.id=join_access_log.site_id
ORDER BY join_access_log.contact_info DESC;

### LEFT JOIN 的扩展用法左全集-交集
## 下列语句只返回一行，weibo null null

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
LEFT JOIN join_access_log
ON join_Websites.id=join_access_log.site_id
where join_access_log.site_id is NULL
ORDER BY join_access_log.contact_info DESC;

### RIGHT JOIN /RIGHT OUTER JOIN 右全集+交集
## RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT join_websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_websites
RIGHT JOIN join_access_log
ON join_access_log.site_id=join_websites.id
ORDER BY join_access_log.contact_info DESC;

### RIGHT JOIN 的扩展用法 右全集-交集
## 下列语句不返回结果。

SELECT join_websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_websites
RIGHT JOIN join_access_log
ON join_access_log.site_id=join_websites.id
where join_websites.id is NULL
ORDER BY join_access_log.contact_info DESC;

### FULL JOIN /FULL OUTER JOIN 左右均全集
## FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.
## FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
FULL OUTER JOIN join_access_log
ON join_Websites.id=join_access_log.site_id
ORDER BY join_access_log.contact_info DESC;

### FULL JOIN 的扩展用法 左右全集-交集

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
FULL OUTER JOIN join_access_log
ON join_Websites.id=join_access_log.site_id
where join_Websites.id is NULL
or join_access_log.site_id is NULL
ORDER BY join_access_log.contact_info DESC;

### CROSS JOIN 笛卡尔集
## 结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

SELECT join_Websites.name, join_access_log.contact_info, join_access_log.access_date
FROM join_Websites
CROSS JOIN join_access_log

### SQL UNION 操作符
SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

SQL UNION 语法

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SQL UNION ALL 语法

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

注释：UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。

例子：

SELECT country FROM join_Websites
UNION
SELECT country FROM join_apps
ORDER BY country;

SELECT country FROM join_Websites
UNION ALL
SELECT country FROM join_apps
ORDER BY country;

SELECT country, name FROM join_Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM join_apps
WHERE country='CN'
ORDER BY country;

附录：建表和插入数据语句

--/
begin
joe.PROC_DROPTABLEIFEXISTS('join_access_log');
end;
/
CREATE TABLE joe.join_access_log (
aid int NOT NULL,
site_id int default 0,
contact_info varchar(255) default 'user01@runoob.com',
access_date date,
PRIMARY KEY (aid)
);
comment on column joe.join_access_log.site_id is 'web site id';
comment on column joe.join_access_log.contact_info is 'access number';
INSERT INTO joe.join_access_log VALUES ('1', '1', 'user01@runoob.com', TO_DATE('2016-05-10', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('2', '3', 'user02@runoob.com', TO_DATE('2016-05-13', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('3', '1', 'user03@runoob.com', TO_DATE('2016-05-14', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('4', '5', 'user04@runoob.com', TO_DATE('2016-05-14', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('5', '3', 'user05@runoob.com', TO_DATE('2016-05-15', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('6', '5', 'user06@runoob.com', TO_DATE('2016-05-16', 'YYYY-MM-DD'));
INSERT INTO joe.join_access_log VALUES ('7', '3', 'user07@runoob.com', TO_DATE('2016-05-17', 'YYYY-MM-DD'));

--/
begin
joe.PROC_DROPTABLEIFEXISTS('join_apps');
end;
/
CREATE TABLE joe.join_apps (
id int NOT NULL,
app_name char(20) DEFAULT '',
url varchar(255) DEFAULT '',
country char(10) DEFAULT '',
PRIMARY KEY (id)
);
INSERT INTO joe.join_apps VALUES ('1', 'QQ APP', 'http://im.qq.com/', 'CN');
INSERT INTO joe.join_apps VALUES ('2', 'weibo APP', 'http://weibo.com/', 'CN');
INSERT INTO joe.join_apps VALUES ('3', 'taobao APP', 'https://www.taobao.com/', 'CN');

--/
begin
joe.PROC_DROPTABLEIFEXISTS('join_websites');
end;
/
CREATE TABLE joe.join_websites (
id int,
name char(20) DEFAULT '' ,
url varchar(255) DEFAULT '',
alexa int DEFAULT '0' ,
country char(10) DEFAULT '' ,
PRIMARY KEY (id)
);
INSERT INTO joe.join_websites VALUES ('1', 'Google', 'https://www.google.cm/', '1', 'USA');
INSERT INTO joe.join_websites VALUES ('2', 'taobao', 'https://www.taobao.com/', '13', 'CN');
INSERT INTO joe.join_websites VALUES ('3', 'runoob', 'http://www.runoob.com/', '4689', 'CN');
INSERT INTO joe.join_websites VALUES ('4', 'weibo', 'http://weibo.com/', '20', 'CN');
INSERT INTO joe.join_websites VALUES ('5', 'Facebook', 'https://www.facebook.com/', '3', 'USA');

posted @ 2023-01-09 14:35 panda4671 阅读(24) 评论(0) 编辑收藏举报

刷新页面返回顶部

panda4671

SQL join

公告