SQL 的JOIN ON连表查询注意事项(查偏科学生的例子)

目录

1、关联查询时，别名随便起，极限情况下甚至中文也可以(但不提倡)，注意在多个子查询内，对同一个表的同一个字段可以起不同的别名，在外面join on和where时也是互不影响的.

2.当left join或from后面跟的是子查询，即当查询内容是子查询(a和b)时，，如果给子查询中的字段取了别名(pid)，那么在外层联表这两个子查询on和where时用a.就必须要使用别名来.出来了，必须要和各自子查询里面取的别名对的上 如果还是使用原字段名去on会导致查询时找不到字段，会报错：

[Err] 1054 - Unknown column '原字段名' in 'on clause'

3、表与表在使用ON连表必须严格用字段名，即使给字段取了别名，也不能用起的别名，否则会找不到详细的字段，报错：

Oracle 报错：ORA-00904 “p”."别名" : 标识符无效

MySQL报错：[Err] 1054 - Unknown column 'p.别名' in 'on clause'

​编辑

​编辑

4.关联表时注意先后关系，注意要先声明用到哪个表后，再使用其.点出里面的字段；比如到22行的时候s表还没有声明，就先使用的话，就会报异常

5.在连表查询时，若与关联的表有重复的字段，就不要用select*来直接获取所有字段的值了，需要一个一个写完整，并起别名,否则报错:

[Err] 1060 - Duplicate column name '重复的字段'​编辑

6、如果查询内容是子查询关联的结果，那么最外层一般直接select*，因为是连几张表或自己查，会指定好字段，外层不需要额外指定

7、select的时候可以from一个子查询()a，不是非要from一个表，同样left join也不是非要连上一个表,也可以连上一个子查询()b

---

三种写法速记

-- 两个子查询左连接  分别是语文成绩和数学成绩  条件是pid相等  条件偏科
-- 查询人员表 再左连接成绩表s1(语文)和成绩表s2(数学) 条件是偏科
-- from后面跟所有表,where后面些条件 把表关系用and连起来

现有：三张表 人员表、课程表、分数表；其中人员表和课程表是独表，成绩表为联表；

需求：查询语文分数大于数学分数（即偏科）的学生信息 

分析：显然,查每一个偏科的人,都需要从成绩表查此人的两条数据(语/数)出来进行分数比较,所以这里如果用到左连接,那么一定需要关联两次成绩表，on到同一个人的标识，即学号；再在最后定下偏科的where规则，即此人的语文成绩大于数学成绩完成查询；

然后，如果不使用左连接，在from时，也要在后面跟上两次成绩表，并取别名（s1/s2）详见博客最尾部展示了其他的几种查询方法

人员表(Person)id，name      课程表(Class)  id，name      成绩表(Score)  id，pid，cid，fen

1、关联查询时，别名随便起，极限情况下甚至中文也可以(但不提倡)，注意在多个子查询内，对同一个表的同一个字段可以起不同的别名，在外面join的on和where时也是互不影响的.

2.left join或from后面跟的是子查询中的字段若取了别名，那么在外层on联表或where写条件时，就必须要使用别名来点出各自的字段了，：

当left join或from后面跟的是子查询，即当查询内容是子查询(a和b)时，，如果给子查询中的字段取了别名(pid)，那么在外层on联表或where写条件时，用子查询的字段就必须要使用别名来.出来，并且必须要和各自子查询里面取的别名对的上，因为会出现自己连接自己的情况，字段会重，如果还是使用原表字段名去on会导致查询时不能确定是哪个子查询里面的字段，会报错：

[Err] 1054 - Unknown column '原字段名' in 'on clause'

联表left join子查询on错误示例;

查询from表where条件错误示例; 

3、表与表在使用ON连表必须严格用字段名，即使给字段取了别名，也不能用起的别名，否则会找不到详细的字段，报错：

Oracle 报错：ORA-00904 “p”."别名" : 标识符无效

MySQL报错：[Err] 1054 - Unknown column 'p.别名' in 'on clause'

oracle数据库:

mysql数据库: 

4.关联表时注意先后关系，注意要先声明用到哪个表后，再使用其.点出里面的字段；比如到22行的时候s表还没有声明，就先使用的话，就会报异常

[Err] 1054 - Unknown column 's.cid' in 'on clause'

5.在连表查询时，若与关联的表有重复的字段，就不要用select*来直接获取所有字段的值了，需要一个一个写完整，并起别名,否则报错:

[Err] 1060 - Duplicate column name '重复的字段'

6、如果查询内容是子查询关联的结果，那么最外层一般直接select*，因为是连几张表或自己查，会指定好字段，外层不需要额外指定

7、select的时候可以from一个子查询()a，不是非要from一个表，同样left join也不是非要连上一个表,也可以连上一个子查询()b

select * from

(根据pid过滤出语文成绩) a

left ioin (根据pid过滤出语文成绩) b  on a.pid=b.pid

where a.fen>b.fen

附:

分析：查每一个偏科的人,都需要从成绩表查此人的两条数据(语/数)出来进行分数比较,所以这里如果用到左连接,那么一定需要关联两次成绩表，on到同一个人的标识，即学号；最后再来制定where规则：即此人的语文成绩大于数学成绩完成查询；

如果不使用左连接，在from时，也要在后面跟上两次成绩表，并取别名（s1/s2）后面第二种示例

一.这是几种方法中使用了左连接并最简洁的SQL：

SELECT * FROM person p 
LEFT JOIN    score s1 on s1.pid=p.id and s1.cid='101'
LEFT JOIN    score s2 on s2.pid=p.id and s2.cid='102'
where   s1.fen>s2.fen

如果觉得查询结果乱，可以将select* 改为别名：

SELECT  p.id 学号,p.namee 名称,s1.fen 语文成绩,s2.fen 数学成绩 FROM person p 
LEFT JOIN    score s1 on s1.pid=p.id and s1.cid='101'
LEFT JOIN    score s2 on s2.pid=p.id and s2.cid='102'
where   s1.fen>s2.fen            查询结果如下：

二。如果不使用左连接查询，在from时，也要在后面跟上两次成绩表，并取别名（s1/s2）

SELECT * FROM person p,score s1, score s2
WHERE p.id=s1.pid
AND p.id=s2.pid
AND s1.cid=(SELECT id FROM class WHERE namee='语文')
AND s2.cid='102'     ←←←(这里102可以如上一行一样,用内连接根据课程名查课程id)
AND s1.fen>s2.fen

 如果觉得查询结果乱，可以将select* 改为别名：

SELECT p.id 学号,p.namee 名称,s1.fen 语文成绩,s2.fen 数学成绩 FROM person p,score s1, score s2
WHERE p.id=s1.pid
AND p.id=s2.pid
AND s1.cid=(SELECT id FROM class WHERE namee='语文')
AND s2.cid='102'     ←←←(这里102可以如上一行一样,用内连接根据课程名查课程id)
AND s1.fen>s2.fen

还发现了CSDN编辑页面CV到SQL查询页面的一个小bug:

---2022/11/8