joins .. 
join 2 or more tables together 
displaying data from different tables
at the same time... 

employees 
departments 

in joins, u have to pay attention to the 
primary key and the foreign key of the 
tables

primary key : is the attribute that uniqly 
identifies the table 

foreign key : the foreign key for one table
is a primary key in another table 

Example: if i want to diaplay the employees names 
with the department names which they work for 

select first_name, last_name
from employees 

select department_name
from departments 

Rule: you can perform a join between 2 tables if and
there was a link between those tables 
the link : when the primary key of one table is equal
to the foreign key of the other table 

Example of joins. 
Equiy join.

Traditional ..  
select first_name, last_name, department_name
from employees, departments 
where employees.department_id = departments.department_id 

select emp.first_name, emp.last_name, dept.department_name
from employees emp, departments dept
where emp.department_id = dept.department_id

ANSI Syntax ..
-Using 
-On   //to write the join condition 

Examples for USING clause .. 
select emp.first_name, emp.last_name, dept.department_name
from employees emp join departments dept
Using (department_id) //join condition 

Advantages of USING clause: 
it allows you to add an extra condition using WHERE caluse 

select emp.first_name, emp.last_name, dept.department_name
from employees emp join departments dept
Using (department_id)
where department_id = 50

Examples for ON clause ...
select emp.first_name, emp.last_name, dept.department_name
from employees emp join departments dept
ON emp.department_id = dept.department_id   //100 rows

select emp.first_name, emp.last_name, dept.department_name
from employees emp join departments dept
ON emp.department_id = dept.department_id //Join Condition
where dept.department_id = 50

Note: 
if you want to add an extra condidtion with WHERE clause while using the 
ON clause, you have to add the alias to the attribute used in the condition
but in the USING clause, you don't have to add the alias 

NOTE: If you omit the join condition, the result will be the Cartesian Product
of the two tables. A Cartesian Product joins all rows of the first table with 
all rows of the second table.That is if the first table has n rows and the 
second table has m rows, the output will have n*m rows.

select emp.first_name, emp.last_name, dept.department_name
from employees emp join departments dept

employees = 100 rows 
departments = 20 rows 
Cartesian Product : 100*20 = 2000 rows
in the result you'll have a lot of duplicates 
the initial result will be duplicated 20 times 


Non Equiy join (Outer join)
Traditional ... 

select emp.first_name, emp.last_name, dept.department_name
from employees emp, departments dept
where emp.department_id(+) = dept.department_id

Rule: the (+) can be written only on one side of the condition 

select emp.first_name, emp.last_name, dept.department_name
from employees emp, departments dept
where emp.department_id(+) = dept.department_id(+) //THIS IS WROOONNNGGG

ANSI Syntax ... 
There are 3 types of outer join
-left outer join
-right outer join 
-full outer join  right+left

select emp.first_name, emp.last_name, dept.department_name
from employees emp right outer join departments dept
Using (department_id)

select emp.first_name, emp.last_name, dept.department_name
from employees emp left outer join departments dept
Using (department_id)

select emp.first_name, emp.last_name, dept.department_name
from employees emp full outer join departments dept
Using (department_id)