Different Types of SQL Joins

Sql joins are used to fetch/retrieve data from two or more data tables, based on a join condition. A join condition is a relationship among some columns in the data tables that take part in Sql join. Basically data tables are related to each other with keys. We use these keys relationship in sql joins.

 

Inner Join

Inner join returns only those records/rows that match/exists in both the tables. Syntax for Inner Join is as

     Select * from table_1 as t1
    inner join table_2 as t2
    on t1.IDcol=t2.IDcol 

  Outer Join

We have three types of Outer Join.

    Left Outer Join
    Left outer join returns all records/rows from left table and from right table returns only matched records. If there are no columns matching in the right table, it returns NULL values. Syntax for Left outer Join is as :

         Select * from table_1 as t1
        left outer join table_2 as t2
        on t1.IDcol=t2.IDcol

    Right Outer Join

    Right outer join returns all records/rows from right table and from left table returns only matched records. If there are no columns matching in the left table, it returns NULL values. Syntax for right outer Join is as :

         Select * from table_1 as t1
        right outer join table_2 as t2
        on t1.IDcol=t2.IDcol

    Full Outer Join

    Full outer join combines left outer join and right outer join. This join returns all records/rows from both the tables.If there are no columns matching in the both tables, it returns NULL values. Syntax for full outer Join is as :

         Select * from table_1 as t1
        full outer join table_2 as t2
        on t1.IDcol=t2.IDcol

Cross Join

Cross join is a cartesian join means cartesian product of both the tables. This join does not need any condition to join two tables. This join returns records/rows that are multiplication of record number from both the tables means each row on left table will related to each row of right table. Syntax for right outer Join is as :

     Select * from table_1
    cross join table_2 

Self Join

Self join is used to join a database table to itself, particularly when the table has a Foreign key that references its own Primary Key. Basically we have only three types of joins : Inner join, Outer join and Cross join. We use any of these three JOINS to join a table to itself. Hence Self join is not a type of Sql join.