Joins

We discussed earlier how to select data from a single table. Now what if we want to select data from multiple related tables. For ex lets say we have below 2 tables:

Table 1: Employee                                  Table 2 :  Department

Columns                                                  Columns 

EmpID                                                     DepartmentID

EmpName                                                DepartmentName

DepartmentId                                           Function

Now if we want to see the Employee's name the department name which he belongs to, this can be accomplished by using the joins with the select statement.

INNER or Simple joins

Syntax:

 

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> ,<table_name_2>
WHERE  <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

OR

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1>  JOIN <table_name_2>
ON  <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

Both the above queries will return the similar results


OUTER JOINS:



Now if we want all the records from one of the tables in join and only matching values from another table this can be accomplished by Outer joins.

Syntax:

 

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> ,<table_name_2> (+)
WHERE  <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>];

OR

SELECT <column_name_1>,
<column_name_2>,
<column_name_N>
FROM <table_name_1> [Left , Right] JOIN <table_name_2>
ON  <table_name_1>.<column_name> = <table_name_2>.<column_name>

[ORDER BY <order_by_column_name_N>]; 


Here: 
We have introduced 2 new commands:
(+) for tradional joins : represents all the values will be selected from the table 
[Left , Right] : Use Left if you want to select all the value from the left hand side table
and use RIGHT if you want to select all the value from the right hand side table.
Only one of them can be used in a single join