MS SQL JOINS

What are SQL Joins?

SQL Join is used to fetch data from two or more tables, which is joined as a single set of data. It is used for combining column from two or more tables by using values common to both tables.

Types of Joins

There are following types of Joins:-


  • Inner
  • Outer
  • Left
  • Right

  • CROSS JOIN:-

    This type of JOIN returns the Cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table.
    Cross JOIN syntax:-
    select column_names
    from 
    first_table CROSS JOIN second_table

    For Example: We have two tables Employees and city as shown below:-

    SQL JOINS

    Cross join of these table is as:-

    SQL JOINS

    INNER JOIN:-
    This is a simple JOIN which returns the matched data between two tables as per the equality condition in the sql query.
    Inner Join Syntax is,
                             SELECT  * FROM table1 INNER JOIN
                             table2 ON Equality_Condition
    For Example:-
                            Inner join of tables Employees and gender is as shown below:-
    SQL JOINS


    Query that shows data of two tables and its inner join is as shown below:-


    OUTER JOIN:-

    Outer Join is based on both matched and unmatched data.
    Outer join further divides into three parts:

    1. Left Outer Join
    2. Right Outer Join
    3. Full Outer Join

    LEFT Outer Join

    The left outer join returns a resultset table with the matched data from the two tables and then the remaining rows of the left table and null from the right table's columns.
    Syntax for Left Outer Join is,
    select * from table1 left outer join table2 on (equality condition).

    For example: we have two table emp and city as shown below.


    SQL JOINS

    Inner join of these two table is as below:-
    SQL JOINS
    RIGHT Outer Join

    The right outer join returns a resultset table with the matched data from the two tables being joined, then the remaining rows of the right table and null for the remaining left table's columns.

    Syntax for Right Outer Join is:-

    select * from table1 right join table2 on (equality condition).

    For example: We have two tables.

    SQL JOINS


    Resultant of these two tables as right outer join is as:-

    SQL JOINS


    Full Outer Join

    The full outer join returns a resultset table with the matched data of two table then remaining rows of both left table and then the right table.
    Syntax of Full Outer Join is,
    select * from table1 full outer join table2 on (equality condition).
    For example:- We have two tables as shown below:-
    SQL JOINS


    Resultant of these two tables as Full Outer Join is as below:-
    SQL JOINS










                        

    Post a Comment

    0 Comments