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


Attachments

  • working with sql server joins (41180-15417-Joins.zip)
  • Comments

    Author: Mohite Mina20 Mar 2014 Member Level: Silver   Points : 10

    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



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: