1
\$\begingroup\$

I have a table called tblStudent having data as

Student table

I want to retrieve records having single value compared against multiple columns. For example -
find all students having Grade 'C' in any of the subjects.
One way could be - 

SELECT * FROM tblStudent
WHERE Grade_Maths = 'C' OR
Grade_Science = 'C' OR
Grade_English = 'C'

Another (cleaner/shorter) way of achieving same is 

SELECT * FROM tblStudent
WHERE 'C' IN (Grade_Maths, Grade_Science, Grade_English)

Because the table data can be considerably huge in real-time, I am not sure whether second method will always return correct result. Can please suggest if there could be any potential issue with using second approach.

Thanks a lot!

\$\endgroup\$

2 Answers 2

Reset to default
2
\$\begingroup\$

The queries are the same except potentially the second won't use index if any exists on grade_* columns. And this flipped approach Is rather uncommon (at least i see it for first time). Possibly because you wouldnt need such approach if you had a better tables structure.

What if suddently students can study history or biology? You Will need to create new columns And Alter all related queries.

Instead there should be a "subjects" table and an M:N relation table between subjects and students. Your query wont contain any ORs or INs the way it does now. And adding new subjects Is matter of merely inserting new row to subjects table.

\$\endgroup\$
1
  • \$\begingroup\$ Thank you for reply and giving the alternative to avoid using IN. For now, I am making changes to some legacy database stored proc so not in a position to change database structure. I checked there are no index on grade columns, so I feel I can safely make the change without breaking it..fingers crossed it's a massive stored proc with 1400 lines. \$\endgroup\$ Commented Mar 13, 2020 at 9:38
0
\$\begingroup\$

It depends on database that you are using. In case of MySQL database IN will have better performance. For oracle, internally it will use OR 1

\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.