What are join in sql? Explain Types of Join

Ans :

Join : When we need data from more than 1 table, then we can fetch data from those table using joining those table. It will return only matching data from two tables.

Select * from table1 t1 inner join table2 t2 on t1.column1 = t2.column1

Types of Join :

1. Inner Join : If we use inner join between two table, then only data exists in both table, are returned.

2. Outer Join :
    Left Outer Join : It returns all data from left table(table1) and only matching data from both table.
    Right Outer Join : It returns all data from right table(table2) and only matching data from both table.

3. Cross Join : It is like cartesian join. It returns all records from both table and it returns table1.count * table2.count records in returns. Suppose table1 has 4 records and table2 has 3 records and returns 4*3 records.

4. Self Join : If some column's reference has in same table then self join is used. It is same like inner join but here both left and right table are same. Its like if you table and Columns are such as MainID, Name, ParentID then you can make query like
Select * from table t1 join table t2 on t1.MainID = t2.ParentID