---create table deptments
create table deptments
(
dept_id number,
dept_name varchar2(100),
location_id number,
constraint pk_id primary key(dept_id)
)

---create table employees
create table employees
(
emp_id number,
first_name varchar2(30),
last_name  varchar2(50),
dept_id  number,
constraint  pk_emp_id primary key(emp_id),
constraint  fk_dept referencing deptments(dept_id)
);

---natural join
--natural join clause
    The NATURAL JOIN clause is based on all the columns in
the two tables that have the same name.
It selects rows from the two tables that have equal values in all matched columns.
If the columns having the same names have different data types, an error is returned.

select emp_id,first_name,dept_name from employees natural join deptments;

--using clause
    Natural joins use all columns with matching names and data types to join the tables. The USING
clause can be used to specify only those columns that should be used for an equijoin.
    If several columns have the same names but the data
types do not match, use the USING clause to specify the
columns for the equijoin.
Use the USING clause to match only one column when
more than one column matches.
The NATURAL JOIN and USING clauses are mutually
exclusive.

select emp_id,first_name,dept_name from employees join deptments using (dept_id);
---Equijoins are also called simple joins or inner joins.

--on clause
     The join condition for the natural join is basically an
equijoin of all columns with the same name.
Use the ON clause to specify arbitrary conditions or specify
columns to join.
The join condition is separated from other search
conditions.The ON clause makes code easy to understand

select emp_id,first_name,dept_name from employees join deptments on
(employees.dept_id=employees.dept_id);

----

SELECT e.employee_id, l.city, d.department_name
FROM employees e
JOIN departments d
USING (department_id)
JOIN locations l
USING (location_id)

---self-join

SELECT worker.last_name emp, manager.last_name mgr
FROM
employees worker JOIN employees manager
ON
(worker.manager_id = manager.employee_id);

----non-equijoin
A nonequijoin is a join condition containing something other than an equality operator


---outer joins
In SQL:1999, the join of two tables returning only matched
rows is called an INNER join.
? A join between two tables that returns the results of the
   INNER join as well as the unmatched rows from the left (or
  right) table is called a left (or right) OUTER join.
? A join between two tables that returns the results of an
  INNER join as well as the results of a left and right join is a
full OUTER join.

--left outer join
--right outer join
--full outer join


---cross joins