Blog Details

Types of Joins in MYSQL



                                

* What is join?

  • An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
  • There are many types of join.
  • Inner Join

     1) Equi-join      2) Natural Join
  • Outer Join

    1) Left outer Join     2) Right outer join     3) Full outer join
  • Cross Join

  • Self Join

1) INNER JOIN :-

 

     a) Equi-join

  • An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
  • Inner join also called equi join.
  • Returns all rows when there is at least one match in BOTH tables
 

SYNTAX :

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Example :

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;

  • 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.
 

     b) Natural-join

  • The Natural join is same as our Equi join but only the difference is it will restrict to display redundant values.
 

SYNTAX :

  • SELECT * FROM table_name1 t1 NATURAL JOIN table_name2 t2
 

Example :

  • SELECT * FROM UserDetails NATURAL JOIN OrderDetails
 

2) Outer JOIN :-

 

          a) Left Outer join :

    • Left join displays all the rows from first table and matched rows from second table like that..
    • Return all rows from the left table, and the matched rows from the right table
 

SYNTAX :

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Example :

SELECT suppliers.supplier_id, 
suppliers.supplier_name, 
orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

  • 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.
  • 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 <null> in the result set.
 

       b) Right Outer join :

  • Right outer join displays all the rows of second table and matched rows from first table like that.
  • Return all rows from the right table, and the matched rows from the left table
 

SYNTAX :

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Example :

SELECT orders.order_id, orders.order_date, 
suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

  • 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.
  • 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 <null> in the result set.
 

  c) Full Outer join :

  • Another type of join is called a SQL FULL OUTER JOIN. 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.
  • Full outer join returns all the rows from both tables whether it has been matched or not.
 

SYNTAX :

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

Example :

SELECT suppliers.supplier_id, suppliers.supplier_name, 
orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

  • 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, <nulls> would be extended to those fields in the result set.
 

3) Cross JOIN :-

  • A cross join that produces Cartesian product of the tables that are involved in the join.
  • The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this.
 

SYNTAX :

  • SELECT * FROM table_name1  CROSS JOIN  table_name2      OR
  • SELECT * FROM table_name1,table_name2
 

Example :

  • SELECT * FROM UserDetails CROSS JOIN OrderDetails
 

4) Self JOIN :-

  • Joining the table itself called self join.
  • Self join is used to retrieve the records having some relation or similarity with other records in the same table.
  • 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.
 

SYNTAX :

SELECT columns
FROM table1 t1
INNER JOIN table1 t2
ON t1.column = t2.column;

Example :

  • select e2.EmpName,e1.EmpName as 'Manager'
              from Employee e1
              INNER JOIN Employee e2
              on e1.EmpID=e2.EmpMgrID
 
                                


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