Write without subquery? - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Write without subquery?

Author

Topic

Magua1
Starting Member

15 Posts

Posted - 2015-04-01 : 09:53:39

Hi

Can the below query be rewritten without the subquery?
TIA

SELECT DISTINCT srs_sqe_dqi.sqe_stuc,
srs_sqe_dqi.sqe_esmc,
srs_sqe_dqi.sqe_esgc
FROM [entsql1\entsql1].[sits].[dbo].srs_sqe_dqi
LEFT OUTER JOIN [entsql1\entsql1].[sits].[dbo].srs_esg
ON srs_sqe_dqi.sqe_esmc = srs_esg.esg_esmc
WHERE 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources