[an error occurred while processing this directive]

Joins

Sometimes you required the data from more than one table. When you select the data from more than one table this is known as Joining. A join is a SQL query that is used to select the data from more than one table or views. When you define multiple tables or views in the FROM clause of a query the MySQL performs a join that linking the rows from multiple tables together.

Types of Joins :

INNER Joins
OUTER Joins
SELF Joins

We are going to describe you the Join with the help of following two tables :

mysql> SELECT * FROM Client;
+------+---------------+----------+
| C_ID | Name          | City     |
+------+---------------+----------+
| 1    | A K Ltd       | Delhi    |
| 2    | V K Associate | Mumbai   |
| 3    | R K India     | Banglore |
| 4    | R S P Ltd     | Kolkata  |
+------+---------------+----------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM Products;
+---------+-------------+------+
| Prod_ID | Prod_Detail | C_ID |
+---------+-------------+------+
| 111     | Monitor     | 1    |
| 112     | Processor   | 2    |
| 113     | Keyboard    | 2    |
| 114     | Mouse       | 3    |
| 115     | CPU         | 5    |
+---------+-------------+------+
5 rows in set (0.00 sec)

INNER Joins

The INNER join is considered as the default Join type. Inner join returns the column values from one row of a table combined with the column values from one row of another table that satisfy the search condition for the join. The general syntax of INNER Join is :
SELECT <column_name1>, <column_name2> FROM <tbl_name> INNER JOIN <tbl_name> ON <join_conditions>

The following example takes all the records from table Client and finds the matching records in table Product. But if no match is found then the record from table Client is not included in the results. But if multiple results are found in table Product with the given condition then one row will be return for each.
Example :

mysql> SELECT * FROM Client
    -> INNER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
+------+---------------+----------+---------+-------------+------+
4 rows in set (0.04 sec)

OUTER Joins

Sometimes when we are performing a Join between the two tables, we need all the records from one table even there is no corresponding record in other table. We can do this with the help of OUTER Join. In other words an OUTER Join returns the all rows that returned by an INNER Join plus all the rows from one table that did not match any row from the other table. Outer Join are divided in two types : LEFT OUTER Join, RIGHT OUTER Join

LEFT OUTER Join

LEFT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from first table that did not match with any row from the second table but with the NULL values for each column from second table. The general syntax of LEFT OUTER Join is :
SELECT <column_name1>, <column_name2> FROM <tbl_name> LEFT OUTER JOIN <tbl_name> ON <join_conditions>

In the following example we are selected every row from the Client table which don’t have a match in the Products Table. Example :

mysql> SELECT * FROM CLIENT
    -> LEFT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| 4    | R S P Ltd     | Kolkata  | NULL    |             | NULL |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.00 sec)

In the following example we are using the ORDER BY Clause with the LEFT OUTER Join.

mysql> SELECT * FROM Client
    -> LEFT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID
    -> ORDER BY Client.City;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 4    | R S P Ltd     | Kolkata  | NULL    |             | NULL |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.08 sec)

In the result of LEFT OUTER Join " R S P Ltd " is included even though it has no rows in the Products table.

RIGHT OUTER Join

RIGHT OUTER Join is much same as the LEFT OUTER JOIN. But RIGHT OUTER Join is used to return all the rows that returned by an INNER Join plus all the rows from second table that did not match with any row from the first table but with the NULL values for each column from first table. The general syntax of RIGHT OUTER Join is :
SELECT <column_name1>, <column_name2> FROM <tbl_name> RIGHT OUTER JOIN <tbl_name> ON <join_conditions>

In the following example we are selected every row from the Products table which don’t have a match in the Client Table. Example :

mysql> SELECT * FROM Client
    -> RIGHT OUTER JOIN Products
    -> ON Client.C_ID=Products.C_ID;
+------+---------------+----------+---------+-------------+------+
| C_ID | Name          | City     | Prod_ID | Prod_Detail | C_ID |
+------+---------------+----------+---------+-------------+------+
| 1    | A K Ltd       | Delhi    | 111     | Monitor     | 1    |
| 2    | V K Associate | Mumbai   | 112     | Processor   | 2    |
| 2    | V K Associate | Mumbai   | 113     | Keyboard    | 2    |
| 3    | R K India     | Banglore | 114     | Mouse       | 3    |
| NULL |               |          | 115     | CPU         | 5    |
+------+---------------+----------+---------+-------------+------+
5 rows in set (0.03 sec)

SELF Join

SELF Join means a table can be joined with itself. SELF Join is useful when we want to compare values in a column to other values in the same column. For creating a SELF Join we have to list a table twice in the FROM clause and assign it a different alias each time. For referring the table we have to use this aliases.

The following example provide you the list of those Clients that belongs to same city of C_ID=1.

