SqlTutorial-1 : Lesson-6 : Class-1
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Sql-JOIN operations: INNER, OUTER, CROSS

Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

JOINs are used to access the data that combined together from multiple tables simultaneously.
INDEX
  1. Introduction 
  2. Types of JOINs ( INNER, OUTER, CROSS )
  3. INNER JOIN ( Default JOIN Type )
  4. OUTER JOIN (FULL | LEFT | RIGHT and default=None; Optional=OUTER Clause)
  5. CROSS JOIN
  6. DELETE using JOIN Statement
  7. UPDATE using JOIN Statement
  8. Custom JOINs 
  9. JOINs with Sub-Queries


1. Introduction

  • Previously, JOIN-operations was performed in WHERE-clause, but new syntax introduce JOIN-statement as extention to FROM-clause, so that you can implement JOIN within FROM-Clause instead of WHERE-Clause.
    Note that still you can use previous syntax for JOIN
    that does not includes JOIN-statement.
  • Specifiying JOIN condition in FROM clause (as new syntax support) is recommended because:
    1. ANSI Standard: According to ANSI specification, joining condition must be in FROM clause.
    2. Readability: It increases readability; you can easily determine the kind by finding relevant keywords as INNER JOIN, OUTER JOIN, and CROSS JOIN. 
    3. Filter Isolations: It isolates the joining conditions from data filtering condition. So, you can specify joining condition in FROM clause and data filtering condition in WHERE clause.
  • For joining, the column types to be joined need not be same but values expect same.
      E.g., 10 = "10"  => true;
              10=10.00 => true
  •  JOINs not only work with SELECT-statement they also work with UPDATE and DELETE statements.

2. Types of JOINs


SQL Server joins fall into three types:
  1. INNER JOIN: It merges (or  combines) matched rows from two tables. The matching is done based on a common columns of tables and their values satisfying joining condition.
  2. OUTER JOIN: It returns both matched and unmatched rows between two tables. Matched rows are combined with each other and unmatched rows are combined with NULL-rows. i.e., rows satisfying joining conditions are combined with each other and those does not satisfying are combined with NULL-Rows. However, you can filter unmatched rows in result set by using sub-types: LEFT | RIGHT OUTER JOINs. The following are types of OUTER JOINS
    • LEFT Outer Join (a.k.a LEFT-JOIN): Returns matched rows from two tables and unmached from LEFT table (or first table) only.
    • RIGHT Outer Join (a.k.a RIGHT-JOIN):Returns matched rows from two tables and unmached from RIGHT table(or second table) only.
    • FULL Outer Join (a.k.a FULL-JOIN): Returns matched rows from two tables as well as unmatched rows also from two tables.
  3. CROSS JOIN: This join does not merges/combines instead it perform cartesian product. It is similar to simple SELECT without joining condition or without WHERE-cluase.
Microsoft SQL Server Training Online Learning Classes Sql JOIN Types INNER-OUTER-CROSS

The other categories of joins are Equi-join, Natural Join, Self Join etc these can be achieved using INNER JOIN , OUTER JOIN and CROSS Joins. Using CROSS JOIN, you can achieve Cartesian product.

  • INNER JOIN merge records M + N 
  • OUTER JOIN merge records M+N for match; (M-R) X (N-R) for unmatched. 
  • CROSS JOIN perform Cartesian product as M X N.


3. INNER JOIN

  • INNER JOIN works on conditional matching and returns matched rows from multiple tables.
  • Using INNER JOIN you can achieve EQUI-JOIN, NATURAL JOIN, and SELF-JOIN operations.
    • Equi-join (based equality condition and result containing duplicate columns)
    • Natural-Join (based on equality condition and result doesn’t contain duplicate columns).
    • Self-Join (Joining a table to it self).
  • It is Default JOIN operation. i.e, you can use just JOIN keyword instead of INNER JOIN in the query.
  • Using CROSS JOIN with equality condition in WHERE clause, you can achieve INNER JOIN.

Syntax:

FROM Table_1 [AS] t1
  [INNER] JOIN Table_2 [AS] t2 ON Joining_Condition1
  [[INNER] JOIN Table_3 [AS] t3 ON Joining_Condition2]...

Where Joining_condition1,2 ..: Simple(means without logical operators) or complex
                                                   conditions(using complex operators such as AND, OR,NOT),
                                                   i.e, you can assign filtering condition in FROM clause also.

