SQL join

本文基于 sqlite 进行测试，并做如下准备工作

import sqlite3

### 连接
conn = sqlite3.connect('test.db')
print("Opened database successfully")

### 获取游标并建表
cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
       (ID INT PRIMARY KEY     NOT NULL,
       NAME           TEXT    NOT NULL,
       AGE            INT     NOT NULL,
       ADDRESS        CHAR(50),
       SALARY         REAL);''')
print("Table created successfully")

### 写入数据
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )")
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )")
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )")
cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )")

conn.commit()
conn.close()

内连接 inner join

不以谁为准，相当于取交集

sql = '''select * from company m inner join (select * from company where age > 25) n on m.age = n.age'''
cur.execute(sql)
print(cur.fetchall())
# (1, 'Paul', 32, 'California', 20000.0, 1, 'Paul', 32, 'California', 20000.0)

如果只写 join，默认是内连接

外连接 outer join

外连接分为 left join、right join、full join 3 种

### left join
## 以左为准，左边有 x 行，输出 x
# as 可有可无
sql = '''select * from company m left join (select * from company where age > 25) as n on m.age = n.age;'''
cur.execute(sql)
print(cur.fetchall())
# (1, 'Paul', 32, 'California', 20000.0, 1, 'Paul', 32, 'California', 20000.0)
# (2, 'Allen', 25, 'Texas', 15000.0, None, None, None, None, None)
# (3, 'Teddy', 23, 'Norway', 20000.0, None, None, None, None, None)
# (4, 'Mark', 25, 'Rich-Mond ', 65000.0, None, None, None, None, None)

### right join
## 以右为准，sqlite 不支持
sql = '''select * from company m right join (select * from company where age > 25) as n on m.age = n.age;'''

### full join
## 取并集，sqlite 不支持

交叉连接 cross join

相当于作笛卡尔内积，如左表 x 行，右表 y 行，输出 x*y 行，然后基于关键字如 on 进行筛选

sql = '''select * from company m cross join (select * from company where age > 23) n on m.age = n.age'''
cur.execute(sql)
print(cur.fetchall())
# (1, 'Paul', 32, 'California', 20000.0, 1, 'Paul', 32, 'California', 20000.0)
# (2, 'Allen', 25, 'Texas', 15000.0, 2, 'Allen', 25, 'Texas', 15000.0)
# (2, 'Allen', 25, 'Texas', 15000.0, 4, 'Mark', 25, 'Rich-Mond ', 65000.0)
# (4, 'Mark', 25, 'Rich-Mond ', 65000.0, 2, 'Allen', 25, 'Texas', 15000.0)
# (4, 'Mark', 25, 'Rich-Mond ', 65000.0, 4, 'Mark', 25, 'Rich-Mond ', 65000.0)

关键字表达式

包括 on using natural 3 种，这三个关键字不可同时使用

### using
## 简化，去重
sql = '''select * from company m cross join (select * from company where age > 23) n using (name, age);'''
cur.execute(sql)
print(cur.fetchall())
# (1, 'Paul', 32, 'California', 20000.0, 1, 'California', 20000.0)
# (2, 'Allen', 25, 'Texas', 15000.0, 2, 'Texas', 15000.0)
# (4, 'Mark', 25, 'Rich-Mond ', 65000.0, 4, 'Rich-Mond ', 65000.0)