mysql> SELECT b.C_ID,b.Name,b.City FROM Client a, Client b
    -> WHERE a.City=b.City AND a.C_ID=1;
+------+----------+-------+
| C_ID | Name     | City  |
+------+----------+-------+
| 1    | A K Ltd  | Delhi |
| 5    | A T Ltd  | Delhi |
| 6    | D T Info | Delhi |
+------+----------+-------+
3 rows in set (0.00 sec)

we can write this SELF JOIN Query in Subquery like this also :

mysql> SELECT * FROM Client
    -> WHERE City=(
    -> SELECT City FROM Client
    -> WHERE C_ID=1);
+------+----------+-------+
| C_ID | Name     | City  |
+------+----------+-------+
| 1    | A K Ltd  | Delhi |
| 5    | A T Ltd  | Delhi |
| 6    | D T Info | Delhi |
+------+----------+-------+
3 rows in set (0.03 sec)

Facing Programming Problem? Ask Questions? Browse Latest Questions Question-Answer Guidelines
Add This Tutorial To:
Del.icio.us	Digg	Google	Spurl	Blink	Furl	Simpy	Y! MyWeb

Current Comments

3 comments so far (post your own) View All Comments Latest 10 Comments:

This material is pretty good for the beginners, giving them a comprehensive knowledge about MySQL. I appreciate the work done behind.
Please accept my regards.

Thanks,
Deepak E.

Posted by deepak on Wednesday, 02.20.08 @ 17:08pm | #49212

View This Comment Separately

its very gud site. I learn so much. Thanks guys who build this site

bye

Posted by jitendra on Wednesday, 08.29.07 @ 22:26pm | #24445

View This Comment Separately

I have a query related to joins.

Problem is that i want to access data from two table. from first table all record i want to retrive but seconds table not have all records related to first table record. i used left outer join but it returns NULL value for second table that have no record. Please suggest me what should i do?
ranu

Posted by ranu on Thursday, 05.10.07 @ 17:40pm | #15578

View This Comment Separately

Leave your comment:
Name:
Email:
URL:
Title:
Comments:
Enter Code:
	Note: Emails will not be visible or used in any way, and are not required. Please keep comments relevant. Any content deemed inappropriate or offensive may be edited and/or deleted. No HTML code is allowed. Line breaks will be converted automatically. URLs will be auto-linked. Please use BBCode to format your text.

  JDO Tutorials
  EAI Articles
  Struts Tutorials
  Java Tutorials
  Java Certification

Browse all Java Tutorials

Java New to Java? Learn Java In A Day Learn Java Quickly Java Tools Beginners Java Tutorial Master Java Tutorial (TOC) Java IO Package Examples Java AWT Package Examples Swing Example Java util Examples Java Programming	JSP Introduction to JSP JSP Architecture Introduction to the JSP tags Features of JSP 2.1 JSP Fundamentals	Struts Introduction to the Apache Struts Struts Architecture Introduction to the Struts Controller Introduction to Struts 2 Struts 2 Features Struts 2 History	Servlets History of Web application Introduction to Web Server What is servlet? Methods of Servlet Advantages of Java Servlets	Hibernate Introduction to Hibernate 3.0 Hibernate Architecture Hibernate Update Query Hibernate Count Query Hibernate Projections	XML XML : An Introduction XML: An Introduction - Brief History XML:Validation Introduction to DTD DTD:Document Type Definition
Ajax What is Ajax? Ajax Code Libraries and Tools Ajax Login Example	JDBC What is JDBC? JDBC Driver and Its Types Features of JDBC 3.0	EJB Introduction to Application Server Features of EJB 3.0 Message Driven Beans	MySQL Introduction to MySQL Installing MySQL on Windows MySQL Configuration	JavaScript What is JavaScript? Statements In JavaScript Functions in JavaScript	JSF Introduction To JSF JSF Releases JSF Features
Maven2 Tutorial An introduction to Maven 2 Maven 2: Features Maven 2 Eclipse Plug-in	JEE5 Tutorial Understanding JEE technologies JEE 5 API Deploying Application	Java Threading Tutorial Overview of Thread Thread Creation Daemon Thread	Photoshop Tutorials How to design a express realization mode How to design a table lamp	Linux Linux Tutorials Linux CDs Linux Distributions Free Linux CDs	Technology GPS WiMAX VoIP WiFi HSDPA Tech. What is?
Technology Revolutions Rich Internet Application Web 2.0 Web 3.0 JavaFX	Eclipse About IDE Creating a Project	Spring Tutorial Introduction to the Spring Framework	Bioinformatics Tutorials History of Bioinformatics	Tools Junit Jmeter DBUnit	SQL SQL: A brief Introduction Create a database

About Us | Advertising On RoseIndia.net | Site Map

Send your comments, Suggestions or Queries regarding this site at roseindia_net@yahoo.com.