Why does this query only return results with non-empty child tables?

Question

This is a simplified version of a query we are running where we need to find all rows in the main parent table where the child rows match. The query below returns no results when one of the child tables is empty.

The main table has two child tables:

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 it is 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 doesn't match the WHERE clause, then the SELECT does return 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 this looks to be something general with databases.

Why is this behaving this way?

What can I do to fix it?

I could change to UNION separate SELECTs, but that's much more verbose, and plus, we're generating the SQL dynamically and I'd rather not have to change our code.

Another fix is just to add a dumb row to the database, but that's messy.

PS The main table is a session table in an asset management system that records the assets that clients look up. There are different types of lookups and each kind gets a separate child table, plus there is an attributes child table for key/value pairs for the session that can be searched on.

Answer 1

When child2 has no rows, the query returns no rows because of the inner join to the child2 table. If you inner join to a table that has no rows, you will never get any results - you would have to outer join to child2 instead if you want to get results when child2 is empty.

When child2 does have a row, the reason your query returns results is because of the where clause:

WHERE
  child1.name = 'child1' OR
  child2.name = 'DOES NOT EXIST';

The inner join says there has to be something in child2 with a matching ID, but the where clause has an OR in it, so you will get results just because child1.name = 'child1'. After that, the database doesn't have to bother looking at the child2 tables.

To fix it:

I have hunch that you only want to return the child rows when some condition is met. You should outer-join to both of them, and perhaps also move your extra conditions from the where clause to the join clause, like this:

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'

• The outer joins mean you have the chance of getting results even if one table is empty.

• Moving the extra conditions (child1.name = ...) from the WHERE clause to the outer join means you only get the tables info if the condition is true. (I think this might be what you are trying to do, but maybe not, in which case leave the conditions in the WHERE clause where you originally had them.)

Answer 2

It's returning nothing because you are using inner joins.

Change your inner joins to left joins

Answer 3

When you say INNER JOIN you are asking the query to return rows that have results on both sides of the join. This means any rows that do not have matching child rows will be removed.

It sounds like what you are looking for is LEFT JOIN which will include all rows on the left hand side of the join (main) even if they do not have a matching entry on the right hand side (child1, child2).

This is standard behaviour and a very common problem for people not familiar with SQL. Wikipedia has all the details, otherwise a quick Google search brings up plenty of results.