How to rewrite legacy join syntax *= in SQL Server

Question

I am trying to rewrite legacy join syntax with new standards.

SELECT count(*)
FROM es_dbo.tablTypes t
    ,es_dbo.tablReg r
    ,es_dbo.tabl_PRGandCLI p
WHERE t.ClientType *= r.ClientType
    AND p.ID IN (
        SELECT DISTINCT ClientID
        FROM esinet_dbo.tablReG
        )
    AND t.ClientType IN (@intClientType)

Here is what I am trying.

SELECT count(*)
FROM es_dbo.tablTypes t
LEFT JOIN es_dbo.tablReg r ON t.ClientType = r.ClientType
LEFT JOIN es_dbo.tabl_PRGandCLI p ON p.ID IN (
        SELECT DISTINCT ClientID
        FROM es_dbo.tablReG
        )

I am getting same no of records whether I use LEFT JOIN or INNER JOIN in 2nd part of query. Can anyone explain

You're getting the same records because the data that is there means there's no difference. I think your rewrite should be inner joins, not left, and I don't think you need the in-subquery — Allan S. Hansen, Dec 18 '15 at 13:30
I am not sure if `es_dbo.tabl_PRGandCLI` should be used in INNER JOIN or LEFT JOIN. There is no joining condition — Shantanu Gupta, Dec 18 '15 at 13:32
I think the query you tried replicates the logic correctly. Can you find an example of a row of data that was returned by the old query that is not returned by your new query? — Tab Alleman, Dec 18 '15 at 13:56

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

Try the following:

SELECT count(*)
FROM es_dbo.tablTypes t
    left join es_dbo.tablReg r on t.ClientType = r.ClientType
WHERE t.ClientType IN (@intClientType)
   EXISTS (SELECT 1 FROM esinet_dbo.tablReG p WHERE r.ClientID = p.ID)

1) ~~I assumed @intClientType is a scalar value, so no need for IN~~

2) removed DISTINCT and subquery as you check for existence. EXISTS should be faster as it involves finding the first element, rather than doing some sorting for DISTINCT.

3) *= was replaced with LEFT JOIN, based on discussion from here.

edited May 23 '17 at 12:15

Community

1
1

answered Dec 18 '15 at 13:33

Alexei - check Codidact

22,016
16
145
164

Original query selects multiple columns. I just replaced with COUNT(*) to see result. without parameter value, query was returning over 100 million records. – Shantanu Gupta Dec 18 '15 at 13:38
Ok, I have updated suggested query replacement accordingly – Alexei - check Codidact Dec 18 '15 at 13:49

score 0 · Answer 2 · answered Dec 18 '15 at 13:52

It is neither inner join nor left join according to query it seems like cross join so you can use following query:

SELECT count(*)
FROM es_dbo.tablTypes t
LEFT JOIN es_dbo.tablReg r ON t.ClientType = r.ClientType,
es_dbo.tabl_PRGandCLI p WHERE p.ID IN (
    SELECT DISTINCT ClientID
    FROM es_dbo.tablReG
    )

How to rewrite legacy join syntax *= in SQL Server

2 Answers2