Conditional Join: don't join on null

Question

I have relationships that might not necessarily exist (they could be optional i.e. null); for example, a image may not have an address so it may be null.

I am unsure how to not return all null values.

Is there some condition I can put in place on the join that says if the address is null don't do a join and don't return all the null columns?

SELECT im.title, im.alias_title, im.description, im.main_image, im.hits, 
    im.show_comment, im.created_on, im.date_taken, im.account_type_id,
    c.make, c.model, ad.address_line_1, ad.address_line_2, 
    spc.state_province_county, tvc.town_village_city, co.country, 
    ge.latitude, ge.longitude, ge.zoom, ge.yaw, ge.pitch, 
    us.first_name, us.surname, us.user_set_online, ut.username,
    ut.account_type_id, aty.`type`, ufy.realname, ufy.location, 
    ufy.location, ufy.account_type_id
FROM image im 
    INNER JOIN user us 
            ON im.user_id = us.id
     LEFT JOIN user_type ut 
            ON us.id = ut.user_id
     LEFT JOIN user_flickr_youtube ufy 
            ON ut.id = ufy.user_type_id  
     LEFT JOIN account_type aty 
            ON ut.account_type_id =aty.id
     LEFT JOIN address ad 
            ON im.address_id = ad.id
     LEFT JOIN state_province_county spc 
            ON ad.state_province_county_id = spc.id
     LEFT JOIN town_village_city tvc 
            ON ad.town_village_city_id =tvc.id
     LEFT JOIN country co 
            ON ad.country_id =co.id
     LEFT JOIN geolocation ge 
            ON im.geolocation_id = ge.id
     LEFT JOIN camera c 
            ON im.camera_id = c.id
WHERE im.alias_title = 'test' 
    AND im.approved = 'Yes' 
    AND im.visible = '1'
LIMIT 1;

Answer 1

Is there some condition i can put in place on the join that says if the address is null dont do a join and dont bring me back all the null columns

Yes; you can run a JOIN instead of a LEFT JOIN. But that won't simply exclude the address if it is NULL, it will ignore the whole row altogether.

Usually this kind of situation is either handled by supplying a default value, possibly empty, for example directly in MySQL

SELECT
...COALESCE(ad.address_line_1,'(no address)') AS address_line_1,
   COALESCE(ad.address_line_2,'')             AS address_line_2, ...

or it is handled by the application:

if row['address_line_1']:
   result = result + ("<td class=\"address\">%s</td>" % ( row['address_line_1'] ))
...

This also because a query could potentially return not one record, but several, and of these, some might have a NULL colum and some might not.

UPDATE

There is a way, but it's likely to make milk go sour in cows fifty miles downrange.

This is a proof of concept, on a MUCH smaller query and table, and takes advantage of the possibility of dynamically building a query.

First of all we have our query WHERE condition, here represented by "id = 1". We want to have the name column if the name column is not NULL.

SELECT @address := COALESCE(MIN(',name'),'') FROM client WHERE name IS NOT NULL AND id = 1;

This will return an empty string if the selected column is NULL. Otherwise it will return a comma and the name of that column.

This is the statement that in your case will be humongous, given your query. It contains the same WHERE as before, without the request that the name be NULL. And the field list is now dynamic.

SELECT @string := CONCAT('SELECT id', @address, ' FROM client WHERE id = 1');

Except that @string is, well, a string. To execute it as a query we do

PREPARE query FROM @string;
EXECUTE query;
DEALLOCATE PREPARE query;

How this might interact with your application, I do not dare fathom. I have tried an implementation in PHP on an expendable VM :-), cycling between the values of 1 and 3 (one row has a NULL name, one hasn't).

<?php
        // Connect to this VM's local DB
        mysql_connect('localhost','root','') or die("Cannot connect");
        mysql_select_db('test');

        foreach(array(1, 3) as $id)
        {
                mysql_query("SELECT @address := COALESCE(MIN(',name'),'') FROM client WHERE name IS NOT NULL AND id = $id;");
                mysql_query("SELECT @string := CONCAT('SELECT id', @address, ' FROM client WHERE id = ', $id);");
                mysql_query("PREPARE query FROM @string;");
                $exec = mysql_query("EXECUTE query;");
                while($tuple = mysql_fetch_assoc($exec))
                {
                        print implode(" | ", $tuple) . "\n";
                }
                mysql_query("DEALLOCATE PREPARE query;");
        }
?>

The answer seems to indicate it's working:

1 | Rossi
3

(I wouldn't have been surprised if it returned something like 'Ia! Cthulhu fhtagn!').