What is Join? Explian about Inner and Outer Joins?

What is Join? Explian about Inner and Outer Joins?

We can use Join condition whenever we need to retrieve data from two (or) more tables.

Join can be used in either Where or From clause. But Join specification in From clause is recommended. We can also use Having and Where clause for filtering the data.

Joins can be classified into following types:

ü  1.Inner Join (Equi Join & Natural Join)

ü  2.Outer Join (Left Outer Join, Right Outer Join & Full Join)

ü  3.Self Join

ü  4.Cross Join

Inner Join: To retrieve matched records we can use comparison operators (i.e. =, < >).Whenever we need to get the matched records from two tables,this type of Join is used. 

Inner Join Also call as Natural Join or Equi Join.

Syntax:

SELECT emp.emp_id

                , emp. sal

                , ph.phone_no

FROM employee emp

INNER JOIN phone ph

ON emp.emp_id = ph. emp_id

WHERE emp.sal > 2000;

Outer Join: To retrieve matched and unmatched records from two tables we can use Outer Join. Unmatched rows will be displayed as NULL from both tables.

Outer Join can be classified as: Left Outer Join , Right Outer Join and Full Outer Join.

Left Outer Join: All matched and unmatched records from left table will be displayed and unmatched records will be displayed as NULL from left table.But from right table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

SELECT emp.emp_id

                , emp. sal

                , ph.phone_no

FROM employee emp

LEFT JOIN phone ph

ON emp.emp_id = ph.emp_id

WHERE emp.sal > 2000 ;

Right Outer Join: All Records from right table will be displayed either matched or unmatched and unmatched records will be displayed as NULL from right table.But from left table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

SELECT emp.emp_id

                , emp. sal

                , ph.phone_no

FROM employee emp

RIGHT JOIN phone ph

ON emp.emp_id = ph.emp_id

WHERE emp.sal > 2000 ;

Full Outer Join:  All matched and unmatched records from both left table and right table will be displayed and unmatched records will be displayed as NULL from both the tables.

Cross Join: This Join returns all rows from the left table, each and every row from the left table is in combination with all rows from the right table. Cross join also called  as Cartesian Product Join.

Syntax:

SELECT au.au_fname

                , au.au_lname

                , p.pub_name

FROM authors AS au

INNER JOIN publishers AS p

ON au.city = p.city

AND au.state = p.state

ORDER BY au.au_lname ASC

, au.au_fname ASC ;

Self Join: A table can be joined to itself is a self-join. 

For example, we can use a self-join to find out the authors in New Zealand who live in the same ZIP Code area.

SELECT au.au_fname

                , au.au_lname

                , a.au_fname

                , au2.au_lname

FROM authors au

INNER JOIN Jauthors a

ON au.zip = a.zip

WHERE au.city = ' New Zealand'

ORDER BY au.au_fname ASC

, au.au_lname ASC ;