SQL left join query

We can link more than one table to get the records in different combinations as per requirement. Keeping data of one area in one table and linking them each other with key field is better way of designing tables than creating single table with more number of fields. For example in a student database you can keep student contact details in one table and its performance report in another table. You can link these two tables by using on unique student identification number.

Here are two tables with some data. We will apply first sql where command to this table

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

SELECT t1.id,t1.name1 FROM `t1`,t2 WHERE t1.id=t2.id

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
1 one1
2 two1

SELECT t1.id, name1, t2.id, t2.name2 FROM t1 left join t2 on t1.id = t2.id

id name1 id name2
1 one1 1 one1
2 two1 2 two2
3 three1 NULL NULL
The above result shows a simple left join and its output. We may be interested to identify the the records in table t1 for which there is no 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 out put 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
3 three1
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
1 one1 1 one1
2 two1 2 two2

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, on is student list and other one is selected football team. Some of the students are selected to play for football team. In our football table we have kept two student ids (selected for 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 .

    Download sql dump of student and student_footbal tables .

    Number of User Comments : 5


    Google+

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.

Post Comment This is for short comments only. Use the forum for more discussions.






OPINION POLL

What is the most important factor of a web site

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2015 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer