Last night I was sitting with my friends when my friends asked "What are Joins in SQL?". I then decided to write an introduction with examples in an article. In this article we are discussing what are Joins? How are they created? What are the differences of the types of Joins: Inner Join, Outer Join, Left Join, Right Join, Cross Join....
1. INNER JOIN
This Join returns a row when there is at least one match in both tables; let's see an example:
Select * From Table1
Inner Join Table2
on table1.ID=table2.ID
There are three different types of outer joins; let's see 1 by 1.
- Left Outer Join
- Right Outer Join
- Full Outer Join
Left Outer Join
A LEFT OUTER JOIN is one of the JOIN operations that allows you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table.
Select * From Table1
Left Outer Join
on table1.ID=table2.ID
Right Outer Join
A RIGHT OUTER JOIN is one of the JOIN operations that allows you to specify a JOIN clause. It preserves the unmatched rows from the Table2 (right) table, joining them with a NULL in the shape of the Table1 (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order.
Select * From Table1
Right Outer Join
on table1.ID=table2.ID
Full Outer Join
FULL OUTER JOIN - This JOIN is a combination of both. All records from both Left_Table and Right_Table are in the result set and matched when they can be on the Join_Condition; when no record is found in the opposite table, NULL values are used for the columns.
Select * From Table1
Full Outer Join
on table1.ID=table2.ID
Cross Join
This JOIN has a slightly different format in that it does not have a ON clause with a Join_Condition. This is because of the nature of the CROSS JOIN it doesn't need a join condition. What it does is perform a cartesian product of the tables involved in the join. This mean every row in the Left_Table is joined to every row in the Right_Table. For our tables with 3 Table1 and 3 Table2 we would get a result set of 3x3 or 9 records. The query would look like
select t1.FullName as Boss,t2.FullName as Employee
from Table1 t1
cross join Table2 t2
Extra Notes related to JOIN:
The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.
SELECT t1.*
FROM table1 t1
WHERE t1.id not in (SELECT t2.id FROM table2 t2)
The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives the same result as above is displayed here using Outer Join and WHERE clause in join.
/*LEFTJOIN-WHERE NULL*/
SELECT t1.*,t2.*FROM Table1 t1
LEFT JOIN Table2 t2
ON t1.ID = t2.IDWHERE t2.ID IS NULL
Left Outer Join Where NULL
Select * From Table2 t2
Left Outer Join Table1 t1
on t2.Id=t1.Id
where t1.Id is Null
The above example can also be created using Right Outer Join.
Right Outer Join Where NULL
Select * From Table1 t1
right Outer Join Table2 t2
on t1.Id = t2.Id
where t1.Id is Null
NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when a full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.
Outer Join Where NULL
select * from Table1 t1
full outer join table2 t2
on t1.id = t2.id
where t1.id is null
or t2.id is null
I hope this article fulfills its purpose. I would like to have feedback from my readers. Please suggest where do you all want me to take this article next. And you can send related examples on Comment Box.
Thanks