MySQL case statement with two tables

Question

How can I improve the following working SQL statement for better performance and consistency?

SELECT if(roll is null,0,roll) as roll, 
 if(cpapername is null,0,cpapername) as compulsory,
 if(corepapername is null,0,corepapername) as Core, 
 if(e1papername is null,0,e1papername) as Elective1,
 if(e2papername is null,0,e2papername) as Elective2
 FROM(
 Select student.univ_roll as roll, student.name as name, student.rollno as rollno, 
 (CASE WHEN student.compulsory = paper.subject
 THEN (paper.paper_name) END) AS cpapername,
 (CASE WHEN student.core= paper.subject
 THEN (paper.paper_name) END) AS corepapername,
 (CASE WHEN student.elective_I= paper.subject
 THEN (paper.paper_name) END) AS e1papername,
 (CASE WHEN student.elective_II= paper.subject
 THEN (paper.paper_name) END) AS e2papername
 FROM student, paper WHERE student.semester='First' 
 AND student.batch='2014' AND student.class='BSc' 
 AND student.semester=paper.semester AND student.batch=paper.batch 
 AND student.class=paper.class AND student.regd='1')r

Any suggestions and alternative solution are greatly welcome and appreciated as I am looking for better ways to code.

• 200_success: Are there NOT NULL constraints on columns paper.name, paper.subject, student.compulsory, student.core, etc.?
• Mawia HL: In my next version, instead of if(), I used group_concat which eliminates null cells and outputs single row.
• Brythan: GROUP_CONCAT will probably produce the wrong output if you add a GROUP BY clause to your SQL. I would avoid this use of GROUP_CONCAT for that reason. You're really not using it as intended but for a side effect of its behavior.

Answer 1

You can simplify if(col is null,0,col) as ifnull(col, 0).

The SQL keywords are inconsistently formatted. Sometimes you write SELECT, other times as Select. I suggest to stick to all caps always. The same goes for if (and ifnull) and as too. It's easier to read the code when the writing is consistent.

The last line of your script is very long, and it's difficult to read when I have to scroll to the right and the left part goes out of sight. I suggest to add a few line breaks in there to make the code fit within one horizontal window. (I see you updated your original post, now it's good.)

It would be better to indent the sub-query more, and to put the closing ) on a new line to make it more visible. Something like this:

SELECT IFNULL(roll, 0) AS roll, 
 IFNULL(cpapername, 0) AS compulsory,
 IFNULL(corepapername, 0) AS Core, 
 IFNULL(e1papername, 0) AS Elective1,
 IFNULL(e2papername, 0) AS Elective2
 FROM (
 SELECT student.univ_roll AS roll, student.name AS name, student.rollno AS rollno, 
 (CASE WHEN student.compulsory = paper.subject
 THEN (paper.paper_name) END) AS cpapername,
 (CASE WHEN student.core= paper.subject
 THEN (paper.paper_name) END) AS corepapername,
 (CASE WHEN student.elective_I= paper.subject
 THEN (paper.paper_name) END) AS e1papername,
 (CASE WHEN student.elective_II= paper.subject
 THEN (paper.paper_name) END) AS e2papername
 FROM student, paper WHERE student.semester='First' 
 AND student.batch='2014' AND student.class='BSc' 
 AND student.semester=paper.semester AND student.batch=paper.batch 
 AND student.class=paper.class AND student.regd='1'
 ) r

• Mawia HL: what about group_concat? I don't know much about its theory.
• Brythan: You have both 'as' and 'AS' in your version. I would consider 'AS' to be more consistent with the other keywords. You also write 'ifnull' instead of 'IFNULL'.
• Clockwork-Muse: ... the standard recommendation now is to not use the implicit-join syntax (comma-separated FROM clause), but to explicitly spell out joins.
• janos: @Clockwork-Muse that's a very good point. I suggest to post an answer with that!