96 lines
1.7 KiB
SQL
96 lines
1.7 KiB
SQL
/*
|
||
Author: Fromsko
|
||
*/
|
||
|
||
|
||
-- 创建 Person 表
|
||
CREATE TABLE PERSON (
|
||
PERSONID NUMBER,
|
||
FIRSTNAME VARCHAR2(255),
|
||
LASTNAME VARCHAR2(255)
|
||
);
|
||
|
||
-- 创建 Address 表
|
||
CREATE TABLE ADDRESS (
|
||
ADDRESSID NUMBER,
|
||
PERSONID NUMBER,
|
||
CITY VARCHAR2(255),
|
||
STATE VARCHAR2(255)
|
||
);
|
||
|
||
-- 清空 Person 表数据
|
||
TRUNCATE TABLE PERSON;
|
||
|
||
-- 向 Person 表插入数据
|
||
INSERT INTO PERSON (
|
||
PERSONID,
|
||
LASTNAME,
|
||
FIRSTNAME
|
||
) VALUES (
|
||
'1',
|
||
'Wang',
|
||
'Allen'
|
||
);
|
||
|
||
INSERT INTO PERSON (
|
||
PERSONID,
|
||
LASTNAME,
|
||
FIRSTNAME
|
||
) VALUES (
|
||
'2',
|
||
'Alice',
|
||
'Bob'
|
||
);
|
||
|
||
-- 清空 Address 表数据
|
||
TRUNCATE TABLE ADDRESS;
|
||
|
||
-- 向 Address 表插入数据
|
||
INSERT INTO ADDRESS (
|
||
ADDRESSID,
|
||
PERSONID,
|
||
CITY,
|
||
STATE
|
||
) VALUES (
|
||
'1',
|
||
'2',
|
||
'New York City',
|
||
'New York'
|
||
);
|
||
|
||
INSERT INTO ADDRESS (
|
||
ADDRESSID,
|
||
PERSONID,
|
||
CITY,
|
||
STATE
|
||
) VALUES (
|
||
'2',
|
||
'3',
|
||
'Leetcode',
|
||
'California'
|
||
);
|
||
|
||
/*
|
||
INNER JOIN: 两表值都存在
|
||
OUTER JOIN: 附表中值可能存在NULL的情况。
|
||
总结：
|
||
1. A INNER JOIN B：取交集
|
||
2. A LEFT JOIN B：取A全部，B没有对应的值，则为NULL
|
||
3. A RIGHT JOIN B：取B全部，A没有对应的值，则为NULL
|
||
4. A FULL OUTER JOIN B：取并集，彼此没有对应的值为NULL
|
||
上述4种的对应条件，在ON后填写。
|
||
*/
|
||
|
||
-- 查询数据 (左外连接)
|
||
SELECT
|
||
PERSON.PERSONID,
|
||
FIRSTNAME,
|
||
LASTNAME,
|
||
CITY, -- NVL(CITY, '不存在') CITY,
|
||
STATE -- NVL(STATE, '不存在') STATE
|
||
FROM
|
||
PERSON
|
||
LEFT OUTER JOIN ADDRESS -- 左边取全部, 右边可能有取不到的 null
|
||
ON PERSON.PERSONID=ADDRESS.PERSONID -- 连接条件为 双表之间相同的值
|
||
ORDER BY
|
||
PERSON.PERSONID; |