Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MYSQL INNER JOIN with possible empty fields

Ok, I have a problem with joining 2 tables (with INNER JOIN). First table containts categories list (ecc. for photo albums, or blog posts) while the second table contains "the data"

I tried like this:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
INNER JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

This gives me fair result, joining my tables nicely, just as I want them to with one crucial exeption.

If table "photos" doesn't have a row which contains "photos_gal_id" (for example "2"), than it will NOT return any category (galeries_id, galeries_title) for that category even if it does egzist in galeries table.

It is logical because of:

 ON galeries.galeries_id=photos.photos_gal_id

Now I need to adopt this part to show me even thoes which doesn't have a related row in the second table

The result I'm trying to get is:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2                                     
     3               etata               5            test.jpg

I hope I explained it good enough :) Thank you..

like image 928
uollaa Avatar asked Jul 30 '13 14:07

uollaa


People also ask

Can inner join have NULL values?

When using left join, right join, full (outer) join, it is possible to return NULL value, while (inner) join, cross join will not return NUll value.

What happens if you use inner join with no conditions?

We can use 'cross join' without on condition. Cross join gives the result in cartesian product form. For instance, if in one table there are 3 records and another table has 2 records, then the first record will match with all the second table records. Then, the same process will be repeated for second record and so on.

Does inner join ignore nulls?

Columns containing NULL do not match any values when you are creating an inner join and are therefore excluded from the result set.

Can you inner join without on?

If you use INNER JOIN without the ON clause (or if you use comma without a WHERE clause), the result is the same as using CROSS JOIN : a Cartesian product (every row of o1 paired with every row of o2 ).


2 Answers

To retain the rows from galeries with no matching ID in photos, you will need to join photos on galeries with LEFT JOIN instead of INNER JOIN:

SELECT galeries_id, galeries_title, 
       photos.photos_id, photos.photos_gal_id, photos.photos_link 
FROM galeries 
LEFT JOIN photos 
ON galeries.galeries_id=photos.photos_gal_id 
GROUP BY photos_gal_id

 

This will give you:

galeries_id      galeries_title       photos_id       photos_link
     1               blabla              3            test.jpg
     2               bla bla2            NULL         NULL                
     3               etata               5            test.jpg

 

And if you wish to replace NULL with an empty string, you can use:

SELECT
  IFNULL(photos.photos_id, ''),
  IFNULL(photos.photos_link, '')
like image 171
Mark Avatar answered Oct 08 '22 11:10

Mark


Wouldn't it just be a

LEFT JOIN photos

Because you want the records reguardless of wether or not they are filled?

like image 30
Jacob Goulden Avatar answered Oct 08 '22 11:10

Jacob Goulden