September 20, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Choosing the Right SQL Join, Page 2

  • February 14, 2011
  • By Jason Gilmore
  • Send Email »
  • More Articles »

SQL Join Concept #2: The SELF JOIN

It would be useful to have a look at which customers were responsible for referring a new customer. Because the referrer's referral ID is stored in the very same table where customer information is stored, we need to execute a SELF JOIN. The SELF JOIN is actually a variation of the INNER JOIN in which both tables happen to be identical.

To illustrate the example, here is a partial listing of the customers table:

+----+---------------+-------------+
| id | name | referral_id |
+----+---------------+-------------+
| 1 | John Smith | 0 |
| 2 | Josey Wales | 1 |
| 3 | Bob McClain | 1 |
| 4 | Peter Sellers | 3 |
+----+---------------+-------------+

Therefore to retrieve both the customer's name and the associated referrer's name, the join query must be constructed in such a way that we're retrieving the referrer's name based on a comparison of the id and the referral_id columns.

SELECT c.name as 'Customer', r.name as 'Referrer'
FROM customers c
INNER JOIN customers r
ON c.referral_id = r.id;

Executing this query produces the following results:

+---------------+-------------+
| Customer | Referrer |
+---------------+-------------+
| Josey Wales | John Smith |
| Bob McClain | John Smith |
| Peter Sellers | Bob McClain |
+---------------+-------------+

Remember: you should use a SELF JOIN when you need to compare a table's primary key to a foreign key, which happens to reside in the same table.

SQL Join Concept #3: The OUTER JOIN

In your drive to create a successful business, it's crucial to maximize the potential of your most valuable resource: the customer. Word of mouth has been proven time and again to be the most effective way to attract new business, and therefore encouraging every customer to make a referral is crucial. One way to make sure every customer refers at least one friend is to further incentivize those customers who have yet to refer somebody. This is the opposite tack taken in the previous example, because this time we want to know only about customers whose primary key is not associated with a foreign key.

A LEFT OUTER JOIN is useful for learning more about records that do not appear in another table, because it will return all records from the table even if they do not appear in the other table. Those that do not appear in the other table will return NULL values within the desired column. Consider the following example and associated output:

SELECT c.name, o.customer_id 
FROM customers c
LEFT OUTER JOIN orders o
ON c.id = o.customer_id;

+---------------+-------------+
| name | customer_id |
+---------------+-------------+
| John Smith | 1 |
| John Smith | 1 |
| John Smith | 1 |
| Josey Wales | 2 |
| Josey Wales | 2 |
| Bob McClain | NULL |
| Peter Sellers | 4 |
| Peter Sellers | 4 |
+---------------+-------------+

Notice the NULL value associated with Bob McClain. This means that Bob has yet to make a purchase. Of course, he's the only prospective customer we're interested in, so you can modify the previous query to filter according to which customer_id values are NULL:

SELECT c.name FROM customers c 
LEFT OUTERJOIN orders o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

Executing this query returns:

+-------------+
| name |
+-------------+
| Bob McClain |
+-------------+

Choosing the Right SQL Join

When it's time to start taking advantage of SQL joins, take the time to consider the query's expected behavior--especially the desired data's relationship with the tables in question. Being able to conceptualize this relationship is going to help you to quickly determine which type of join should be embraced, saving you a great deal of time and tears!


Tags: SQL, joins

Originally published on http://www.developer.com.

Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel