MySQL Left join


SQL (MySQL) LEFT JOIN will obtain the left table (table1) all records, even if the right table (table2) does not match the corresponding record. The basic syntax of LEFT JOIN is as follows:

 ... FROM table1 LEFT JOIN table2 ON condition ...

examples

Here are the two data tables :

Article table :

id title content uid
1 Article 1 Article 1 Body Content ... 1
2 Article 2 Article 2 Body Content ... 1
3 Article 3 Article 3 Body Content ... 2

User table:

uid username email
1 admin admin@gmail.com
2 John jack@gmail.com
3 Jack jack@gmail.com

We list all the articles and their corresponding users, even if no user's article is listed.

SELECT ... LEFT JOIN ... ON statement reads as follows:

SELECT article.aid, article.title, user.username FROM article LEFT JOIN user ON article.uid = user.uid

The results are as follows:

Article table :

id title username
1 Article 1 Admin
2 Article 2 Admin
3 Article 3 John
4 Article 4 NULL

Difference with INNER JOIN is that the left table records are all removed, even if the right table does not match the matching records.


Note

Here the so-called record is "all" out, is relative to the limitations of the INNER JOIN. In fact, the above SQL statement can be followed by a WHERE condition or LIMIT keywords such as the same general SQL statement to do a range limit on the result set.


IS NULL

In the above example, for the right table does not match the corresponding data records, all of its columns are set to NULL, so to query this part of the record (such as in the above example is reflected in the search for aid = 4 such non-corresponding user Of the article record), you can attach an IS NULL condition:

SELECT article.aid, article.title, user.username FROM article LEFT JOIN user 
ON article.uid = user.uid WHERE user.uid IS NULL



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook