2

I am attempting to write a query (mysql maria db) that pulls all rows from my main table (19m records) but also pulls categories / sub categories (in another table -> business_sub_category / categories).

I have written a number of inner joins into my statement to pull the tables but it appears that i have made a mistake. The below query only pulls 500 records. There are at least 7m emails.

Why is my query being limited to only 500 (thats how many get returned from this result set).

Even just giving me an idea of why my query might be limited would be very helpful. I'm new!

SELECT businesses_old.id, businesses_old.name, businesses_old.email, business_sub_category.sub_category_id, sub_categories.id, sub_categories.category_id, categories.id, categories.name
FROM businesses_old
INNER JOIN business_sub_category
ON businesses_old.id = business_sub_category.id
INNER JOIN sub_categories
ON business_sub_category.id = sub_categories.id
INNER JOIN categories
ON sub_categories.category_id = categories.id
WHERE email IS NOT NULL AND email <> '';
3

You need to perform a left outer join or a full outer join. An inner join takes the value that matches both tables and gives you back the results of both tables.

A left outer join (sometimes referred to as a left join) matches the value between both tables. When a value exists in the primary table you are selecting from but does not exist on the table you are joining to, the results from the primary table will be returned as well as any results that were matched from the joining table. If the values do not exist in the column from the table you are joining to, you will not get those results back.

This can be reversed with a right outer join (also referred to as a right join). I try to remember it as the left join refers to the from table, the right join refers to the joined table. It is generally seen as better practice to keep it to a left join so your code is consistent to read. Sometimes it could be hard to refactor and I'd look at right joins then.

A full outer join is combining all rows in both tables and matching them if possible. If the results don't match the from table, those results will be populated with NULLS. The same is true for the other table if it has no matches.

https://www.w3schools.com/sql/sql_join.asp

Try this:

SELECT 
    businesses_old.id, businesses_old.name, businesses_old.email,
    business_sub_category.sub_category_id, sub_categories.id,
    sub_categories.category_id, categories.id, categories.name 
FROM businesses_old 
LEFT JOIN business_sub_category 
ON businesses_old.id = business_sub_category.id 
LEFT JOIN sub_categories 
ON business_sub_category.id = sub_categories.id 
LEFT JOIN categories 
ON sub_categories.category_id = categories.id 
WHERE email IS NOT NULL AND email <> '';

I would also try to alias each column you refer to with the table before it, this keeps your records straight for anyone else trying to read your SQL.

|improve this answer|||||
  • Great explanation. Your suggestion worked perfectly. I love you. – Krunkmaster Apr 9 '18 at 17:06
  • Steady on there old chap! I know that code (and explanations thereof) can be beautiful, but really, there are limits.... :-) I'm giving a +1 for that explanation... Oops, does that mean I'm in love too? – Vérace Apr 10 '18 at 2:54
  • It does, the Shaulinator has also worked his magic on you! – Krunkmaster Apr 11 '18 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.