Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Magua1
Starting Member
15 Posts |
 Posted - 2015-04-01 : 09:53:39
|
| HiCan the below query be rewritten without the subquery?TIASELECT DISTINCT srs_sqe_dqi.sqe_stuc, srs_sqe_dqi.sqe_esmc, srs_sqe_dqi.sqe_esgcFROM [entsql1\entsql1].[sits].[dbo].srs_sqe_dqiLEFT OUTER JOIN [entsql1\entsql1].[sits].[dbo].srs_esgON srs_sqe_dqi.sqe_esmc = srs_esg.esg_esmcWHERE sqe_esmc IS NOT NULL -- Marking scheme (Qual Type) AND srs_sqe_dqi.sqe_ayrc = '2014/5'AND srs_sqe_dqi.sqe_esmc + srs_sqe_dqi.sqe_esgc NOT IN (SELECT esg_esmc + esg_code AS EsgQualGrade FROM sits..srs_esg)ORDER BY sqe_esmc,sqe_esgc |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2015-04-01 : 10:28:15
|
| You can do a left join to sits..srs_esg on srs_sqe_dqi.sqe_esmc + srs_sqe_dqi.sqe_esgc = esg_esmc + esg_code and look for rows where the primary key from sits..srs_esg is null.When you use NOT IN, you have to be careful if the values you are looking for (in this case esg_esmc + esg_code) can be null. You would get unexpected results. For that reason, I prefer NOT EXISTS rather than NOT IN. In the case of IN (rather than NOT IN), using EXISTS rather than IN can be more efficient as well. (That sentence is confusing isn't it? :) |
 |
|
|
Magua1
Starting Member
15 Posts |
Posted - 2015-04-01 : 10:33:31
|
| Thanks James K |
|
|
|
|
|
|
Write without subquery?