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

select * from orders_t

Type		Query	Output
Inner Join		select * from customers_t c JOIN orders_t o on c.cid = o.cid
Left Outer Join		select * from customers_t c LEFT OUTER JOIN orders_t o on c.cid = o.cid
Right outer join		select * from customers_t c RIGHT OUTER JOIN orders_t o on c.cid = o.cid
Full Join		select * from customers_t c FULL OUTER JOIN orders_t o on c.cid = o.cid
Cross Join		select * from customers_t c CROSS JOIN orders_t o

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