1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Understanding All Types of Joins in SQL Server

Discussion in 'SQL Server' started by MinalS, Oct 16, 2014.

  1. To retrieve data from multiple tables, SQL server allows user to apply joins to the tables. Depending on the condition, user can join multiple tables or views. There are different types of join available in SQL Server as inner join, outer join, cross join, equi join, and self join.

    Inner Join



    An inner join is used to retrieve multiple records by comparing the values from the common column in the table. After the inner join is applied, the common columns that satisfy the join condition are displayed. The columns that do not satisfy the condition are not displayed.

    The syntax for applying the inner join is as shown below:

    Code:
    SELECT column_name, column_name [ , column_name ]
    FROM table1_name JOIN table2_name
    ON table1_name.ref_column_name join_operator
    table2_name.ref_column_name 
    [ WHERE search_condition ]
    
    Where,

    table1_name and table2_name are the names of the table that are joined

    join_operator is the comparison operator based on the join to be applied

    table1_name.ref_column_name and table2_name.ref_column_name is the names of the columns on which the join is applied

    WHERE specifies the search condition for the rows returned by the query

    search_condition is the condition to be satisfied to return the selected rows

    Consider an example; supplier table is created as shown below:

    Code:
    create table supplier(
    sid int not null,
    item varchar(20) not null,
    location varchar(20) not null)
    
    Insert the following values in the supplier table.

    Code:
    insert into supplier values ( 101, ‘Pen’ , ‘Mumbai’ );
    insert into supplier values ( 102, ‘Books’ , ‘Calcutta’ );
    insert into supplier values ( 103, ‘Laptop’ , ‘Bangalore’ );
    insert into supplier values ( 104, ‘Table’ , ‘Pune’ );
    
    The supplier table contains the following values.

    [​IMG]

    Consider another table as orderdata as shown below:

    Code:
    create table orderdata(
    orderid int not null,
    price int not null,
    quantity int not null)
    
    insert into orderdata values( 101, 1000, 200);
    insert into orderdata values ( 102, 20000, 100);
    insert into orderdata values ( 104, 100000, 20);
    
    The orderdata table contains the following values.

    [​IMG]

    The query to execute the inner join is as shown below:

    Code:
    select s.sid, s.location, o.orderid, o.quantity from supplier s join orderdata o on s.sid=o.orderid
    
    The output generated after executing the query is as shown below:

    [​IMG]

    Outer Join



    The outer join is used to display the result set containing all rows from one table and the matching rows from another table. An outer join displays NULL for the columns of the related table where it does not find any matching records. The syntax for creating an outer join is as shown below:

    Code:
    SELECT column_name, column_name [ , column_name ] 
    FROM table1_name [ LEFT | RIGHT | FULL ] OUTER JOIN table2_name
    ON table1_name.ref_column_name join_operator
    table2_name.ref_column_name [ WHERE search_condition ]
    
    An outer join is of the following types as mentioned below:
    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join

    1. Left Outer Join

    A left outer join returns all rows from the table specified on the left side of the LEFT OUTER JOIN keyword and the matching rows are specified on the right side. The NULL value is displayed for the columns of the table specified on the right side where there are no matching records.

    Consider the examdata table and the values inserted in it.

    Code:
    create table examdata(
    subid int not null,
    subname varchar(30) not null,
    examdate datetime not null,
    location varchar(30) not null)
    
    insert into examdata values ( 201, ‘Science’, ‘2010-04-05’, ‘Mumbai’ );
    insert into examdata values ( 202, ‘English’, ‘2011-04-05’, ‘Lucknow’ );
    insert into examdata values ( 203, ‘Maths’, ‘2010-04-05’, ‘Delhi’ );
    insert into examdata values ( 204, ‘Social Science’, ‘2012-04-05’, ‘Pune’ );
    
    The table is displayed as follows:

    [​IMG]

    Create a table named as resultdata containing the following values.

    Code:
    create table resultdata(
    subid int not null,
    subname varchar(20) not null,
    marks int not null )
    
    insert into resultdata values ( 201, ‘Science’, 80);
    insert into resultdata values ( 202, ‘Maths’, 90);
    insert into resultdata values (204, ‘Social Science’, 70);
    
    The table is displayed as shown below:

    [​IMG]

    The query to demonstrate the left outer join is as shown below:

    Code:
    select e.subid, e.subname, r.marks from examdata e left outer join resultdata r on e.subid=r.subid
    
    The output for the join is as shown below.

    [​IMG]

    2. Right Outer Join

    A right outer join returns all the rows from the table specified on the right side of the RIGHT OUTER JOIN keyword and only the matching rows from the left side of the table.

    Execute the right outer join query on the above tables.

    Code:
    select e.subid, e.subname, r.marks from examdata e right outer join resultdata r on e.subid=r.subid
    
    The output for the query is as shown below:

    [​IMG]

    3. Full Outer Join

    A full outer join is the result of left outer join and right outer join. The join returns all the matching and non matching rows from the table. There are no repeat matching records. The NULL value is displayed when the records do not match.

    Execute the full outer join query as mentioned below:

    Code:
    select e.subid, e.subname, r.marks from examdata e full outer join resultdata r on e.subid=r.subid
    
    The output for the query is as shown below:

    [​IMG]

    Cross Join



    A cross join is used as Cartesian product of tables. It joins each row of one table with every row of another table. The total number of rows is equal to the number of rows of first table multiplied with the number of rows of another table.

    Consider an example, table 1 contains 5 rows and table 2 contains 6 rows. The result table contains all the rows of table 1 are joined with all the rows of table 2. Hence, the result contains 30 rows.

    The cross join query is executed on the examdata and result data tables.

    Code:
    select e.subid, e.subname., r.marks from examdata cross join resultdata r
    
    The result of the cross join query is as shown below:

    [​IMG]

    Equi Join



    An equi join is equal to inner join. The tables are joined with the help of foreign key. The equality operator is used for the join condition. The conditional operators are used to specify the join condition in an inner join.

    Code:
    select * from supplier s join orderdata o on s.sid=o.orderid join empholiday h on h.empid=o.orderid
    
    The output generated after executing the query is as shown below:

    [​IMG]

    Self Join



    A self join is used to join the table with itself. It states that one row in a table correlates with other rows in the same table. As a result the same table name is used twice in the query. The table is given two alias names to differentiae the two tables.
    The query to demonstrate the self join is as shown below:

    Code:
    select e.subid, e.subname, e.location, r.marks as examdetails from examdata e, resultdata r where e.subid=r.subid
    
    The output displayed after executing the query is as shown below:

    [​IMG]
     
    shabbir likes this.

Share This Page