Ignore null results in MySQL JOIN queries

Question

I have a MySQL join query which works well (disclaimer: taken from tutorial):

<?php
    $connection = mysql_connect("localhost", "root", "test") or die("Error connecting to database");
    mysql_select_db("products90", $connection);
    $result = mysql_query("SELECT * FROM buyers LEFT JOIN products USING (id);", $connection) or die("error querying database");
    $i = 0;
    while($result_ar = mysql_fetch_assoc($result)){
    ?>
    <table>
    <tr <?php if($i%2 == 1){ echo "class='body2'"; }else{echo "class='body1'";}?>>
    <td>
    <?php echo $result_ar['buyer_name']; ?></td>
    <td>
    <?php echo $result_ar['manufacturer']; ?>
    </td>
    <td>
    <?php echo $result_ar['product_name']; ?>
    </td>
    </tr>
    </table>
    <?php
    $i+=1;
    }
    ?>

However, if I wanted it to ignore NULL results, what would I need to do with the JOIN, which type is appropriate?

I had a look on Google, but am not sure where to proceed from here.

score 2 · Answer 1 · answered Jul 14 '11 at 22:39

2

By saying you want to ignore NULL results what exactly do you mean? Are you saying that when your left join executes you're getting some buyers which NULL next to them where products would be (had they bought some)?

In that case what you're looking for is an INNER JOIN which will only show values if it has a match in the other.

answered Jul 14 '11 at 22:39

m.edmondson

30,382
27
123
206

If a product is discontinued I've made the column NULL - database has a copy made of it, this is a testing copy. – avenas8808 Jul 14 '11 at 22:45

score 2 · Answer 2 · answered Jul 14 '11 at 22:40

When you say you have null results, you mean where there are no products?

Anyway, if you have null IDs, therefore, no result in products, you could just also add

where products.id is not null

or, you could change the join from left join to just join, making it an inner join

Sabeen Malik · Answer 3 · 2011-07-14T22:47:32.253

1

SELECT * FROM buyers LEFT JOIN products USING (id) WHERE products.id IS NOT NULL

OR

SELECT * FROM buyers JOIN products USING (id)

Using JOIN only brings matched rows where as the LEFT JOIN brings all buyers regardless of any matches found in products and filters the records afterwards. I am not exactly sure about your scenario but it seems simpler to me to just use JOIN instead of LEFT JOIN

edited Jul 14 '11 at 22:47

answered Jul 14 '11 at 22:39

Sabeen Malik

10,816
4
33
50

Thanks, but what if I had two values - products.id and the product_name column - I just get the "error querying database" message. – avenas8808 Jul 14 '11 at 22:41
well, you should only do LEFT JOINs when necessary, since the query optimizer cannot optimize them (order is important for LEFT JOINs). So you should go for the second query – wonk0 Jul 14 '11 at 22:42
@avenas8808 use `or die(mysql_error())` instead and it would output the accurate error. – Sabeen Malik Jul 14 '11 at 22:44
@avenas8808 i think you should show us your DB schema of both tables. Seems to me that you don't have a foreign key in the products table from your above comment. – Sabeen Malik Jul 14 '11 at 22:50

score 1 · Answer 4 · answered Jul 14 '11 at 22:40

1

You would use an INNER JOIN for that (if I got your question right).

For an overview of JOIN types in MySQL please refer to http://dev.mysql.com/doc/refman/5.1/en/join.html

answered Jul 14 '11 at 22:40

wonk0

13,402
1
21
15

Thanks; what's the INNER JOIN query for this one then, that I can try? – avenas8808 Jul 14 '11 at 22:46
SELECT ... FROM buyers INNER JOIN products USING (id) – wonk0 Jul 14 '11 at 22:47

score 0 · Answer 5 · answered Jul 14 '11 at 22:40

0

If you want to ignore the null results then just use the INNER JOIN, you don't need a LEFT JOIN.
Change your query to:

SELECT * FROM buyers JOIN products USING (id)

answered Jul 14 '11 at 22:40

Chandu

81,493
19
133
134

Ignore null results in MySQL JOIN queries

5 Answers5

Linked