How can I prevent inner SELECT from returning NULL?

Question

How can I prevent inner SELECT from returning NULL (when matches no rows) and force query to fail.

INSERT INTO tt (t1_id, t2_id) VALUES (
  (SELECT id FROM t1 WHERE ...),
  (SELECT id FROM t2 WHERE ...)
);

Side question: is there better way form this query (t1_id, t2_id are foreign keys, but might be NULL) ?

Answer 1

INSERT INTO tt (t1_id, t2_id) VALUES (
  (SELECT id FROM t1 WHERE ...),
  (SELECT id FROM t2 WHERE ...)
)
WHERE EXISTS (SELECT id FROM t1 WHERE ...)
AND (SELECT id FROM t2 WHERE ...)

It may seem awkward and redundant but any SQL executor worth its salt won't execute each part twice.

Alternatively if t1 and t2 are related somehow:

INSERT INTO tt (t1_id, t2_id)
SELECT t1.id, t2.id
FROM t1
JOIN t2 ON ...
WHERE ...

Answer 2

How about something like:

INSERT INTO tt (t1_id, t2_id)
SELECT t1.id, t2.id FROM t1, t2 WHERE ...

Just make sure the SELECT returns exactly what you want to INSERT - so if it's possible for t1.id and t2.id to be NULL then include the relevant clause in your WHERE condition (... AND t1.id IS NOT NULL AND t2.id IS NOT NULL ...).

You may also prefer to use SELECT DISTINCT if there's a chance of duplicate rows appearing.

Edit: If you need 2 IDs from different rows of the same table:

SELECT t1.id, t2.id FROM some_table AS t1, some_table AS t2
WHERE ...

Answer 3

INSERT INTO tt (t1_id, t2_id) VALUES (
  IFNULL((SELECT id FROM t1 WHERE ...), 0),
  IFNULL((SELECT id FROM t2 WHERE ...), 0)
);

0 is the value you want to return if SELECT returns NULL