Working with joins in sql server
In this artcile, I explain about joins, types of joins, working with types of joins.
A Join is used to combine columns from two or more tables into a single result set.
In this article I will tell you about three important joins
1. Cross Join
2. Inner Join
3. Outer Join
I have two tables employee(Eid pimaery key, ename, designation, age) and depart(Did, Eid foreign key, deptname) table.
1.Cross Joins:
Cross Join creates a list of all records from both the tables. The first record from the parent table is associated to each record from the child table and second record from the parent table is associated to each record from the child table. There is no need of common columns between the two tables. We will not use the ON clause.
Example:
SELECT employee.Eid, employee.ename, employee.designation, employee.age,
depart.Did, depart.Eid, depart.deptname
FROM employee
CROSS JOIN depart
GO
output
Eid ename designation age Did Eid deptname
0 1 24707 computers
1 Satish Programmer 24 1 24707 computers
2 Zainab Tester 23 1 24707 computers
1.Inner joins:
An Inner join combines the records from two tables (table 1 and table 2) based on a given Join-predicate. While using Inner Joins, there should be atleast one common column. In Inner joins we will be using ON clause.
Explicit Inner Join:
Example:
SELECT E.Eid, E.ename, E.designation, D.Did, D.deptname
FROM employee AS E
Inner Join depart AS D
ON E.Eid=D.Eid
GO
Output:
Eid ename designation Did deptname
24707 tarun developer 1 computers
4 lohit developer 3 Medical
Implicit Inner Joins:
Example:
SELECT * FROM employee, depart
WHERE employee.Eid=depart.Eid
GO
Output:
Eid ename designation Did deptname
24707 tarun developer 1 computers
4 lohit developer 3 Medical
Outer Joins:
Instead of showing only records that have entries in the child table, we want our query to include all records, including those that are null. To get this, we would be using Outer Join.
Left Outer Join:
It produces all records of the child table, also called the right table. The records of the child table that do not have an entry in the foreign key column as marked as null.
Example:
SELECT employee.Eid, employee.ename, depart.Did, depart.deptname
FROM employee LEFT OUTER JOIN depart
ON employee.Eid=depart.Eid
GO
Output:
Eid ename Did deptname
0 NULL NULL
1 Satish NULL NULL
2 zainab NULL NULL
4 lohit 3 Medical
--> The results of the left outer join for tables employee and depart always contains all records from table employee, even if the join condition does not find any matching record in the right table(depart).
--> That is if the ON clause matches 0 records in the table depart, the join will still return a row in the result but with null in each column from depart.
--> That means that left join will return all the values from the left table and matched values from the right table(null values if no matching value is found).
Right Outer Join:
Right outer join is somewhat close to left outer join, except the tables reserved. Every record from depart table will appear in the joined table at least once. If no matching record from the employee table exists, Null will appear in columns from employee for those records that have no match in employee.
Example:
SELECT * FROM employee
RIGHT JOIN depart
ON depart.Eid=employee.Eid
GO
Output:
Eid ename designation age Eid deptname
24707 tarun developer 23 24707 computers
4 lohit developer 24 4 Medical
Types of Joins
1) Inner Joins
2) Outer Joins
3) Self Join
Inner Join
The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.
SELECT *
FROM tblProgram1 TPA
INNER JOIN tblProgram2 TPB
ON TPA.id = TPB.id
We can write our inner join query like this also it will give same result
SELECT *
FROM tblProgram1 TPA
JOIN tblProgram2 TPB
ON TPA.id = TPB.id
In inner Join we are having different types of Joins those are
1) Equi Join
2) Natural Join
3) Cross Join
Equi Join
An equi-join is used to match two columns from two tables using explicit operator =:
select *
from tblProgram1 TPA, tblProgram2 TPB
where TPA.id = TPB.id
Natural Joins
The Natural join is same as our Equi join but only the difference is it will restrict
to display redundant values.
SELECT *
FROM tblProgram1
NATURAL JOIN tblProgram2
These NATURAL Joins won't work in our SQL Server (only supports in Oracle) it will throw
syntax error.
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 *
FROM tblProgram1
CROSS JOIN tblProgram2
Or we can write it in another way also
select *
from tblProgram1 , tblProgram2
Outer Joins
A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.
We are having three types of Outer Joins
Left Outer Join
Right Outer Join
Full Outer Join
Left Outer Join
The left outer join displays all the rows from the first table and matched rows from the second table.
SELECT u.id,u.Program,o.chapter
FROM tblProgram1 u
LEFT OUTER JOIN tblProgram2 o
ON u.id=o.id
Right Outer Join
The right outer join displays all the rows from the second table and matched rows from the first table.
SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
RIGHT OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id
Full Outer Join
Full Outer Join displays all the matching and non matching rows of both the tables.
SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
FULL OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id
Self Join
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.
select e2.EmpName,e1.EmpName as 'Manager'
from EmployeeDetails e1
INNER JOIN EmployeeDetails e2
on e1.EmpID=e2.EmpMgrID