SQL Join based on Ven Diagram

Hi Guys, Hope You  all  are doing well !!!
Today i am going to put some light on sql join .
 At first  I am starting  with what is sql join ?
The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a
means for combining fields from two tables by using values common to each.
Now why should we use join?
 if you have some experience in database programming you know we can run queries one by one, use output of each in successive queries. Of course, that is possible. But using JOINs, you can get the work done by using only a one query with any search parameters.
   On the other hand MySQL can achieve better performance with JOINs as it can use Indexing. Simply use of single JOIN query instead running multiple queries do reduce server overhead. Using multiple queries instead that leads more data transfers between MySQL and applications (software). Further it requires more data manipulations in application end also.
It is clear that we can achieve better MySQL and application performances by use of JOINs.
I am going to discuss seven different ways you can return data from two relational tables. I will be excluding cross Joins and self referencing Joins.
The seven Joins I will discuss are shown below:

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. OUTER JOIN
5. LEFT JOIN EXCLUDING INNER JOIN
6. RIGHT JOIN EXCLUDING INNER JOIN
7. OUTER JOIN EXCLUDING INNER JOIN

Before I start sql join  query suppose we have  following table created in database . 

table_a and table_b . Data in table_a

pk       value                   and   table_b

1          A1                              pk        value

2          A2                               1            B1

3          A3                               2            B2

6         A6                                3            B3

7         A7                                6            B6

4         A4                                 7           B7

5         A5                                 8          B8

10       A10                               9           B9

1. Inner Join
This is the simplest, most understood Join and is the most common. This query will return all of the records in the left table (table A) that have a matching record in the right table (table B). This Join is written as follows:

INNER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A INNER JOIN Table_B B ON A.PK = B.PK

Results Output

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1  A1        B1          1
   2  A2        B2          2
   3  A3        B3          3
   6  A6        B4          6 
   7  A7        B7          7

2. Left Join  or  Left outer join 
This query will return all of the records in the left table (table A) regardless if any of those records have a match in the right table (table B). It will also return any matching records from the right table. This Join is written as follows:

LEFT JOIN

SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 A1         B1           1
   2 A2         B2           2
   3 A3         B3           3
   4 A4       NULL         NULL
   5 A5       NULL         NULL
   6 A6       B6            6
   7 A7       B7            7
  10 A10     NULL         NULL

(8 row(s) affected)

3. Right  Join or Right outer  Join
This query will return all of the records in the right table (table B) regardless if any of those records have a match in the left table (table A). It will also return any matching records from the left table. This Join is written as follows:

RIGHT JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
RIGHT JOIN Table_B B ON A.PK = B.PK

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1  A1        B1           1
   2  A2        B2           2
   3  A3        B3           3
   6  A6        B6           6
   7  A7        B7           7
NULL  NULL      B8           8
NULL  NULL      B9           9
NULL  NULL      B11          11

(8 row(s) affected)

4. Full Join or full outer join 
This Join can also be referred to as a FULL OUTER JOIN or a FULL JOIN. This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). This Join is written as follows:

OUTER JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
FULL OUTER JOIN Table_B B ON A.PK = B.PK

Result Will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   1 A1        B1           1
   2 A2        B2           2
   3 A3        B3           3
   6 A6        B4           6
   7 A7        B5           7
NULL NULL      B8           8
NULL NULL      B9           9
NULL NULL      B11         11
   5 A5       NULL       NULL
   4          NULL       NULL
  10 A10      NULL       NULL

(11 row(s) affected)

5. Left Excluding JOIN
This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). This Join is written as follows:

LEFT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
   4  A4        NULL       NULL
   5  A5        NULL       NULL
  10  A10       NULL       NULL
(3 row(s) affected)

6. Right Excluding JOIN
This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). This Join is written as follows:

RIGHT EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL  NULL       B8         8
NULL  NULL       B9         9
NULL  NULL       B11       11

(3 row(s) affected)

7.Outer Excluding JOIN
This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. I have yet to have a need for using this type of Join, but all of the others, I use quite frequently. This Join is written as follows:

OUTER EXCLUDING JOIN
SELECT A.PK AS A_PK, A.Value AS A_Value,B.Value AS B_Value, B.PK AS B_PK FROM Table_A A
FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL

Result will be

A_PK A_Value    B_Value    B_PK
---- ---------- ---------- ----
NULL  NULL       B8         8
NULL  NULL       B9         9
NULL  NULL       B11        11
   5  A5         NULL       NULL
   4  A4         NULL       NULL
  10  A10        NULL       NULL

(6 row(s) affected)

Summary:

• JOINS allow us to combine data from more than one table into a single result set.
• JOINS have better performance compared to sub queries
• INNER JOINS only return rows that meet the given criteria.
• OUTER JOINS can also return rows where no matches have been found. The unmatched rows are returned with the NULL keyword.
• The major JOIN types include Inner, Left Outer, Right Outer, Cross JOINS etc.
• The frequently used clause in JOIN operations is “ON”. “USING” clause requires that matching columns be of the same name.
• JOINS can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc.

               Happy Coding !!!