MySQL – Can Inner Join Statement Limit Query Results?

MySQL

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 <> '';

Best Answer

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.

Related Solutions

Thesql inner join producing more results than it should

Why are you using two syntaxes, old fashioned verbs, roles and then an explicit inner join? Makes the query very hard to read and since you don't have any where clause that defines the relationship between verbs and roles (this is one of the biggest problems with using old fashioned syntax), you essentially have the cartesian product of those first two tables, and this probably explains your row counts.

You need to define the join columns between verbs and roles and, preferably, put that into an ON clause with a proper INNER JOIN. E.g.:

SELECT r.name, v.name, p.enabled
FROM verbs AS v
INNER JOIN privileges AS p
  ON v.id = p.verb_id
INNER JOIN roles AS r
  ON p.role_id = r.id;

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

Have you tried piling more data and benchmarking it? 100K rows is inconsequential. Try 250M or 500M like you're expecting you'll need to handle and see where the bottlenecks are.

An RDBMS can do a lot of things if you pay careful attention to the limitations and try and work with the strengths of the system. They're exceptionally good at some things, and terrible at others, so you will need to experiment to be sure it's the right fit.

For some batch processing jobs, you really cannot beat flat files, loading the data into RAM, smashing it around using a series of loops and temporary variables, and dumping out the results. MySQL will never, ever be able to match that sort of speed, but if tuned properly and used correctly it can come within an order of magnitude.

What you'll want to do is investigate how your data can be partitioned. Do you have one big set of data with too much in the way of cross-links to be able to split it up, or are there natural places to partition it? If you can partition it you won't have one table with a whole pile of rows, but potentially many significantly smaller ones. Smaller tables, with much smaller indexes, tend to perform better.

From a hardware perspective, you'll need to test to see how your platform performs. Sometimes memory is essential. Other times it's disk I/O. It really depends on what you're doing with the data. You'll need to pay close attention to your CPU usage and look for high levels of IO wait to know where the problem lies.

Whenever possible, split your data across multiple systems. You can use MySQL Cluster if you're feeling brave, or simply spin up many independent instances of MySQL where each stores an arbitrary portion of the complete data set using some partitioning scheme that makes sense.

Best Answer

Related Solutions

Thesql inner join producing more results than it should

Mysql – Use MySQL to regularly do multi-way joins on 100+ GB tables

Related Question