3
\$\begingroup\$

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.

\$\endgroup\$
3
  • \$\begingroup\$ Are there NOT NULL constraints on columns paper.name, paper.subject, student.compulsory, student.core, etc.? \$\endgroup\$ Oct 28, 2014 at 21:21
  • \$\begingroup\$ In my next version, instead of if(), I used group_concat which eliminates null cells and outputs single row. \$\endgroup\$
    – Mawia HL
    Oct 28, 2014 at 22:27
  • \$\begingroup\$ 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. \$\endgroup\$
    – Brythan
    Oct 29, 2014 at 0:57

1 Answer 1

2
\$\begingroup\$

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
\$\endgroup\$
4
  • \$\begingroup\$ what about group_concat? I don't know much about its theory. \$\endgroup\$
    – Mawia HL
    Oct 28, 2014 at 20:06
  • 1
    \$\begingroup\$ 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'. \$\endgroup\$
    – Brythan
    Oct 29, 2014 at 0:59
  • \$\begingroup\$ ... the standard recommendation now is to not use the implicit-join syntax (comma-separated FROM clause), but to explicitly spell out joins. \$\endgroup\$ Oct 29, 2014 at 10:04
  • 1
    \$\begingroup\$ @Clockwork-Muse that's a very good point. I suggest to post an answer with that! \$\endgroup\$
    – janos
    Oct 29, 2014 at 10:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.