Why Is My Left Join Not Returning Nulls

Why is my left join not returning nulls?

change join items i... to LEFT join items i... and your query should work as you expect.

EDIT
You can not filter LEFT JOIN tables in the where clause unless you account for nulls, because the left join allows those columns to have a value or be null when no rows matches:

and i.siteid = 132 will throw away any of your rows that have a NULL i.siteid, where none existed. Move this to the ON:

left join items i on ic.itemid = i.itemid and i.siteid = 132

or make the WHERE handle NULLs:

WHERE ... AND (i.siteid = 132 OR i.siteid IS NULL)

EDIT based on OP's edit 3

SET NOCOUNT ON
DECLARE @Categories table (CategoryID int,Title varchar(30))
INSERT @Categories VALUES (1,'Cat AAA')
INSERT @Categories VALUES (2,'Cat BBB')
INSERT @Categories VALUES (3,'Cat CCC')

DECLARE @SubCategories table (SubCategoryID int,CategoryID int,Title varchar(30))
INSERT @SubCategories VALUES (1,1,'SubCat AAA A')
INSERT @SubCategories VALUES (2,1,'SubCat AAA B')
INSERT @SubCategories VALUES (3,1,'SubCat AAA C')
INSERT @SubCategories VALUES (4,2,'SubCat BBB A')

DECLARE @ItemCategories table (ItemCategoryID int, ItemID int, SubCategoryID int, IsActive char(1))
INSERT @ItemCategories VALUES (1,1,2,'Y')
INSERT @ItemCategories VALUES (2,2,2,'Y')
INSERT @ItemCategories VALUES (3,3,2,'Y')
INSERT @ItemCategories VALUES (4,4,2,'Y')
INSERT @ItemCategories VALUES (5,7,2,'Y')

DECLARE @Items table (ItemID int, Title varchar(30), SiteID int)
INSERT @Items VALUES (1,'Item A',111)
INSERT @Items VALUES (2,'Item B',111)
INSERT @Items VALUES (3,'Item C',132)
INSERT @Items VALUES (4,'Item D',111)
INSERT @Items VALUES (5,'Item E',111)
INSERT @Items VALUES (6,'Item F',132)
INSERT @Items VALUES (7,'Item G',132)
SET NOCOUNT OFF

I'm not 100% sure what the OP is after, this will return all info that can be joined when the siteid=132 as given in the question

SELECT
    c.title as categorytitle
        ,s.title as subcategorytitle
        ,i.title as itemtitle
        --,i.itemID, ic.SubCategoryID, s.CategoryID
    FROM @Items                          i
        LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID
        LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID
        LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID
    WHERE i.siteid = 132

OUTPUT:

categorytitle                  subcategorytitle               itemtitle
------------------------------ ------------------------------ ------------------------------
Cat AAA                        SubCat AAA B                   Item C
NULL                           NULL                           Item F
Cat AAA                        SubCat AAA B                   Item G

(3 row(s) affected)

This will list all categories, even if there is no match to the siteid=132

;WITH AllItems AS
(
SELECT
    s.CategoryID, ic.SubCategoryID, ItemCategoryID, i.ItemID
        ,c.title AS categorytitle, s.title as subcategorytitle, i.title as itemtitle
    FROM @Items                          i
        LEFT OUTER JOIN @ItemCategories ic ON i.ItemID=ic.ItemID
        LEFT OUTER JOIN @SubCategories   s ON ic.SubCategoryID=s.SubCategoryID
        LEFT OUTER JOIN @Categories      c ON s.CategoryID=c.CategoryID
    WHERE i.siteid = 132
)
SELECT
    categorytitle, subcategorytitle,itemtitle
    FROM AllItems
UNION
SELECT
    c.Title, s.Title, null
    FROM @Categories                     c
        LEFT OUTER JOIN @SubCategories   s ON c.CategoryID=s.CategoryID
        LEFT OUTER JOIN @ItemCategories ic ON s.SubCategoryID=ic.SubCategoryID
        LEFT OUTER JOIN AllItems         i ON c.CategoryID=i.CategoryID AND  s.SubCategoryID=i.SubCategoryID
    WHERE i.ItemID IS NULL
ORDER BY categorytitle,subcategorytitle

OUTPUT:

categorytitle                  subcategorytitle               itemtitle
------------------------------ ------------------------------ ------------------------------
NULL                           NULL                           Item F
Cat AAA                        SubCat AAA A                   NULL
Cat AAA                        SubCat AAA B                   Item C
Cat AAA                        SubCat AAA B                   Item G
Cat AAA                        SubCat AAA C                   NULL
Cat BBB                        SubCat BBB A                   NULL
Cat CCC                        NULL                           NULL

(7 row(s) affected)

Left join not returning null records

The problem with your attempt is that you start with the records in TableA then LEFT JOIN against TableB. This forces the engine to only display records from TableA, with additional rows/columns from TableB if they match, but not records from TableB that aren't on TableA.