Examples:

  1. Default JOIN is INNER-JOIN

    SELECT *
    FROM Products p JOIN Suppliers S ON P.SupplierID = S.SupplierID
  2. Simple INNER JOIN (Joining two tables)

    SELECT *
    FROM Products p
       INNER JOIN Suppliers S ON P.SupplierID = S.SupplierID
  3. Complex INNER JOIN (Joining more than two tables with filtering)

    SELECT *
    FROM Products p
      INNER JOIN Suppliers s ON p.SupplierID = s.SupplierID
      INNER JOIN Vendors V ON V.SupplierID = s.SupplierID
    WHERE p.ProductID < 4
  4. Achieving Equi-join using INNER JOIN. (select identical columns from both tables)

    SELECT t1.RNo, t1.sname,t1.Address, t2.Rno AS ex1, t2.Total
    FROM StudentInfo t1
      INNER JOIN StudentMarks t2 ON t1.Rno=t2.Rno
    =
    SELECT * FROM StudentInfo t1, StudentMarks t2
    WHERE t1.rno = t2.rno
  5. Achieving NATURAL JOIN using (Select only one identical column from any table)

    SELECT t1.Rno, t1.sname, t1.Address, t2.Total
    FROM StudentInfo t1
      INNER JOIN SudentMarks t2 on t1.Rno = t2.Rno
  6. Achieving SELF JOIN (FIND DUPLICATE RECORDS)

    SELECT * FROM StudentInfo AS s1
       INNER JOIN StudentInfo AS s2 
            ON s1.sname = s2.sname AND s1.Sid <> s2.Sid
  7. Achieving INNER JOIN using CROSS JOIN.

    -- using CROSS -- Equivalent to
    SELECT *
    FROM Table1 t1
    CROSS JOIN Table2 t2
    WHERE t1.ID =t2.ID
    SELECT *
    FROM Table1 t1
       INNER JOIN Table2 t2
    ON t1.ID = t2.ID


4. OUTER JOIN

  • Unlike INNER JOIN, OUTER JOIN retrieves both matched and unmatched records.
  • OUTER JOIN can be LEFT, RIGHT or FULL outer Join.
  • LEFT OUTER JOIN (simply LEFT JOIN), returns matched records from both tables and unmatched record from first(left) table. Similarly, RIGHT OUTER JOIN(simply LEFT JOIN) returns matched and unmatched records from second(right table). FULL JOIN (Full Outer Join) returns matched and unmatched records from both the table.
If no matching rows found, the associated result row contains null values for all select columns.

Syntax:

SELECT "SELECT list"
FROM LeftTablel
   "LEFT|RIGHT|FULL" [OUTER] JOIN  RightTable ON "join condition"

Examples:

Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

  1. LEFT OUTER JOIN (Result Set = Matching from both tables + un-matching from left table)

    SELECT *
    FROM T1
      LEFT OUTER JOIN T2 ON T1.col1 = T2.col1
  2. RIGHT OUTER JOIN(Result Set = Matching from both tables + un-matching from right table)

    SELECT *
    FROM T1
        RIGHT OUTER JOIN T2 ON T1.col1 = T2.col1
  3. FULL OUTER JOIN (Result Set = Matching and un-matching from both tables)

    SELECT *
    FROM T1
       FULL OUTER JOIN T2 ON T1.col1 = T2.col1
  4. Achieving Equi-join (By filtering using WHERE clause, you can achieve equi-join)

    SELECT *
    FROM T1
        FULL OUTER JOIN T2 ON T1.col1 = T2.col1 
    WHERE (T1.col1 IS NOT NULL) AND (T2.col1 IS NOT NULL)

    -- Equals to  --

    SELECT *
    FROM T1
       INNER JOIN T2 ON T1.col1 = T2.col1
  5. Achieving Self-join

    SELECT t1.col1, t1.col2, t2.col1 AS Expr1, t2.col2 AS Expr2
    FROM T1 AS t1
       FULL OUTER JOIN T1 AS t2 ON t1.col1 = t2.col1


5. CROSS JOIN

  • CROSS JOIN returns all possible combinations of rows from both joined table.
  • Using CROSS JOIN without WHERE clause, you can achieve Cartesian product.
  • If Table A has 7 rows, and table B has 3 rows, then result set would have 7x3=21 rows

Examples:

  1. Achieving CARTESIAN PRODUCT

    --Simple CROSS JOIN -- Equivalent example
    SELECT * FROM Table1
    CROSS JOIN Table2
    SELECT *
    FROM Table1,Table2
  2. Achieving Self-join using CROSS JOIN.

    SELECT * FROM Table1 CROSS JOIN Table1
  3.  Achieving INNER join using CROSS JOIN.

    -- using CROSS -- Equivalent to
    SELECT *
    FROM Table1 t1
    CROSS JOIN Table2 t2
    WHERE t1.ID =t2.ID
    SELECT *
    FROM Table1 t1 INNER JOIN Table2 t2
    ON t1.ID = t2.ID

    • Using OUTER JOIN you cannot achieve CROSS JOIN or vice versa. Similarly, using INNER JOIN you cannot achieve OUTER JOIN, CROSS JOIN.
    • Cross Join returns zero-rows resultset when either of the table is empty.

6. DELETE using JOIN Statement

  1. DELETE+INNER Join - 1:
    Deleting all COMMON records from two tables

    DELETE Table1, Table2
    FROM Table1 t1
      INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
  2. DELETE+INNER Join - 2:
    Deleting COMMON records from left table only

    DELETE Table1
    FROM Table1 t1
      INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
  3. DELETE+INNER Join-3:
    Deleting COMMON records from RIGHT table only.

    DELETE Table2
    FROM Table1 t1
      INNER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
  4. DELETE+OUTER Join-1:
    Deleting UN-COMMON records from BOTH tables.

    DELETE Table1, Table2
        FROM Table1 t1
           FULL OUTER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
     WHERE t1.PrimaryKey IS NULL AND t2.ForiegnKey IS NULL;
  5. DELETE+OUTER Join-2:
    Deleting UN-COMMON records from LEFT table only.

    DELETE Table1
       FROM Table1 t1
           LEFT JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
     WHERE t1.PrimaryKey IS NULL;
  6. DELETE+INNER Join-3:
    Deleting COMMON records from RIGHT table only.

    DELETE Table2
       FROM Table1 t1 RIGHT JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
    WHERE t2.ForiengKey IS NOT NULL;

7. UPDATE using JOIN Statement

  1. UDPATE+JOIN -1:
    Updating all records of Left tables that matches(COMMON) with right table.

    UPDATE Table1
    SET Column1 = Value1, Column2=Value2....
    FROM Table1 t1
       JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;

    You can't update more that one table in a single statement.
  2. UPDATE+JOIN -2:
    Updating all records of right tables that matches(COMMON) with left table.

    UPDATE Table2
     Set Col1=Value1, Col2=Value2...
    FROM Table1 t1
        JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey;
  3. UPDATE+OUTER JOIN -1 :
    Updating all records of table1 those are UN-COMMON with table2.

    UPDATE Table1
       Set Col1=Value1, Col2=Value2...
    FROM Table1 t1
       FULL OUTER JOIN Table2 t2 ON t1.PrimaryKey = t2.ForeignKey
    WHERE t1.Primarykey IS NULL;

8. CUSTOM JOINS

Microsoft SQL Server Training Online Learning Classes Sql JOIN Operations INNER OUTER CROSS

  1. Negation of INNER JOIN:
    Find all records of two tables those are unmatched with each other.

    SELECT *
     FROM TableA a
        FULL OUTER JOIN TableB b ON a.PrimaryKey = b.ForiegnKey
    WHERE a.PrimaryKey IS NULL and b.ForeignKey IS NULL;
  2. Negation of LEFT OUTER JOIN:
    Find all records of a Table1 those does not match with other Table records:

    SELECT a.*
      FROM TableA a
        LEFT JOIN TableB b ON a.PriamryKey=b.ForeignKey
    WHERE a.PrimaryKey IS NULL;
  3. Negation of RIGHT OUTER JOIN:
    Find all records of RIGHT-table those does not matched with LEFT-table.


    SELECT b.*
      FROM TableA a
        RIGHT JOIN TableB b ON a.PriamryKey=b.ForeignKey
    WHERE b.ForeignKey IS NULL
  4. LEFT CROSS JOIN:
    Find all records joined with 1st record of the table;


    SELECT *
      FROM TableA as A CROSS JOIN (Select Top 1  *  From Table B) as B;

  5. Negation of RIGHT OUTER JOIN:
    Find all records of RIGHT-table those does not matched with LEFT-table.


    SELECT *
      FROM (Select Top 1 * from TableA ) a
        CROSS JOIN TableB b


9. JOINs with Sub-Queries

T-Sql allows you to nest the sub-queries within JOIN Clause.
  1. Sub-Query with INNER JOIN

    SELECT *
    FROM (Select * from Table1 ......) as t1
       INNER JOIN (Select * from Table2 .....) as t2  ON t1.Id = t2.Id;
  2. Sub-Query with OUTER JOIN

    Select *
    FROM Table1 as t1
     FULL OUTER JOIN (Select * from Table2 Where ......) as t2 ON t1.Id=t2.Id

7 comments:

  1. It was really a nice article and I was really impressed by reading this article. We are also giving all software Course Online Training. The Microsoft Courses Online Training is one of the leading Online Training institute in the world.

    ReplyDelete
  2. Hi
    It was really a nice article and I was really impressed by reading this article. You can also visit here…

    MS SQL Corporate Training

    ReplyDelete

  3. It is very good and useful for students and developer .Learned a lot of new things from your post!Good creation ,thanks for good info Dot Net Online Training Bangalore

    ReplyDelete
  4. Free Internet Learning-When it involves obtaining started on the internet one amongst the most effective ways that to induce started goes to be a free internet business chance. the rationale this can be the most effective approach is as a result of you virtually don't seem to be about to need to place any cash into it so as to induce started and check it out. The approach that this works is essentially the corporate offers you a free trial for you to envision out the corporate and everything that must supply. If you're glad with what it's to supply and you keep you are merely about to be paying an inexpensive monthly fee.

    ReplyDelete
  5. Thank you for your guide to with upgrade information.
    .Net Online Training Bangalore

    ReplyDelete