Joins Basics

Joins Basics

EQUI JOIN / INNER JOIN 

NATURAL JOIN

------------

Based on the columns in two tables that have same name based on equal values in matched tables

EX : select department_id , DEPARTMENT_NAME ,LOCATION_ID , CITY from DEPARTMENTS   natural join LOCATIONS  where department_id in(10,20)

USING clause

---------------

If columns have same name but data is not matching , use the USING clause to specify column for equijoin

Ex : select last_name , location_id,department_id from employees JOIN departments using (department_id) where  department_id=50

ON Clause

-------------

On clause specify columns to join

Ex : select e.last_name , d.location_id,d.department_id from employees  e JOIN departments d ON (e.department_id=d.department_id) and e.MANAGER_ID=149;

ON is the more general of the two. One can join tables ON a column, a set of columns and even a condition. For example:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

USING is useful when both tables share a column of the exact same name on which they join. In this case, one may say:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE .

SELF JOIN

-----------------

Sometime you need to join table to itself to retrieve data . For example to find emp's managaer Name

select e.last_name, m.last_name from employees e join employees m  ON (e.manager_id = m.employee_id)

NON -EQUIJOIN

---------------------

A join condition other than an equality operator

Ex : 

OUTER JOIN : 

result of inner join + Return record with no direct match

Extension of ON clause

LEFT OUTER JOIN

----------------
In Left Outer Join all rows in the first-named table i.e. "left" table, which appears leftmost in the JOIN clause are included. Unmatched rows in the right table do not appear.

Brings all data from employee table even if no match from departments

 Ex : select e.last_name , d.location_id,d.department_id from employees  e LEFT outer JOIN departments d ON (e.department_id=d.department_id)

RIGHT OUTER JOIN

----------------
In Right Outer Join all rows in the second-named table i.e. "right" table, which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included.

Brings all data from departments table even if no match from employees 

Ex : select e.last_name , d.location_id,d.department_id from employees  e RIGHT outer JOIN departments d ON (e.department_id=d.department_id)

FULL OUTER JOIN

----------------

In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

ALL data

Ex : select e.last_name , d.location_id,d.department_id from employees  e FULL outer JOIN departments d ON (e.department_id=d.department_id)

CROSS JOIN

----------------

A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. The common example is when company wants to combine each product with a pricing table to analyze each product at each price.

ALL rows from first table is joined to all tables in second table

Ex : select last_name , department_name from employees cross join departments