SQL IN Clause - Possibility of Empty Set

Question

I am trying something like the following

SELECT * FROM tbl WHERE col IN (SELECT col1 FROM tbl1)

However, when SELECT col1 FROM tbl1 returns no results, I want to display all results from the tbl

Something like this

IF(SELECT col1 FROM tbl1)
SELECT * FROM tbl WHERE col IN (SELECT col1 FROM tbl1)
ELSE
SELECT * FROM tbl

Answer 1

You can add NOT EXISTS() statement . Though this solution won't be efficient on large sets of data, so for more efficient method, you can use PL/SQL

SELECT * FROM tbl 
WHERE col IN(SELECT col1 FROM tbl1)
   OR NOT EXISTS(SELECT 1 FROM tbl1)