本文基于 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)
参考资料:
https://www.runoob.com/sqlite/sqlite-joins.html SQLite Join
https://www.cnblogs.com/assasion/p/7768931.html inner join 与 left join 之间的区别
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)