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