Friday, November 28, 2008

Exploring SQL Server Joins

 

By using joins you can retrieve the data from more than one table based on the condition specified in the join.
below examples are based on the 2 test tables <click here to get the test table script>
select * from customers_t
image
select * from orders_t
image
Type Query Output
Inner Join clip_image002 image select * from customers_t c JOIN orders_t o on c.cid = o.cid
image
Left Outer Join image select * from customers_t c LEFT OUTER JOIN orders_t o on c.cid = o.cid image
Right outer join
image
select * from customers_t c RIGHT OUTER JOIN orders_t o on c.cid = o.cid image
Full Join image select * from customers_t c FULL OUTER JOIN orders_t o on c.cid = o.cid image
Cross Join  
clip_image002[1]
select * from customers_t c CROSS JOIN orders_t o image
Join More than 2 Tables


create table TA(A VARCHAR(2))
create table TB(B VARCHAR(2),A VARCHAR(2))
create table TC(C VARCHAR(2),A VARCHAR(2))
create table TD(D VARCHAR(2),A VARCHAR(2))


INSERT INTO TA SELECT 'A1'
INSERT INTO TA SELECT 'A2'
INSERT INTO TA SELECT 'A3'

INSERT INTO TB SELECT 'B1','A1'
INSERT INTO TB SELECT 'B2','A1'
INSERT INTO TB SELECT 'B3','A1'

INSERT INTO TC SELECT 'C1','A2'
INSERT INTO TC SELECT 'C2','A2'
INSERT INTO TC SELECT 'C3','A2'

INSERT INTO TD SELECT 'D1','A3'
INSERT INTO TD SELECT 'D2','A3'
INSERT INTO TD SELECT 'D3','A3'


SELECT * FROM TA
SELECT * FROM TB
SELECT * FROM TC
SELECT * FROM TD


SELECT * FROM TA 
LEFT OUTER JOIN TB ON TA.A = TB.A
JOIN TC ON TA.A = TC.A
JOIN TD ON TA.A = TD.A







Post a Comment

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate the Mi...

Contributors