What is SQL JOIN ?

SQL JOIN is a method to retrieve data from two or more database tables.

What are the different SQL JOINs ?

There are a total of five JOINs. They are :

  1. JOIN or INNER JOIN
  2. OUTER JOIN

     2.1 LEFT OUTER JOIN or LEFT JOIN
     2.2 RIGHT OUTER JOIN or RIGHT JOIN
     2.3 FULL OUTER JOIN or FULL JOIN

  3. NATURAL JOIN
  4. CROSS JOIN
  5. SELF JOIN

1. JOIN or INNER JOIN :

In this kind of a JOIN, we get all records that match the condition in both the tables, and records in both the tables that do not match are not reported.

In other words, INNER JOIN is based on the single fact that : ONLY the matching entries in BOTH the tables SHOULD be listed.

Note that a JOIN without any other JOIN keywords (like INNER, OUTER, LEFT, etc) is an INNER JOIN. In other words, INNER JOIN is a Syntactic sugar for JOIN (see : Difference between JOIN and INNER JOIN).

2. OUTER JOIN :

Outer Join retrieves

Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match).

There are three kinds of Outer Join :

2.1 LEFT OUTER JOIN or LEFT JOIN

This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

2.2 RIGHT OUTER JOIN or RIGHT JOIN

This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

2.3 FULL OUTER JOIN or FULL JOIN

This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

In other words, OUTER JOIN is based on the fact that : ONLY the matching entries in ONE OF the tables (RIGHT or LEFT) or BOTH of the tables(FULL) SHOULD be listed.

Note that OUTER JOIN is a loosened form of INNER JOIN.

3. NATURAL JOIN :

It is based on the two conditions :

the JOIN is made on all the columns with the same name for equality.
Removes duplicate columns from the result.

This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.

4. CROSS JOIN :

It is the Cartesian product of the two tables involved. The result of a CROSS JOIN will not make sense in most of the situations. Moreover, we wont need this at all (or needs the least, to be precise).

5. SELF JOIN :

It is not a different form of JOIN, rather it is a JOIN (INNER, OUTER, etc) of a table to itself.

Better Illustration over theory

Examples:

1.1: INNER-JOIN: Equi-join implemetation

SELECT  *
FROM Table1 A 
 INNER JOIN Table2 B ON A.<PrimaryKey> =B.<ForeignKey>;

1.2: INNER-JOIN: Natural-JOIN implementation

Select A.*, B.Col1, B.Col2          --But no B.ForiengKyeColumn in Select
 FROM Table1 A
 INNER JOIN Table2 B On A.Pk = B.Fk;

1.3: INNER-JOIN with NON-Eqijoin implementation

Select *
 FROM Table1 A INNER JOIN Table2 B On A.Pk <= B.Fk;

1.4: INNER-JOIN with SELF-JOIN

Select *
 FROM Table1 A1 INNER JOIN Table1 A2 On A1.Pk = A2.Fk;

2.1: OUTER JOIN (full outer join)

Select *
 FROM Table1 A FULL OUTER JOIN Table2 B On A.Pk = B.Fk;

2.2: LEFT JOIN

Select *
 FROM Table1 A LEFT OUTER JOIN Table2 B On A.Pk = B.Fk;

2.3: LEFT JOIN

Select *
 FROM Table1 A RIGHT OUTER JOIN Table2 B On A.Pk = B.Fk;

3.1: CROSS JOIN

Select *
 FROM TableA CROSS JOIN TableB;

3.2: CROSS JOIN-Self JOIN

Select *
 FROM Table1 A1 CROSS JOIN Table1 A2;

//OR//

Select *
 FROM Table1 A1,Table1 A2;

Feb 19, 2014

what is joins in sql server