When practicing MySQL join table query, we encountered such a problem

– question: query the relevant information of all students whose “biology course” scores are higher than “physics course”

– error instruction:

1 SELECT  student.sid AS 'number', student.sname AS 'name', course.cname AS 'course', score.num AS 'performance' 
2 FROM student INNER JOIN course INNER JOIN score  
3 ON student.sid=score.student_id AND course.cid=score.course_id AND course.cid=2 
4 WHERE score.num < (SELECT score.num FROM course INNER JOIN score ON course.cid=score.course_id AND course.cid=1);

Problematic instructions

– error information:

ERROR 1242 (21000): Subquery returns more than 1 row

– error information translation:

subquery returns more than 1 line

– Analysis and solutions:

1. This problem can be solved by removing the duplicate data

– prevent data from being written repeatedly by adding logic judgment or foreign key during writing

2. Use in, some, any and all keywords to restrict

– the error information comes from the subquery, so it is necessary to modify the conditions of the instructions involved in the subquery

– final resolution instruction:

1 SELECT  student.sid AS 'student.name AS 'name', course.cname AS 'course', score.num AS 'grade'
2 FROM student INNER JOIN course INNER JOIN score
3 ON student.sid=score.student_id AND course.cid=score.course_id AND course.cid=2 
4 WHERE score.num < ANY(SELECT score.num FROM course INNER JOIN score ON course.cid=score.course_id AND course.cid=1);

Final resolution instruction

supplement

Subquery refers to nesting another select statement in a select statement.
in, some, any and all are keywords involved in subquery

– any and = (& gt>=, & lt;, & lt;=, & lt;>) In combination, it respectively represents any data equal to (greater than, greater than or equal to, less than, less than or equal to, not equal to)

The — any keyword must be used with a comparison operator

— any keyword can be understood as “for any value in the column returned by the subquery, if the comparison result is true, return true”

– all can be associated with = (& gt>=, & lt;, & lt;=, & lt;>) In combination, it represents all data equal to (greater than, greater than or equal to, less than, less than or equal to, not equal to) respectively

The — all keyword must be used with a comparison operator

— all keyword can be understood as “for all values in the column returned by the subquery, if the comparison result is true, return true”

– the keyword in has the same effect as the keyword combination “= any”

1 SELECT s1 
2 FROM t1 
3 WHERE s1 =ANY(SELECT s1 FROM t2);
4 -- Effectiveness Equivalent
5 SELECT s1 
6 FROM t1 
7 WHERE s1 IN(SELECT s1 FROM t2);

Examples of in and = any

– notin and “& lt> “All” has the same usage and function

1 SELECT s1 
2 FROM t1 
3 WHERE s1 <>ANY(SELECT s1 FROM t2);
4 -- Effectiveness Equivalent
5 SELECT s1 
6 FROM t1 
7 WHERE s1 NOT IN(SELECT s1 FROM t2);

<> Examples of any and not in

– some is the alias of any

1 SELECT s1 FROM t1 WHERE s1 <> ANY(SELECT s1 FROM t2);
2 -- Effectiveness Equivalent
3 SELECT s1 FROM t1 WHERE s1 <> SOME(SELECT s1 FROM t2);

Examples of any and some

— in terms of understanding, some are easier to explain than any. In the above example, the instruction involving the keyword “some S1 in table t1 are not equal to S1 in table t2”, and the instruction involving the keyword “any” is interpreted as “all S1 in table t1 are not equal to S1 in table t2”