SQL left join query

We keep data in different tables based on their properties and way they are used. Student can appear several examinations and its mark can be stored in examination result table. Student master table need not have all these details like semester or type of examination or mark in each examinations.

A report can be generated by collecting student details from Student master table and the semester and marks from the examination result table.

Let us understand the difference between LEFT join and WHERE condition.
There are two tables with some sample data. We will apply first sql where command to these tables.

Table one ( t1 ) Table two ( t2)
id name1
1one1
2two1
3three1
id name2
1one2
2two2
4four2

Joining tables Using SQL LEFT Join, RIGHT Join and Inner Join

Using WHERE Query

SELECT t1.id,t1.name1 FROM `t1`,t2 WHERE t1.id=t2.id
Where Query The output of the above SQL command will give records of MySQL table t1 for which there is a record present in table t2 and linking is done by id field.
id name1
1one1
2two1
Area as per venn diagram B

USING LEFT JOIN

SELECT t1.id, name1, t2.id, t2.name2 FROM t1
 LEFT JOIN t2 ON t1.id = t2.id
Venn diagram Left Join We can display all the  records of table t1 along with the (matching ) records of t2 if matching is found by linking them through ID field. Here is the left join SQL to display this and below that the output is listed.

id name1 id name2
1one11one2
2two12two2
3three1NULLNULL
Area as per Venn diagram A + B

LEFT JOIN with IS NULL

Venn diagram Left Join with null  The above result shows a simple left join and its output. We may be interested to identify the records in table t1 for which there is no matching record present in table t2. Here we have to use left join and link the tables by id field.

Here is the query using left join and the output is below that. 
SELECT t1.id, name1 FROM t1 LEFT JOIN t2 on t1.id = t2.id
 WHERE  ISNULL(t2.id)
for MySQL 5 and above try like this

SELECT t1.id, name1 FROM t1 LEFT JOIN  t2 on t1.id = t2.id
 WHERE (t2.id) is null
id name1
3three1
Area as per Venn diagram A
In the above result we have seen how the output works when we have to get records which are not present in other table. Now let us find out the other way , we will get the record of table one for which matching record exists in table 2. Here is the left join sql and below that the results of the query.

SELECT t1.id,name1,t2.id,t2.name2 FROM t1 
LEFT JOIN t2 ON t1.id = t2.id  WHERE  NOT ISNULL( t2.id )
for MySQL 5 try like this

SELECT t1.id,name1,t2.id,t2.name2 FROM t1 
LEFT JOIN t2 ON t1.id = t2.id  WHERE  ( t2.id ) NOT is null
id name1 id name2
1one11one2
2two12two2

PHP script using left join to display records

We will try to develop one PHP script to display records by using left join query. Here we have used PHP PDO connection to connect to MySQL database. You can read more on PHP connection to MySQL using PDO here.
After connection we have used foreach loop to display all the records. Here is the code.
<?Php
require "config.php"; // Database Connection details

$count="SELECT t1.id, name1 FROM t1 LEFT JOIN t2 on t1.id = t2.id
 WHERE (t2.id) is null";

echo "<table>";
echo "<tr><th>id</th><th>name1</th></tr>";
foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name1]</td></tr>";
}
echo "</table>";
?>
We can replace the query part in above code to check other results.

Here is the SQL dump of two tables t1 & t2

Application of LEFT Join Query

We have two tables, one is the student list and the other one is a selected football team. Some of the students are selected to play for the football team. In our football table, we have kept two student ids (selected for the football team ).

Using left join we can list out various way to display the list.
  • Display all students with a mark for those who are selected
  • SELECT * FROM `student` LEFT JOIN student_football ON id=f_id
    student list of LEFT JOIN

    From the above list you can see by using WHERE condition we can filter out our required records
  • List students who are selected for football team
  • SELECT * FROM `student` LEFT JOIN student_football
     ON id=f_id WHERE f_id IS NOT NULL
    selected IS NOT NALL student list

  • Who are not selected for football team
  • SELECT * FROM `student` LEFT JOIN student_football
     ON id=f_id WHERE  f_id IS NULL

    Using three tables in LEFT Join Query

    In above example we have seen how to find out records present in one table and not present in another table. Let us try for another example.

    In addition to football team there is one more baseball team. Two students are selected for baseball team. So now we have two in football team and two in baseball team.

    Display all students with a mark for those who are selected for teams ( any )
    SELECT * FROM `student`
    LEFT JOIN student_football ON id=f_id
    LEFT JOIN student_baseball on id=b_id
    selected IS NOT NALL student list with baseball team

    Next, let us find out who are the students not selected in any of the team. In other words, find out the records in student table that don't have matching records in football or baseball table ( team )
    SELECT * FROM `student`
    LEFT JOIN student_football ON id=f_id  
    LEFT JOIN student_baseball on id=b_id 
    WHERE f_id IS NULL  and b_id is NULL
    We can get similar result by using UNION , NOT IN and subqueries.

    You can read more Null data here .

    How aggregate data from one table is used to update another table by using LEFT Join

    Download sql dump of student and student_footbal tables

    .
    For better understanding of LEFT JOIN , try these exercises
    Exercise I Sample code for generating reports on Sales.
    Exercise II Sample code for generating Product Stock and Price .
    CROSS JOIN LEFT JOIN using Multiple Tables
    RIGHT JOIN LINKING TABLES Union of two tables and more Joining the table to itself by inner join
    Linking of more than one table to get matching records Sub Queries to get records with IN and ANY Exercise : Sales - Agent using table JOIN and Date functions
    Subscribe to our YouTube Channel here


    Subscribe

    * indicates required
    Subscribe to plus2net

      plus2net.com
      Valerie

      21-11-2011

      This was very helpful, thanks. Your choice of table and field names made it easy to understand what you were doing.
      ramesh

      24-01-2012

      short and sweet...... tanks for ur info......
      Bakht azam

      19-04-2012

      so grate notes i got more information from your site thankx
      Langat

      18-10-2013

      This was very helpful me, thanks now I can link tables in MYSQL
      Jitender

      21-04-2015

      Thanks a lot, you solved my problem.
      teaser141

      04-02-2016

      Thanks. This article were helpful :-)

      Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer