SqlTutorial-1 : Lesson-6 : Class-1 Microsoft SQL Server Training: Free Online-Learning Classes (OLC) for T-SQL 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.

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:

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

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