When the user clicks a delete button in my UI to delete a product I need to do a fast check to see if it is a foreign key in any of the 4 tables (Table1, Table2, Table3, Table4). If it isn't then I can proceed with the delete. If it is in a single one of them I can't delete it.
Some of these tables have thousands of records and I already learned the hard way that using joins is not the best way because the query takes minutes to complete.
I figured union might be the best way but I am wondering if there is a way I can further enhance it. Or even possibly send back which tables it is involved in so I can give the user a descriptive message on why they can't delete the Product.
Here is what I have so far and it is really fast, but returns thousands of 1's when the product exists all over the place. I suppose I can just do a single or default and if not null then don't let them delete.
select ProductId from Table1
select ProductId from Table2
select ProductId from Table3
select ProductId from Table4
where ProductId = 1000
Here is a method with
select (case when exists (select 1 from table1 where productId = 1000) then 1 when exists (select 1 from table2 where productId = 1000) then 1 when exists (select 1 from table3 where productId = 1000) then 1 when exists (select 1 from table4 where productId = 1000) then 1 else 0 end)