Optimized way to check if record is present in table 1. If not then check table 2, else return default value

Question

Asked in an interview: I have 2 tables, one table has records like ID, Name, address. id(pk) is from 1 to 10000000.

Another table has records from 10000001 to 20000000.

I have to check if a particular ID is present in table 1 or table 2 and return corresponding result.

Because table size is big, have to think an optimized way to do this.

Answer 1

Few ideas on top of my mind.

• In the hive, you can use map-side join which is much faster than usual join when 1 table is large and another is small. (here 2nd table being the id you are searching for)
• You can optimize in the way you store the data. Keeping the data sorted by id column, if such queries are frequent. A columnar format such as orc keeps track of the range of id in each file, resulting in such queries being faster.

Answer 2

declare @ID BIGINT
SET @ID=10000000
IF EXIST(SELECT ID FROM TABLE1 WHERE ID=@ID)
    SELECT ID,NAME,ADDRESS FROM TABLE1 WHERE ID=@ID 
ELSE IF EXIST(SELECT ID FROM TABLE2 WHERE ID=@ID)
    SELECT ID,NAME,ADDRESS FROM TABLE2 WHERE ID=@ID 
ELSE
    SELECT @ID