CODEWITHSUNDEEP
database
Mukul Goel
Mukul Goel

Reputation: 1

SQL Query Using IN. I want to Convert query in EXISTS

SELECT 
    lpn.TC_LPN_ID 
FROM LPN lpn 
WHERE lpn.inbound_Outbound_Indicator = 'O' 
    AND lpn.tc_company_id = 1403 
    AND lpn.TC_REFERENCE_LPN_ID **IN**  
        (SELECT 
            l.tc_lpn_id 
         FROM lpn l 
         WHERE l.TC_COMPANY_ID = 1403 
             AND l.inbound_Outbound_Indicator = 'I' 
             AND (l.tc_lpn_id= '000990950' OR l.TC_REFERENCE_LPN_ID = '000990950'))

Want to modify my query to EXISTS.

EXISTS improve performance.

Upvotes: 0

Views: 253

Answers (2)

Bohemian
Bohemian

Reputation: 425208

JOIN improves performance more.

You can express your query as a standard inner join to itself:

SELECT 
    lpn.TC_LPN_ID 
FROM LPN
JOIN lpn l
    ON l.TC_COMPANY_ID = lpn.l.TC_COMPANY_ID
    AND l.tc_lpn_id = lpn.TC_REFERENCE_LPN_ID
    AND l.inbound_Outbound_Indicator = 'I'
    AND l.tc_lpn_id = '000990950' OR l.TC_REFERENCE_LPN_ID = '000990950')
WHERE lpn.inbound_Outbound_Indicator = 'O' 
AND lpn.tc_company_id = 1403

Note that I removed the pointless aliasing of lpn to itself - ie FROM LPN lpn is identical to simply FROM LPN: aliasing a table to its own name has no effect.

Upvotes: 0

hkutluay
hkutluay

Reputation: 6944

SELECT lpn.tc_lpn_id
  FROM lpn lpn
 WHERE lpn.inbound_outbound_indicator = 'O'
   AND lpn.tc_company_id = 1403
   AND EXISTS (
          SELECT 1
            FROM lpn l
           WHERE lpn.tc_reference_lpn_id = l.tc_lpn_id
             AND l.tc_company_id = 1403
             AND l.inbound_outbound_indicator = 'I'
             AND (   l.tc_lpn_id = '000990950'
                  OR l.tc_reference_lpn_id = '000990950'
                 ))

Upvotes: 1

Related Questions