JOIN

JOIN: 

1.The Join keyword is used in an SQL Statement to query data from two or more tables,based on a Relationship Between  certain columns in these tables.

2.The Join  is Used to Combine Two Or more tables,based on a Relationship Between  certain columns in these tables.

Different Type SQL Join

1.Inner Join

2.Outer Join

3.Self Join

4.Cross Join

create database ForJoin
use ForJoin
create table StudentRecord (id int,Name nvarchar(50),City nvarchar(50))
create table StudentCollage(id int,CollegeName nvarchar(50),BranchName nvarchar(50))
insert into StudentRecord values(9,'A.BHART','PATNA'),(10,'PRASHANT','ORISSA'),(4,'shubham kumar','new delhi'),
(3,'mohit kumar','gudgaon'),(2,'asam mohmmad','moradabad'),(1,'rajan kumar','meerut')
insert into StudentCollage values(6,'AIT','BA'),(7,'DDNT','NIT'),(2,'SUNDERDEEP','B.ARCH'),(1,'ITS','B.BHARMA')
use ForJoin
select * from StudentRecord
select * from StudentCollage

1.Inner Join/JOIN

The INNER JOIN Keyward return rows when there is at least one Match  in Both table.

SYNTAX 

SELECT * FROM Table_name1 INNER JOIN Table_name2 ON Table_name1.Column_Name=Table_name2.Column_Name

SELECT StudentRecord.*,StudentCollage.*from  StudentRecord join StudentCollage

on StudentRecord.id=StudentCollage.id

select* from StudentRecord  inner join  StudentCollage  on StudentRecord.id=StudentCollage.id

2.OUTER JOIN

2.1 LEFT OUTER JOIN:Returns all rows from Left table and match from Right table.

SYNTAX SELECT * FROM Table_name1 LEFT OUETR JOIN Table_name2 ON Table_name1.Column_Name=Table_name2.Column_Name

Example:Select StudentRecord.*,StudentCollage.* from StudentRecord left outer join  StudentCollage  on StudentRecord.id=StudentCollage.id

2.2 RIGHT OUTER JOIN:Returns all rows from Right table and match from left table.

SYNTAX

SELECT * FROM Table_name1 RIGHT OUETR JOIN Table_name2 ON Table_name1.Column_Name=Table_name2.Column_Name

Example:

Select StudentRecord.*,StudentCollage.* from StudentRecord RIGHT outer join  StudentCollage  on StudentRecord.id=StudentCollage.id

2.3 FULL OUTER JOIN:The Combination Of left Outer Join And Right Outer Join table

SYNTAX

SELECT * FROM Table_name1 FULL OUETR JOIN Table_name2 ON Table_name1.Column_Name=Table_name2.Column_Name

Example:

Select StudentRecord.*,StudentCollage.* from StudentRecord Full

outer join  StudentCollage  on StudentRecord.id=StudentCollage.id

3.SELF JOIN:The SQL SELF JOIN is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

----Create table Name----

  create table employee(empid int primary key ,empname varchar(50),managerid int)

  -----Insert Values in table-----

insert into employee values(1,'sunil',2),(2,'karan',2),(3,'Piyush',1),(4,'shivam',3),(5,'ajay',4)

  select * from employee

-----create self join in table----

SELECT DISTINCT e1.EmpId, e1.EmpName FROM Employee e1, Employee e2 where e1.EmpId=e2.ManagerId;

4.Cross Join:A cross join that produces Cartesian product of the tables that involved in the join. The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table.

select StuRecord.*,StuCollege.* from StuRecord ,StuCollege---OLD FORMAT-----

Select *  from  StuRecord cross join StuCollege

Select * from StuCollege cross join StuRecord

select t1.*,StuCollege.* from StuRecord t1   join  StuCollege  on t1.id=StuCollege.id.

.