Blog Details

Types of Joins in MYSQL


                                
                                    <h2><strong>* What is join?</strong></h2>
<ul>
	<li>An SQL <code>JOIN </code>clause is used to <strong>combine rows</strong> from <strong>two or more tables</strong>, based on a common field between them.</li>
	<li>There are many types of <code>join</code>.</li>
</ul>
<ul>
	<li>
<h3><strong>Inner Join</strong></h3>
</li>
</ul>
<strong>     1)</strong> Equi-join

<strong>     2)</strong> Natural Join
<ul>
	<li>
<h3><strong>Outer Join</strong></h3>
</li>
</ul>
<strong>     1)</strong> Left outer Join

<strong>    2)</strong> Right outer join

<strong>    3)</strong> Full outer join
<ul>
	<li>
<h3><strong>Cross Join</strong></h3>
</li>
</ul>
<ul>
	<li>
<h3><strong>Self Join</strong></h3>
</li>
</ul>
<h2>1) INNER JOIN :-</h2>
&nbsp;
<h3>     a) Equi-join</h3>
<ul>
	<li>An SQL JOIN clause is used to <strong>combine rows</strong> from two or more tables, based on a <strong>common field</strong> between them.</li>
	<li>Inner join also <strong>called equi join</strong>.</li>
	<li>Returns all rows when there is <strong>at least one match</strong> in BOTH tables</li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<pre>SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

</pre>
<h3><strong>Example :</strong></h3>
<pre>SELECT s.supplier_id, s.supplier_name, od.order_date
FROM suppliers AS s
INNER JOIN order AS od
ON s.supplier_id = od.supplier_id;

</pre>
<ul>
	<li>This SQL INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.</li>
</ul>
&nbsp;
<h3>     b) Natural-join</h3>
<ul>
	<li>The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values.</li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<ul>
	<li>SELECT * FROM table_name1 t1 NATURAL JOIN table_name2 t2</li>
</ul>
&nbsp;
<h3><strong>Example :</strong></h3>
<ul>
	<li>SELECT * FROM UserDetails NATURAL JOIN OrderDetails</li>
</ul>
&nbsp;
<h2>2) Outer JOIN :-</h2>
&nbsp;
<h3>          a) Left Outer join :</h3>
<ul>
<ul>
	<li>Left join displays all the rows from first table and matched rows from second table like that..</li>
	<li>Return <strong>all rows</strong> from the <strong>left table</strong>, and the <strong>matched rows</strong> from the <strong>right table</strong></li>
</ul>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<pre>SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

</pre>
<h3><strong>Example :</strong></h3>
<pre>SELECT suppliers.supplier_id, 
suppliers.supplier_name, 
orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

</pre>
<ul>
	<li>This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.</li>
	<li>If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as &lt;null<em>&gt;</em> in the result set.</li>
</ul>
&nbsp;
<h3>       b) Right Outer join :</h3>
<ul>
	<li>Right outer join displays all the rows of second table and matched rows from first table like that.</li>
	<li>Return <strong>all rows</strong> from the <strong>right table</strong>, and the matched rows from the <strong>left table</strong></li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<pre>SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

</pre>
<h3><strong>Example :</strong></h3>
<pre>SELECT orders.order_id, orders.order_date, 
suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

</pre>
<ul>
	<li>This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.</li>
	<li>If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as &lt;null<em>&gt;</em> in the result set.</li>
</ul>
&nbsp;
<h3>  c) Full Outer join :</h3>
<ul>
	<li>Another type of join is called a SQL <strong>FULL OUTER JOIN</strong>. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.</li>
	<li>Full outer join returns <strong>all the rows</strong> from <strong>both tables</strong> whether it has been <strong>matched or not.</strong></li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<pre>SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

</pre>
<h3><strong>Example :</strong></h3>
<pre>SELECT suppliers.supplier_id, suppliers.supplier_name, 
orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

</pre>
<ul>
	<li>This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, &lt;nulls&gt; would be extended to those fields in the result set.</li>
</ul>
&nbsp;
<h2>3) Cross JOIN :-</h2>
<ul>
	<li>A cross join that produces <strong>Cartesian product</strong> of the tables that are involved in the join.</li>
	<li>The size of a Cartesian product is the <strong>number of the rows</strong> in the <strong>first table</strong> <strong>multiplied by</strong> the number of rows in the <strong>second table</strong> like this.</li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<ul>
	<li>SELECT * FROM table_name1  CROSS JOIN  table_name2      <strong>OR</strong></li>
	<li>SELECT * FROM table_name1,table_name2</li>
</ul>
&nbsp;
<h3><strong>Example :</strong></h3>
<ul>
	<li>SELECT * FROM UserDetails CROSS JOIN OrderDetails</li>
</ul>
&nbsp;
<h2>4) Self JOIN :-</h2>
<ul>
	<li>Joining the table <strong>itself</strong> called self join.</li>
	<li>Self join is used to retrieve the records having some relation or similarity with other records in the same table.</li>
	<li>Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.</li>
</ul>
&nbsp;
<h3>SYNTAX :</h3>
<pre>SELECT columns
FROM table1 t1
INNER JOIN table1 t2
ON t1.column = t2.column;

</pre>
<h3><strong>Example :</strong></h3>
<ul>
	<li>select e2.EmpName,e1.EmpName as 'Manager'</li>
</ul>
<div>              from Employee e1</div>
<div>              INNER JOIN Employee e2</div>
<div>              on e1.EmpID=e2.EmpMgrID</div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<div></div>
<h5></h5>
<pre></pre>
                                    


Categories

Php mysql

Latest posts

13.05.14
Implementing CRUD Operations ¶

<ul> <li>Now comes the fun part. We would like t

13.05.14
View

<ol> <li><a href="http://www.yiiframework.com/do

13.05.14
Creating Model

<ol> <li><a href="http://www.yiiframework.com/do

Get Free Quote