Sql Joins

Sql Joins

• Sql joins are used to get data from two or more tables according to the join condition.
• We implement primary foreign key relationships on the tables and according to this relationships we make joins on the tables and get data from more than one table.

 Types of Joins

We have three types of joins in sql server

1. Inner Join
2. Outer Join
3. Self  Join

Inner Join :

In Inner Join returns only those rows that have a match in both joined tables.

Syntax : 

SELECT t1.column_name,t2.column_name

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Example :

SELECT c.CustomerName,c.LastName,I.InvoiceNo

FROM CustomerDetails c

INNER JOIN InvoiceDetails I

ON u.CustID=I.CustID

Output of the above query is :
 

CustmerName	LastName	InvoiceNo
Kiran	rattan	534524
Bhavnishx	rattan	213424
Prince	Kumar	323476
Prince	Kumar	543233
Kiran	rattan	238756

 

Types of Inner join

Equi Join
Natural Join
Cross Join


Equi Join

The Equi Join returns only those rows that have a match in both joined tables.
In Equi join we use "=" sign between the condition. If cannot use other operators in Equi joins.
Syntax

SELECT t1.column_name,t2.column_name

FROM table_name1 t1

INNER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Example

SELECT c.CustomerName,c.LastName,I.InvoiceNo

FROM CustomerDetails c

INNER JOIN InvoiceDetails I

ON u.CustID=I.CustID

Output of the above query is :
 

CustmerName	LastName	InvoiceNo
Kiran	rattan	534524
Bhavnish	Bakshi	213424
Prince	Kumar	323476
Prince	Kumar	543233
Kiran	rattan	238756

Cross Join

Cross join basically a Cartesian product of the tables that involved in the join.
Resulted rows basically the number of rows in one table multiplied by the number of rows in the other table.

Syntax

SELECT * FROM table_name1

CROSS JOIN table_name2

OR

SELECT * FROM table_name1,table_name2

Example

SELECT * FROM CustomerDetails

CROSS JOIN InvoiceDetails

Output is following :

CustID	CustomerName	FirstName	LastName	InvoiceID	InvoiceNo	CustID
1	KiranRattan	Kiran	Rattan	1	45224	1
1	KiranRattan	Kiran	Rattan	2	653424	2
1	KiranRattan	Kiran	Rattan	3	347776	3
1	KiranRattan	Kiran	Rattan	4	653233	3
1	KiranRattan	Kiran	Rattan	5	348756	1
2	BhavnishBakshi	Bhavnish	Bakshi	1	543224	1
2	BhavnishBakshi	Bhavnish	Bakshi	2	213424	2
2	BhavnishBakshi	Bhavnish	Bakshi	3	977776	3
2	BhavnishBakshi	Bhavnish	Bakshi	4	323233	3
2	BhavnishBakshi	Bhavnish	Bakshi	5	998756	1
3	PrinceKumar	Prince	Kumar	1	543224	1
3	PrinceKumar	Prince	Kumar	2	213424	2
3	PrinceKumar	Prince	Kumar	3	977776	3
3	PrinceKumar	Prince	Kumar	4	323233	3
3	PrinceKumar	Prince	Kumar	5	998756	1

Outer Joins

1. Left Outer Join
2. Right Outer Join
3. Full Outer Join



 
Left Outer Join

• This Join returns all records from the left table and the matched records from the Right table.
• Left table means the table left in the join
• Right table means the table Right in the join

Syntax

SELECT Column_List FROM table_name1 t1

LEFT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

 

Right Outer Join

• This Join returns all records from the Right table and the matched records from the Left table.

Syntax

SELECT Column_List FROM table_name1 t1

RIGHT OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

 

Full Outer join

• This join returns all the records matching and non matching from both the tables.

Syntax

SELECT Column_List FROM table_name1 t1

FULL OUTER JOIN table_name2 t2

ON t1.column_name=t2.column_name

Left Excluding JOIN

 This will return all rows of the left table which do not match with any record of the right table.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE B.UserId IS NULL

Right Excluding JOIN

  This will return all rows of the right table which do not match with any record of the left table.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE A.Id IS NULL 

Outer Excluding JOIN

 This will all return  all rows from left table and right table except the matched rows.

SELECT *
FROM Table_1 A
LEFT JOIN Table_2 B
ON A.Id = B.UserId
WHERE A.Id IS NULL OR 
B.UserId IS NULL

Self Join

• This Join returns records having some relation with the other records in the same table

Example

Id	Name	CaptainId
1	Tejpal	2
2	Suresh	4
3	Mahesh	2
4	Kiran	1
5	Rajender	1
6	Mahendra	3
7	Dheeraj	3

select s2.Name,s1.Name as 'Captain'

from StudentDetails s1

INNER JOIN StudentDetails s2

on s1.Id=s2.CaptainId


Output :

Name	Captain
Kiran	Tejpal
Rajender	Tejpal
Tejpal	Suresh
Mahesh	Suresh
Mahendra	Mahesh
Dheeraj	Mahesh
Suresh	Kiran