Mysql Left Join Not Returning Null for Missing Rows

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'

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, 

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 that shows NULL for missing rows

Move the WHERE logic to the ON clause:

SELECT r.recipid, r.recipname, ru.userid
FROM recip r
LEFT JOIN recipuser ru
    ON r.recipid = ru.recipid AND ru.userid = 2;

The problem with your current query is that the WHERE clause is filtering off the non matching record which you want to appear.

Why MySQL's LEFT JOIN is returning "NULL" records when with WHERE clause?

A left join condition and where condition filter are not both same. Data is filtered by the where clause after the physical join is done. if you look a left join it will normally return every row from your left table, but once you have a where clause, it will filter the output of the join so the result is like an inner join. You will want to focus on the two diagrams on the left side of the image below.

MYSQL left join function results missing rows

When you use a LEFT JOIN, if there's no matching row in table B, those columns will be NULL, so B.date BETWEEN %s AND %s will always be false.

Put the restrictions on B.date in the ON clause, not the WHERE clause: LEFT JOIN sales AS B ON A.item = B.item AND B.date BETWEEN %s AND %s. This is the exception to the general rule that ON clauses should only contain comparisons between the tables being joined.

SQL LEFT JOIN Not returning the correct DATA

If you use a LEFT JOIN and there isn't a match, the right hand table (items) will have NULL as the values of its columns. You therefore can't have items.published = 1 in your WHERE clause and expect those rows to show up because NULL doesn't equal 1.

Assuming I've not misunderstood the question, you'll need to change that to AND (items.published IS NULL OR items.published = 1) - you could probably also get away with just doing AND items.published <> 0. That essentially checks that the items are published if they exist.

---

Related Topics