Home > DeveloperSection > Articles > SQL Joins

SQL Joins


ASP.Net ASP.Net 
Ratings:
0 Comment(s)
 2089  View(s)
Rate this:

SQL Joins


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.

SQL Joins

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.

SQL Joins

Here create table student and course:

Student table

SQL Joins

Customer table

 

SQL Joins

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:

 

SQL Joins

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;

 

SQL Joins

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)

)

  

 

SQL Joins SQL Joins

 

     

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.

SQL Joins

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

 

SQL Joins

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.

SQL Joins

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 

SQL Joins

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.

SQL Joins

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 

SQL Joins

 

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)

) 

SQL Joins SQL Joins

 

Example

Here describe in outer join tables:

SELECT *FROM Company CROSS JOIN Food 

SQL Joins

SQL Joins

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

SQL Joins



Don't want to miss updates? Please click the below button!

Follow MindStick