Search Marketing

   
   

How To Use Left Join Select Queries

Tue Feb 02, 2010 12:47 am
<<     >>
Comments: 2 Views: 3020

One use of a LEFT JOIN query is to return NULL values as well as matches on at least two different database tables.

To select data from at least two different tables in this example we will use the following database tables.

1. Find all the names in the Name table AND provide the location, if any of where they work.

mysql> select * from Name;
+----+--------+---------------------+
| ID | Name | Date |
+----+--------+---------------------+
| 1 | Joe | 2001-02-12 00:00:00 |
| 2 | Jack | 2003-03-15 00:00:00 |
| 3 | John | 1980-05-15 00:00:00 |
| 4 | James | 1994-09-21 00:00:00 |
| 5 | George | 1996-12-11 00:00:00 |
| 6 | Paul | 2007-10-09 12:54:58 |
| 7 | Simon | 2010-01-01 00:00:00 |
| 8 | Jim | 2009-12-15 00:00:00 |
+----+--------+---------------------+
8 rows in set (0.04 sec)

mysql> select * from Work;
+----+---------------+--------+
| ID | Location | NameID |
+----+---------------+--------+
| 1 | New York | 3 |
| 2 | San Francisco | 2 |
+----+---------------+--------+

2. The following query will provide the correct information, as well as any NULL values, for all the names that exist.

mysql> select n.ID,n.Name,w.Location from Name n LEFT JOIN Work w ON n.ID=w.NameID;
+----+--------+---------------+
| ID | Name | Location |
+----+--------+---------------+
| 1 | Joe | NULL |
| 2 | Jack | San Francisco |
| 3 | John | New York |
| 4 | James | NULL |
| 5 | George | NULL |
| 6 | Paul | NULL |
| 7 | Simon | NULL |
| 8 | Jim | NULL |
+----+--------+---------------+

3. If you did not want the NULL values then you could use an INNER JOIN.

mysql> select n.ID,n.Name,w.Location from Name n,Work w where n.ID=w.NameID;
+----+------+---------------+
| ID | Name | Location |
+----+------+---------------+
| 3 | John | New York |
| 2 | Jack | San Francisco |
+----+------+---------------+



Comments
Wed Jan 21, 2009 1:06 pm
Name: polivios | Comment: Very good example!!

Tue Apr 21, 2009 2:47 pm
Name: akameng | Comment: Thanks!
SImple and Quick

Name:
*
URL: http: (ex. cnn.com)
Comment:
*
Number:
Math (18 + 4)
* required
SubCategories
Books

© 2017 Christonium LLC

Christonium.com
|
Terms of Use
|
Privacy
ccc