Delete records by joining table in SQL Server

I want to DELETE some of my records from main table if their entries are available in another table. How can we do it?

Suppose we have two table TableB and TableC

If we found any entry in TableC then delete corresponding records from TableB.

TableB contains 100 records as

SELECT * FROM TableB

 

TableC contains 5 records as

SELECT * FROM TableC

One way of deleting records from TableB is by using IN clause as

DELETE FROM TableB WHERE ID IN (SELECT TableID FROM TableC)

IN clause query delete records successfully but remembers that IN clause is slower than JOINS.

How can we do it using JOINS?

I tried following query first as

DELETE FROM TableB b INNER JOIN TableC c on b.id = c.bid

I received syntax error as below

 

Correct query is

DELETE FROM b FROM TableB b INNER JOIN TableC c on b.id = c.id

Now if I check TableB, I am getting 95 records

Please leave your comments or share this tip if it’s useful for you.