Replace IN with EXISTS - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Replace IN with EXISTS

Author

Topic

ST0320
Starting Member

6 Posts

Posted - 2005-05-10 : 12:01:39

I have a query as below:
SELECT DISTINCT
C1.EXTNL_CD
,C1.SYS_NM
,C2.SYS_NM
FROM SESSION.TEMP_PART_USE A1
JOIN MANG_PRT_USE_CTGRY B1
ON B1.MANG_PRT_USE_ID = A1.MANG_PRT_USE_ID
JOIN CTGRY_PRT C1
ON C1.CTGRY_PRT_ID = B1.CTGRY_PRT_ID
JOIN CTGRY_PRT C2
ON C2.CTGRY_PRT_ID = C1.PARNT_ID
WHERE C2.SYS_NM IN ('RISK_GROUP',
'PRODUCT_LINE',
'RISK_TYPE',
'BUSINESS_TYPE',
'POLICY_FORM_GROUP',
'LINE_OF_BUSINESS')
AND (A1.PARNT_ID = '' OR A1.PARNT_ID IS NULL)
WITH UR;

Now I need to replace 'IN' in WHERE clause with 'EXISTS'. Any helpers??

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 12:12:27

>>Now I need to replace 'IN' in WHERE clause with 'EXISTS'.

Why?

- Jeff

ST0320
Starting Member

6 Posts

Posted - 2005-05-10 : 13:39:48

Performance problem. So I need to replace with EXISTS clause.

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-10 : 13:46:24

WITH?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-10 : 13:57:07

It would help greatly if you gave us:

1) The DDL of your tables, including all indexes you have set up and primary keys of course

2) Some sample data for those tables -- just enough so that we can visualize your problem and test it

3) The desired results based on that sample data, with an explaination of how to get those results in non-SQL terms.

- Jeff

ST0320
Starting Member

6 Posts

Posted - 2005-05-10 : 14:22:33

The problem is, I do not have proper data to test the SQL for myself. I have to just see the query and modify it to the nearest probability that it will work. The tables are temporary tables and they will be created when a whole set of transactions are run. I am working at the backend and I am not a part of online team. So I cannot literally define as to what data will be present in the table at a certain point in time.

Well, I can give a simplified query without Joins etc.

SELECT CRETR_ID FROM CTGRY_PRT
WHERE SYS_NM IN (
'RISK_GROUP',
'PRODUCT_LINE',
'RISK_TYPE',
'BUSINESS_TYPE',
'POLICY_FORM_GROUP',
'LINE_OF_BUSINESS');

I have a list of values for SYS_NM in the CTGRY_PRT table. And I need CRETR_ID of those records with the values 'RISK_GROUP', 'PRODUCT_LINE' etc.

It would be great if you can help me replace the clause so that i get the desired output. I tried replacing the list of values with a select subquery but it gave me different results coz my original query does not have a select subquery in IN clause. Rather it has a list of values.

If anyone can come up with any alternate solution, that is welcome. Not only EXISTS but what can be used instead of it, will also help.

I dont know how far I am clear in bringing my point home !!!

andy8979
Starting Member

36 Posts

Posted - 2005-05-10 : 15:22:05

You could try by creating a temporary table and inserting the records which you check in the "in clause" in this table and then try using some of the outer join I have used outer join it is more faster than using the "in clause" but in my case I had the data to be compared in a table, so if the records to compare i.e. " 'RISK_GROUP',
'PRODUCT_LINE',
'RISK_TYPE',
'BUSINESS_TYPE',
'POLICY_FORM_GROUP',
'LINE_OF_BUSINESS'" are few records then insert them into the temp. table and use the outer joins.

Anuj

ST0320
Starting Member

6 Posts

Posted - 2005-05-10 : 15:59:02

Thanks Anuj ! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources