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 ;