Either you want to reverse the join order:

SELECT 
    *
FROM
    TableB b
    LEFT JOIN TableA a ON 
        a.Field1 = b.Field1 AND
        a.Field2 = b.Field2    
WHERE 
    a.Field1 IS NULL -- records from A table shouldn't exist

Or as RIGHT JOIN

SELECT 
    *
FROM TableA a RIGHT JOIN
     TableB b
     ON a.Field1 = b.Field1 AND
        a.Field2 = b.Field2    
WHERE a.Field1 IS NULL  -- records from A table shouldn't exist 

Or a FULL JOIN if you want records from both displayed, even if no match on the other table (no WHERE clause):

SELECT 
    *
FROM TableA a FULL JOIN
     TableB b
     ON a.Field1 = b.Field1 AND
        a.Field2 = b.Field2    

LEFT JOIN not returning NULL

You complain about your query producing entirely blank rows. Let's see why:

You outer join qry3 to qry2. That means when there is no match for a qry2 record in qry3, then a pseudo qry3 record with all columns set to null gets joined.

In your query you select only fields from qry3, so in an outer join case they are all null. Select qry2.ID and qry2.item instead of qry3.ID and qry3.item to see the values that have no match:

SELECT 
  qry_HersheySAPMaxDate2.ID, 
  qry_HersheySAPMaxDate2.item, 

Postgres LEFT JOIN not returning nulls?

Your item table has 100 rows. You outer join the votes table. Thus you get all item votes, e.g. 10 votes for item 1, 5 votes for item 2, 0 votes for item 3, ... If you used an inner join, you would lose the 0 votes for item 3, because there is no vote for item 3 in the table. Then, every vote is related to a user. For item 3 there is no vote, so while you produce a result for item 3 with no votes (NULL, which COALESCE turns into 0), this result is empty (i.e. the user ID is also NULL of course). That is all the outer join does.

Then you group by user and collect their votes in arrays. Let's say user 1 has 40 votes, user 2 has 30 votes, user 3 has no votes, and user 4 has 20 votes. For this you'd get three result rows (one for each user in the data): one for user 1 with an array of 40 votes, one for user 2 with an array of 30 votes, one for user 4 with an array of 20 votes.

You want the arrays to magically contain 100 votes each. From this I gather that the votes table has a unique key on user and item and you want to select all users with a vote value for each item - a zero value in case there is no vote entry.

For this to happen you must first create all desired rows: users x items. Then outer join the votes.

SELECT u.user_id, ARRAY_AGG(COALESCE(v.value, 0) ORDER BY i.item_id)
FROM users u
CROSS JOIN items i
LEFT JOIN votes v ON v.user_id = u.user_id and v.item_id = i.item_id
GROUP BY u.user_id
ORDER BY u.user_id;

If you want to restrict this to users with at least one vote, then either replace FROM users u with FROM (SELECT DISTINCT user_id FROM votes) u or add HAVING COUNT(v.item_id) > 0.

Left Join Not Returning Expected Results

You need a LEFT join of AllServices to VendorServices and a case expression to get the column provided:

select s.id,
       case when v.serviceid is null then 0 else 1 end provided
from AllServices s left join VendorServices v
on v.serviceid = s.id and v.vendorid = @VendorID

See the demo.

Unsure why LEFT JOIN is not returning data

Since you're using an outer join you need to move the conditions a little:

SELECT *
FROM #t1 AS a
LEFT JOIN #t1 AS b ON a.col1 = b.col1 AND b.batch = 2
WHERE a.batch = 1 AND b.batch IS NULL

MySQL Left Join not returning null values for joined table

You should not specify rYear in a WHERE clause. Those limit your results after the join. You should specify rYear in an ON clause to get back records with NULL from table B.

SELECT * from A
left join B 
on A.sid = B.sid 
AND (rYear = 2011 or rYear is null)
where (rCode = 1 Or rCode = 2 Or rCode = 3 Or rCode = 5)

MySQL LEFT JOIN not returning NULL for missing rows

You need to put your others conditions in ON clause instead of Where Clause like below -

SELECT
  users.ID,
  users.user_email,
  _first.meta_value as firstName,
  _second.meta_value as lastName,
  users.display_name,
  _gender.meta_value as gender,
  _age.meta_value as age,
  _nationality.meta_value as nationality

FROM kp_users

LEFT JOIN usermeta as _first ON kp_users.id = _first.user_id and _first.meta_key = 'first_name'
LEFT JOIN usermeta as _second ON kp_users.id = _second.user_id and _second.meta_key = 'last_name'
LEFT JOIN usermeta as _gender ON kp_users.id = _gender.user_id and _gender.meta_key = '_user_demographics_gender'
LEFT JOIN usermeta as _age ON kp_users.id = _age.user_id and _age.meta_key = '_user_demographics_age'
LEFT JOIN usermeta as _nationality ON kp_users.id = _nationality.user_id and _nationality.meta_key = '_user_demographics_nationality'

---

Related Topics