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

We can display all the  records of table t1 along with the (matching ) records of t2 if matching is found by linking them in ID field. Here is the left join sql to display this and below that the output is listed.

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

Related Tutorial • MySQL INNER Join • SQL select • Number of Affected rows

List of SQL Tutorials