SQL - Not retrieving records from another table when they don't exist

Question

I have this Table (tblExample):

ID   U_ID   Title   Desc    Cat       SubCat     Country     Date
----------------------------------------------------------------------------
1    2      Title1  Desc1   Orange    Blue       England     12/05/2015
2    3      Title2  Desc2   Orange    Blue       England     12/05/2015                    
3    2      Title3  Desc2   Orange    Blue       England     12/05/2015

I then have another table (tblRating):

ID   U_ID   rating
------------------
1    2      4
2    2      2                      
3    2      4  

If I do an SQL statement like:

select e.*, r.* 
from tblExample e
inner join tblRating r on e.U_ID = r.U_ID

The result I get is just the details of U_ID = 2, but I want to still show U_ID = 3, this is the user that has no record in tblRating. How can I do this? I tried left join but did not work.

Answer 1

Replace the inner join with a left join. This will give you all the relevant rows from tblExample and tblRating or nulls where tblRating does have a matching rows:

SELECT    e.*, r.* 
FROM      tblExample e 
LEFT JOIN tblRating r ON e.U_ID = r.U_ID

Answer 2

This is because of Join issue, when you perform INNER JOIN only the matching records from both table returns.

If you want to get all the records from first table and only the matching records from the second table then you need to perform LEFT JOIN.

select 
    first.*, 
    second.* 
from tblExample first
    LEFT JOIN tblRating second on first.U_ID = second.U_ID

For the second table it will return NULL in case of not matching conditions.

You can also refer this question about difference between JOINS,