MySQL ordering SELECT by associated column

Question

I'd like to select some Addresses from a MySQL DB. Addresses can have a Contact, an Organization, or both. (A Contact can have many Addresses. So can an Organization.)

Let's say I want to get all of an Organization's Addresses. That's no big deal. But what if I want to ORDER them by Contact.last_name? Some of the Addresses don't have a Contact.

I did some experimenting and discovered that if you ORDER BY contacts.last_name, then only those Addresses that have a Contact will be returned. I tried ORDER BY contacts.last_name, addresses.street1, but still only Addresses that had an associated Contact were returned.

Is there a query that will find Addresses by Organization.id, order the results by Contact.last_name, and include all the Organization's Addresses, even ones that don't have a Contact?

---

Table structure

addresses
---------
id
contact_id
organization_id
street1

contacts
--------
id
last_name

organizations
-------------
id
name

---

Solution:

Returns all the Organization's Addresses:

SELECT a.*
FROM addresses a
LEFT JOIN contacts c
ON a.contact_id = c.id
WHERE a.organization_id = '8283'
ORDER BY c.last_name

---

OK, great the above query solved it.

Answer 1

You could use the MySQL IFNULL function to return an empty string when there is no associated contact. That way, if there is no associated contact for an address, you still get a record with an empty string in the contact column if there is no contact.

For example:

SELECT a.*,
IFNULL(c.last_name, '') AS last_name
FROM Addresses a
LEFT JOIN Contacts c
ON a.contact_id = c.id
ORDER BY IFNULL(c.last_name, '')

As others have pointed out, ORDER BY does not filter out missing records. Ensure you have the proper join in your query and it will return records even if there is no associated contact. I have added the IFNULL to return an empty string instead of a NULL, but that is only for aesthetics and doesn't actually change the number of records returned.

Answer 2

The problem is probably the join and not the order-by clause. As soon as you join the Addresses table with the Contacts table, the join condition will eliminate all tuples with NULL values.

To avoid this, you have to use an OUTER JOIN instead of an INNER JOIN (the default). The idea behind an OUTER JOIN is simply to extend the missing table rows with NULL values for processing/ordering and so on.