In this article describe the concept of sql join. Join is a method that we combines the rows of two or more than two tables. There are many types of join. Here we understand by the simple examples.

SQL join is a method that we combines the rows of two or more than two tables, based on a common field between them.

Here describe in the figure how join are possible. It denote the Left outer join in the

left side and right outer join in right side and also define inner join at the central

where intersect both.

There are following types of join:

·         Inner join

·         Equi join

·         Outer join

     1.       Left or Left Outer join

    2.       Right or  Right  Outer join

    3.       Full or Full Outer join

·         Cross join

·         Self join

Inner join

Inner join is also known as default join. Inner join is a join that display the rows in

that table match. Inner is most useful join in sql server.

Here we understand by the simple example. There is a Student table and Course

table that both join by inner join.

Here create table student and course:

Student table

Customer table

Syntax

SELECT column_name1, column_name2,. . . FROM table1 INNER JOIN table2 ONtable1.column_name= table2.column_name; 

Here a simple example of inner join:

 
SELECT Student.student_name, Course.course_name FROM Student INNER JOIN Course ONStudent.student_id=Course.course_id;

Here we join both table:

Equi join

Equi join is a special type of join in which we use only equality (=) operator. Hence,

when you make a query for join using equality operator, then that join query comes

under Equi join. It’s not use the operator (<,>, <>). In inner join equality

operator (=) use and also use the operator (<,>, <>).

SELECT Student.student_name, Course.course_name FROM Student JOIN Course ONStudent.student_id=Course.course_id;
 

Outer join

It does not require that each record in two joined tables to have matching records.  The Join table retains each record – even if no other matching record exists. There are left outer join, right outer join, and full outer join. 

Syntax

SELECT column_name1, column_name2,. . . FROM table1 OUTER JOIN table2 ONtable1.column_name= table2.column_name; 

Here we give the example of employee and department table record get from outer join.

create table Employee(
 
EmpID int identity(1,1) primary key,
FirstName varchar(50),
LastName varchar(50),
DepartID int references Departments(DepartID)
)
 
create table Departments(
 
DepartID int identity(1,1) primary key,
DepartmentName varchar(50)
)
  

     
Left or Left outer join

The left join keyword returns all rows from the left table (table1), with the matching

rows in the right table (table2). The result is NULL in the right side when there is no

match.

Left join displays all the rows from first table and matched rows from second table

like that.

The left join keyword returns all the rows from the left table (Employee), even if

there are no matches in the right table (Departments).

Example

SELECT * FROM Employee e1 LEFT OUTER JOIN Departments e2
ON e1.DepartID = e2.DepartID

Right or Right outer join

The right join keyword returns all rows from the right table (table2), with the

matching rows in the left table (table1). The result is NULL in the left side when

there is no match.

Right outer join displays all the rows of second table and matched rows from first

table like that.

The right join keyword returns all the rows from the right table (Employees), even if

there are no matches in the left table (Departments).

Example

SELECT * FROM Employee e1 RIGHT OUTER JOIN Departments e2
ON e1.DepartID = e2.DepartID 

Full or Full outer join

The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from

the right table (table2).

Full outer join returns all the rows from both tables whether it has been matched

or not.

The full outer join keyword returns all the rows from the left table (Employee), and

all the rows from the right table (Departments). If there are rows in "Employee" that

do not have matches in "Departments", or if there are rows in

"Departments“ that do not have matches in " Employee", those rows will be listed

as well.

Example

SELECT * FROM Employee e1 FULL OUTER JOIN Departments e2
ON e1.DepartID = e2.id 

Cross join

A cross join produces the Cartesian product of the tables those involved in the join.

The size of the Cartesian product is the number of the rows in the first table

multiplied by the number of the rows in the second table.

Syntax

SELECT * FROM table1
CROSS JOIN table2 

Here create two table company and Food and insert some records.

create table Company
(
       company_id int primary key,
       company_name varchar(50),
       company_city varchar(50)
)
 
create table Food
(
       food_id int primary key,
       food_name varchar(50),
       food_unit varchar(10),
       company_id int references Company(company_id)
) 

Example

Here describe in outer join tables:

SELECT *FROM Company CROSS JOIN Food 

In this way it show 40 records because 8 records in Employee table and 5 records

in Departments table. Therefore cross join result is 8x5 = 40 records.

Self join

In this joining, joining the table itself so it is called self join. Self join is used to

retrieve the records having some relation or similarity with other records in the

same table.

Syntax

SELECT column_name1, column_name2 from table t1 inner join table t2 on t1.column_name=t2.column_name 

Here the example of employe table self join with department id:

Example

SELECT e1.FirstName,e1.LastName from Employee e1 inner join Employee e2 one1.EmpID=e2.DepartID