Search This Blog

Friday, September 13, 2013

What is JOIN in SQL ?

JOIN :

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements. Most complex queries in an SQL database management system involve join commands.

Select * from city

sno
city
1
Delhi
2
Kanpur
3
Lucknow
4
Aligarh
5
Alur
6
Ambaliyasan
7
Anandnagaar

select * from State

sno
city
state
1
Lucknow
UP
2
Kanpur
UP
3
Anandnagaar
Assam
4
Ambaliyasan
Gujarat
5
Ambaji
Gujarat
6
Aligarh
UP
7
Delhi
Delhi
8
Alandha
Karnataka
9
Achhnera
UP

There are different types of joins.

INNER JOIN:

This join returns rows when there is at least one match in both the tables.

select * from State S inner join city c on s.city=c.city


sno
city
   state
 sno
  city
1
Lucknow
UP
3
Lucknow
2
Kanpur
UP
2
Kanpur
3
Anandnagaar
Assam
7
Anandnagaar
4
Ambaliyasan
Gujarat
6
Ambaliyasan
6
Aligarh
UP
4
Aligarh
7
Delhi
Delhi
1
Delhi

select s.state,c.city from State S inner join city c on s.city=c.city

state
city
UP
Lucknow
UP
Kanpur
Assam
Anandnagaar
Gujarat
Ambaliyasan
UP
Aligarh
Delhi
Delhi

LEFTOUTER JOIN:

This join returns all the rows from the left table  with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

select * from State S left outer join city c on s.city=c.city

sno
city
state
sno
city
1
Lucknow
UP
3
Lucknow
2
Kanpur
UP
2
Kanpur
3
Anandnagaar
Assam
7
Anandnagaar
4
Ambaliyasan
Gujarat
6
Ambaliyasan
5
Ambaji
Gujarat
NULL
NULL
6
Aligarh
UP
4
Aligarh
7
Delhi
Delhi
1
Delhi
8
Alandha
Karnataka
NULL
NULL
9
Achhnera
UP
NULL
NULL

RIGHTOUTER JOIN:

This join returns all the rows from the right table  with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
select * from State S right outer join city c on s.city=c.city

sno
city
state
sno
city
7
Delhi
Delhi
1
Delhi
2
Kanpur
UP
2
Kanpur
1
Lucknow
UP
3
Lucknow
6
Aligarh
UP
4
Aligarh
NULL
NULL
NULL
5
Alur
4
Ambaliyasan
Gujarat
6
Ambaliyasan
3
Anandnagaar
Assam
7
Anandnagaar

FULL OUTER JOIN:

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

select * from State S full outer join city c on s.city=c.city

sno
city
state
sno
city
1
Lucknow
UP
3
Lucknow
2
Kanpur
UP
2
Kanpur
3
Anandnagaar
Assam
7
Anandnagaar
4
Ambaliyasan
Gujarat
6
Ambaliyasan
5
Ambaji
Gujarat
NULL
NULL
6
Aligarh
UP
4
Aligarh
7
Delhi
Delhi
1
Delhi
8
Alandha
Karnataka
NULL
NULL
9
Achhnera
UP
NULL
NULL
NULL
NULL
NULL
5
Alur


CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

sno
city
state
sno
city
1
Lucknow
UP
1
Delhi
2
Kanpur
UP
1
Delhi
3
Anandnagaar
Assam
1
Delhi
4
Ambaliyasan
Gujarat
1
Delhi
5
Ambaji
Gujarat
1
Delhi
6
Aligarh
UP
1
Delhi
7
Delhi
Delhi
1
Delhi
8
Alandha
Karnataka
1
Delhi
9
Achhnera
UP
1
Delhi
1
Lucknow
UP
2
Kanpur
2
Kanpur
UP
2
Kanpur
3
Anandnagaar
Assam
2
Kanpur
4
Ambaliyasan
Gujarat
2
Kanpur
5
Ambaji
Gujarat
2
Kanpur
6
Aligarh
UP
2
Kanpur
7
Delhi
Delhi
2
Kanpur
8
Alandha
Karnataka
2
Kanpur
9
Achhnera
UP
2
Kanpur
1
Lucknow
UP
3
Lucknow
2
Kanpur
UP
3
Lucknow
3
Anandnagaar
Assam
3
Lucknow
4
Ambaliyasan
Gujarat
3
Lucknow
5
Ambaji
Gujarat
3
Lucknow
6
Aligarh
UP
3
Lucknow
7
Delhi
Delhi
3
Lucknow
8
Alandha
Karnataka
3
Lucknow
9
Achhnera
UP
3
Lucknow
1
Lucknow
UP
4
Aligarh
2
Kanpur
UP
4
Aligarh
3
Anandnagaar
Assam
4
Aligarh
4
Ambaliyasan
Gujarat
4
Aligarh
5
Ambaji
Gujarat
4
Aligarh
6
Aligarh
UP
4
Aligarh
7
Delhi
Delhi
4
Aligarh
8
Alandha
Karnataka
4
Aligarh
9
Achhnera
UP
4
Aligarh
1
Lucknow
UP
5
Alur
2
Kanpur
UP
5
Alur
3
Anandnagaar
Assam
5
Alur
4
Ambaliyasan
Gujarat
5
Alur
5
Ambaji
Gujarat
5
Alur
6
Aligarh
UP
5
Alur
7
Delhi
Delhi
5
Alur
8
Alandha
Karnataka
5
Alur
9
Achhnera
UP
5
Alur
1
Lucknow
UP
6
Ambaliyasan
2
Kanpur
UP
6
Ambaliyasan
3
Anandnagaar
Assam
6
Ambaliyasan
4
Ambaliyasan
Gujarat
6
Ambaliyasan
5
Ambaji
Gujarat
6
Ambaliyasan
6
Aligarh
UP
6
Ambaliyasan
7
Delhi
Delhi
6
Ambaliyasan
8
Alandha
Karnataka
6
Ambaliyasan
9
Achhnera
UP
6
Ambaliyasan
1
Lucknow
UP
7
Anandnagaar
2
Kanpur
UP
7
Anandnagaar
3
Anandnagaar
Assam
7
Anandnagaar
4
Ambaliyasan
Gujarat
7
Anandnagaar
5
Ambaji
Gujarat
7
Anandnagaar
6
Aligarh
UP
7
Anandnagaar
7
Delhi
Delhi
7
Anandnagaar
8
Alandha
Karnataka
7
Anandnagaar
9
Achhnera
UP
7
Anandnagaar

SELF JOIN:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY.

SELECT a.city, b.state
FROM state a, State b
WHERE a.city = b.state

city
state
Delhi
Delhi


No comments :