SQL - How to join two tables but return only matched rows or all rows if other table is empty

One of the common SQL tasks is to return matched data only or all data if other table is empty. In other way it should work like INNER JOIN when both table have data or LEFT JOIN when one table is empty. Real world scenario could be like this:
- Return all properties if filter table is empty. If filter table has any data then return properties that matches to filter.

This is how we can create such query:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

CREATE TABLE MyData(Id INT, Something VARCHAR(10), OwnerId INT); CREATE TABLE OwnerFilter(OwnerId INT);   INSERT INTO MyData VALUES(1, 'AAAA', 1); INSERT INTO MyData VALUES(2, 'BBBB', 1); INSERT INTO MyData VALUES(3, 'CCCC', 1); INSERT INTO MyData VALUES(4, 'AASS', 2);   -- Other table is empty. Query returns all rows. SELECT * FROM (SELECT NULL AS Gr) AS Dummy LEFT JOIN OwnerFilter F ON (1 = 1) JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);   -- Other table has data. Query returns only matched rows. INSERT INTO OwnerFilter VALUES(2);   SELECT * FROM (SELECT NULL AS Gr) AS Dummy LEFT JOIN OwnerFilter F ON (1 = 1) JOIN MyData D ON (F.OwnerId IS NULL OR D.OwnerId = F.OwnerId);

Here you can try it on fiddler: http://sqlfiddle.com/#!6/0f9d9/7