SQL的JOIN操作

对于SQL的JOIN操作,无非就这么几种

  • INNER JOIN
  • OUTER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN

通过文氏图,可以更清晰的了解每种操作以及所得的结果集。

Django ORM的JOIN操作

Django跨表查询时,使用select_related(),将相关表JOIN,从而减少查库次数。

那Django会使用哪种SQL JOIN呢?

Django 定义两张表

1
2
3
4
5
6
7
8
9
10
11
12
from django.db import models


class Teacher(models.Model):
name = models.CharField(max_length=50)


class Student(models.Model):
name = models.CharField(max_length=50)
teacher = models.ForeignKey(
Teacher, related_name='teacher', on_delete=models.DO_NOTHING
)

初始化后,python manage.py shell中执行, 输出相关SQL

1
2
3
4
5
6
7
8
from demo.models import *

students = Student.objects.select_related('teacher')
print(students.query)

SELECT `student`.`id`, `student`.`name`, `student`.`teacher_id`, `teacher`.`id`, `teacher`.`name`
FROM `student`
INNER JOIN `teacher` ON (`student`.`teacher_id` = `teacher`.`id`)

将表Student.teacher字段null=True,重复上一步操作,输出SQL如下

1
2
3
SELECT `student`.`id`, `student`.`name`, `student`.`teacher_id`, `teacher`.`id`, `teacher`.`name` 
FROM `student`
LEFT OUTER JOIN `teacher` ON (`student`.`teacher_id` = `teacher`.`id`)

由此可知,在ForeignKey null=True时,关联查询会通过LEFT JOIN,将左表数据查出,没有匹配的数据则用null代替。反之则通过INNER JOIN筛选出两表并集。