CODEWITHSUNDEEP
sqlt-sql
Nate Pet
Nate Pet

Reputation: 46222

sql subquery from table

I have 2 tables (I am using T-SQL)

Table1

ID  First_Name Last_Name Phone
--  ---------- --------- --------------
1   John       Smith     (643) 434-4343 
2   Dave       Miller    (543) 344-3432
3   Tiffany    Ovally    (434) 343-6598
4   Dan        Davis     (534) 342-9876
5   Mike       Kolis     (454) 345-3434


Table2

Iden   FirstN     LastN      PhoneN
----   ------     -----      ------
J-09   Tiffany    Ovally    (434) 343-6598
K-98   Dan        Davis     (534) 342-9876
W-03   Dave       Miller    (543) 344-3432
C-34   Mike       Kolis     (454) 345-3434

I need to check that key values from Table1 DO NOT exist in table2

I am doing the following

    IF NOT EXISTS(SELECT * FROM Table2 t2
                  WHERE t2.FirstN = (Select First_Name from Table1 where ID = @ID)
                  AND t2.LastN  = (Select Last_Name from Table1 where ID = @ID)  
                  AND t2.PhoneN = (Select Phone from Table1 where ID = @ID)        
                  )
   BEGIN
    .... 
  END

Not sure if there is a more efficient way to do this as I am doing a subquery for each field..

Thanks

Upvotes: 0

Views: 130

Answers (3)

Duane Theriot
Duane Theriot

Reputation: 2185

Your query is looking for records in Table2 that do not match the record in Table1 with an ID of @ID. Using an INNER JOIN here would be better than using the multiple subqueries:

IF NOT EXISTS (SELECT *
               FROM FROM Table1 t1
               INNER JOIN Table2 t2 
                  ON  t1.First_Name = t2.FirstN
                  AND t1.Last_Name = t2.LastN
                  AND t1.Phone = t2.PhoneN
              WHERE t1.ID = @ID)
BEGIN
    ....
END

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85056

You would have to compare execution plans to see how the performance compares, but this seems a little cleaner to me:

SELECT t1.*
FROM Table1 as t1
LEFT JOIN Table2 as t2
ON t1.First_Name = t2.FirstN
   AND t1.Last_Name = t2.LastN
   AND t1.Phone = t2.PHone
WHERE t2.Iden IS NULL

Upvotes: 1

Adam Porad
Adam Porad

Reputation: 14471

You might try looking at the EXCEPT keyword. The brief definition of the EXCEPT functionality is

EXCEPT returns any distinct values from the left query that are not also found on the right query.

The solution using your example tables would be, SELECT t1.First_Name, t1.Last_Name, t1.Phone FROM Table1 t1 EXCEPT SELECT t2.First_Name, t2.Last_Name, t2.Phone FROM Table2 t2

Upvotes: 0

Related Questions