:::: MENU ::::

Complete the T-SQL query to get the required result

  • Feb 03 / 2012
  • 2
DBA Interview questions, dbDigger, Joins, T-SQL Interview Questions, T-SQL Tips and Tricks

Complete the T-SQL query to get the required result

I came across an interesting question. To get those values of table1 that are not there in table2. This task can be completed very easily by using the EXCEPT operation. However i was required to get the result by appending just a single condition in provided T-SQL code. The trick was about using joins concept to get the required result. Create and populate two tables through following code.

 
-- Create first table 
create table Table1 (id smallint, Item varchar(25))  
GO  
 
-- Create second table  
create table Table2 (id smallint, Item varchar(25)) 
GO  

-- Populate table1  
insert into table1 select 1,'Item1'  
union all  select 2, 'Item2' 
union all  select 3, 'Item3'  
union all  select 4, 'Item4'  
GO  

-- Populate table2  
insert into table2 values (3,'Item3')  
GO  

The required result is as shown in the snap

Solve T-SQL puzzle

And just add a single condition either in join clause or in where clause of following query to get the result

 -- Complete the script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 -- Add a condition here to get the required result  

And below is the single line condition that is required to get the required result

 
where t2.id is null  

Complete query would be as following

 -- Complete script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 where t2.id is null  
  • Linda

    Thanks for sharing an interesting question!Interview Questions

  • Linda

    Different jobs require you to dress differently. It is standard practice that, creative’s do not attend interviews in three-piece suits or are lawyers in jeans or flip-flops.

Consult us to explore the Databases. Contact us