Can you suggest me on ways we can rewrite this query by avoiding using of OR condition and achieve below desired result, trying compare performance of SQL using OR condition vs not using OR condition.
Table has students enrolled for different courses, I need to fetch students who all are enrolled for specific course 'Geology'.
Assumptions on table data:
1. Students will enroll for atleast one course
2. ENROLL_ID is PK
WITH t1 AS
(
SELECT 'KJ' Name, 125 ENROLL_ID, 'Psychology' Course1 , 'Mathematics' Course2, 'Physics1' Course3, 'Chemistry' Course4, 'Geology' Course5, 'Physics2' Course6, 'Biology' Course7, 'Computer tech.' Course8 FROM DUAL
UNION ALL
SELECT 'KS' Name, 126 ENROLL_ID, 'Mathematics' Course1 , 'Geology' Course2, 'Physics2' Course3, 'Chemistry' Course4, 'Psychology' Course5, 'Physics1' Course6, 'Biology' Course7, 'Computer tech.' Course8 FROM DUAL
UNION ALL
SELECT 'KB' Name, 127 ENROLL_ID, 'Physics2' Course1 , 'Computer tech.' Course2, 'Mathematics' Course3, 'Chemistry' Course4, 'Biology' Course5, 'Physics1' Course6, 'Psychology' Course7, 'Geology' Course8 FROM DUAL
UNION ALL
SELECT 'KC' Name, 128, 'Physics1' , 'Computer tech.', 'Mathematics', 'Chemistry', 'Biology', 'Physics2', 'Psychology', 'Geology' FROM DUAL
UNION ALL
SELECT 'KD' Name, 129, 'Biology', 'Physics2', 'Physics1' , 'Computer tech.', 'Mathematics', 'Chemistry','Psychology', 'History' FROM DUAL
UNION ALL
SELECT 'KE' Name, 130, 'Computer tech.', 'History', 'Physics1' , 'Geology', 'Psychology', '','', '' FROM DUAL
UNION ALL
SELECT 'KE' Name, 131, 'Geology', '', '' , '', '', '','', '' FROM DUAL
)
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE1 = 'Geology'
OR t1.COURSE2 = 'Geology'
OR t1.COURSE3 = 'Geology'
OR t1.COURSE4 = 'Geology'
OR t1.COURSE5 = 'Geology'
OR t1.COURSE6 = 'Geology'
OR t1.COURSE7 = 'Geology'
OR t1.COURSE8 = 'Geology' ORDER BY 2;
Using Union
WITH t1 AS
(
SELECT 'KJ' Name, 125 ENROLL_ID, 'Psychology' Course1 , 'Mathematics' Course2, 'Physics1' Course3, 'Chemistry' Course4, 'Geology' Course5, 'Physics2' Course6, 'Biology' Course7, 'Computer tech.' Course8 FROM DUAL
UNION ALL
SELECT 'KS' Name, 126 ENROLL_ID, 'Mathematics' Course1 , 'Geology' Course2, 'Physics2' Course3, 'Chemistry' Course4, 'Psychology' Course5, 'Physics1' Course6, 'Biology' Course7, 'Computer tech.' Course8 FROM DUAL
UNION ALL
SELECT 'KB' Name, 127 ENROLL_ID, 'Physics2' Course1 , 'Computer tech.' Course2, 'Mathematics' Course3, 'Chemistry' Course4, 'Biology' Course5, 'Physics1' Course6, 'Psychology' Course7, 'Geology' Course8 FROM DUAL
UNION ALL
SELECT 'KC' Name, 128, 'Physics1' , 'Computer tech.', 'Mathematics', 'Chemistry', 'Biology', 'Physics2', 'Psychology', 'Geology' FROM DUAL
UNION ALL
SELECT 'KD' Name, 129, 'Biology', 'Physics2', 'Physics1' , 'Computer tech.', 'Mathematics', 'Chemistry','Psychology', 'History' FROM DUAL
UNION ALL
SELECT 'KE' Name, 130, 'Computer tech.', 'History', 'Physics1' , 'Geology', 'Psychology', '','', '' FROM DUAL
UNION ALL
SELECT 'KE' Name, 131, 'Geology', '', '' , '', '', '','', '' FROM DUAL
)
SELECT NAME, ENROLL_ID
FROM
(
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE1 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE2 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE3 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE4 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE5 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE6 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE7 = 'Geology'
UNION
SELECT NAME, ENROLL_ID
FROM t1
WHERE t1.COURSE8 = 'Geology')
ORDER BY 2;
Thanks for your time.