:::: MENU ::::

Posts Categorized / Joins

  • 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  
  • May 20 / 2008
  • 0
dbDigger, Joins

T-SQL joins refresher for beginners

Joins are a mandatory concept for T-SQL skill. I have prepared following refresher for preliminary learners of T-SQL. Create Tables to use in refresher

 
USE pubs  
 GO  
 CREATE TABLE t1(f1 INT)  
 CREATE TABLE t2(f1 INT)  
 CREATE TABLE t3(f1 INT)  
 GO  

now populate the tables

 
--=========for t1==================  
 SET NOCOUNT ON  
 DECLARE @j INT  
 SET @j = 1  
 WHILE @j <= 10   
 BEGIN  
  INSERT INTO t1 VALUES(@j) SET @j = @j + 1   
 END   
 GO   
 --=========for t2==================   
 SET NOCOUNT ON   
 DECLARE @k INT   
 SET @k = 6   
 WHILE @k <= 15   
 BEGIN   
 INSERT INTO t2 VALUES(@k)   
 SET @k = @k + 1   
 END   
 GO  
  --==========for t3=================   
 INSERT INTO t3 VALUES(6)   
 INSERT INTO t3 VALUES(2)  
 INSERT INTO t3 VALUES(3)  
 INSERT INTO t3 VALUES(12)   
 INSERT INTO t3 VALUES(13)   
 GO   

Now t1, t2, t3 has following values

t1 t2 t3
1 6 6
2 7 7
3 8 8
4 9 9
5 10 10
6 11 11
7 12 12
8 13 13
9 14
10 15

  • Inner Join Query

Inner joins return rows only when there is at least one row from both tables, that matches the join condition.
Inner joins eliminate the rows that do not match with a row from the other table

 
SELECT * FROM  
 T1 JOIN t2  
 ON t1.f1 = t2.f1  
 GO  

Inner Join

 
SELECT * FROM  
 t1 INNER JOIN t2  
 ON t1.f1 = t2.f1  
 GO  

EQUI Join

 
SELECT * FROM  
 t1,t2  
 WHERE  
 t1.f1 = t2.f1  
 GO  
================================
  • Outer Join Query

Outer joins, return all rows from at least one of the tables or views mentioned in the FROM clause, as long as those rows meet any WHERE or HAVING search conditions.
All rows are retrieved from the left table referenced with a left outer join, and all rows from the right table referenced in a right outer join.
All rows from both tables are returned in a full outer join.
Both queries below will have same result, as both use left outer join

 
SELECT t1.f1, t2.f1  
 FROM t1 LEFT OUTER JOIN t2  
 ON (t1.f1 = t2.f1)  
 GO  
 --==========================  
 SELECT t1.f1, t2.f1  
 FROM t1 LEFT JOIN t2  
 ON (t1.f1 = t2.f1)  
 GO  

Right outer join is used to get all values from right side table in query, ignoring the fact that values match in table on left side or not.

Both queries below will have same result, as both use right outer join

 SELECT t1.f1, t2.f1  
 FROM t1 RIGHT OUTER JOIN t2  
 ON (t1.f1 = t2.f1)  
 GO  
 --==============================  
 SELECT t1.f1, t2.f1  
 FROM t1 RIGHT JOIN t2  
 ON (t1.f1 = t2.f1)  
 GO  

Full outer join is used to get values from both the table, either values are matched in both tables or not.

Both queries below will have same result, as both use full outer join

 
SELECT t1.f1, t2.f1  
 FROM t1 FULL OUTER JOIN t2  
 ON (t1.f1 = t2.f1)  
 ORDER BY t1.f1  
 GO  
 --======================  
 SELECT t1.f1, t2.f1  
 FROM t1 FULL JOIN t2  
 ON (t1.f1 = t2.f1)  
 ORDER BY t1.f1  
 GO  
  • Multiple Joins

JOINS make much sense between two tables, but can obviously be extended to more than two tables.
Full outer join in three tables will get values from all three tables

 
SELECT a.f1, b.f1, c.f1  
 FROM t1 a  
 FULL OUTER JOIN t2 b ON (a.f1 = b.f1)  
 FULL OUTER JOIN t3 c ON (a.f1 = c.f1)  
 GO  
  • Self Joins

When the same table is used in a query with two different aliases,
such a join is referred to as a self-join.

  • Cross Joins

A cross join is referred to as a Cartesian product,which means, for each element in set-A pick all the values from set-B

Consult us to explore the Databases. Contact us