Why does this query only return results with non-empty child tables?
This is a simplified version of the query being executed where we need to find all the rows in the main parent table where the child rows match. The below query returns no results when one of the child tables is empty.
There are two child tables in the main table:
CREATE TABLE main (id INT PRIMARY KEY, name VARCHAR(8));
CREATE TABLE child1(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child1 add constraint fk_child1_main foreign key (main_id) references main (id);
CREATE TABLE child2(id INT PRIMARY KEY, main_id int, name VARCHAR(8));
ALTER TABLE child2 add constraint fk_child2_main foreign key (main_id) references main (id);
INSERT INTO main (id, name) VALUES (1, 'main');
INSERT INTO child1 (id, main_id, name) VALUES (2, 1, 'child1');
There are no rows in child2, and the following query returns no rows when empty:
SELECT
main.*
FROM
main
INNER JOIN
child1
ON
main.id = child1.main_id
INNER JOIN
child2
ON
main.id = child2.main_id
WHERE
child1.name = 'child1' OR
child2.name = 'DOES NOT EXIST';
If a row is added to child2, even if it does not match the WHERE clause, then SELECT returns the row in the main table.
INSERT INTO child2 (id, main_id, name) VALUES (4, 1, 'child2');
I've tested this on Derby and SQLite, so it looks like something in common with databases.
Why do they do this?
What can I do to fix this?
I could switch to separate SELECTs for UNIONs, but this is much more verbose and furthermore we are generating SQL dynamically and I wouldn't have to change the code.
Another solution is to just add a vague row to the database, but this is messy.
PS The master table is a session table in the asset management system that records the assets that customers are looking for. There are different types of lookups, and each view gets a separate child table, plus there is a table of child attributes for key / value pairs for the session that can be searched.
a source to share
When child2 has no rows, the query returns no rows due to an internal join to the child2 table. If you join a table that has no rows, you will never get any results - instead, you have to use an outer join with child2 if you want to get results when child2 is empty.
When child2 has a string, the reason your query is returning results is because of the where clause:
WHERE child1.name = 'child1' OR child2.name = 'DOES NOT EXIST';
The inner join says there must be something in child2 with a matching ID, but the where clause has an OR in it, so you only get results because child1.name = 'child1'. After that, the database doesn't have to worry about looking at child2 tables.
To fix this:
I have a suspicion that you only want to return child rows when some condition is met. You should do an outer join on both of them, and perhaps move additional conditions from the where clause to the join clause as well, e.g .:
SELECT main. * FROM main LEFT OUTER JOIN child1 ON main.id = child1.main_id AND child1.name = 'child1' LEFT OUTER JOIN child2 ON main.id = child2.main_id AND child2.name = 'whatever'
-
Outer joins means you have a chance of getting results even if one table is empty.
-
Moving additional conditions (child1.name = ...) from the WHERE clause to the outer join means that you only get information about the tables if the condition is true. (I think this might be what you are trying to do, but it might not, in which case leave the conditions in the WHERE clause where you originally were.)
a source to share
When you say INNER JOIN, you are asking to return rows that have results on both sides of the join. This means that any rows that do not have matching child rows will be removed.
It looks like you are looking for a LEFT JOIN that will include all lines on the left side of the join (main), even if they don't have a matching entry on the right side (child1, child2).
This is standard behavior and a very common problem for people new to SQL. Wikipedia has all the details, otherwise a quick Google search brings up a lot of results.
a source to share