Odtnhj

I think this maybe a simple question, but I kind of stuck for a non-SQL Developer.

Example my select statement is like this.

Select ID, Year
From Table A
Where Year = (Select Year from table B)

If year from table B is null, I want to return all rows from table A else filter by value from table B.

Now what happen is when table B is null there is no rows return from table A.

Edited: There is only one row in Table B, and Value year from table B can be manually control to be null or not null.

Don't use as Where Year = (Select Year from table B)
Because if TableB has more than one records, the above query will return an error.

Use this query instead;

SELECT TA.ID, TA.Year
FROM TableA TA
LEFT JOIN TableB TB ON TB.Year = TA.Year

Updated:
According to the update you made to the question, the query will be;

SELECT TA.ID, TA.Year
FROM TableA TA
WHERE TA.Year= (Select Year from table B)
OR (Select Year from table B) IS NULL

It's a bit odd to have a specification that a table will always have exactly one row. It's not invalid, but it's unusual.

If table B were empty, if it contained zero rows, then what should the query return? An error? An empty set? or all rows from A?

If table B contained more than one row, what should be returned? An error? An empty set? Rows from A that matched one of the non-NULL values of year from B? All rows from A?

Most often, its the edge cases and corner cases that spell out the specification... we expect the cases to be tested, we need to know what to test for... and then we can develop a query.

There are several query patterns that would satisfy the spec; the big differences are going to be those edge cases... table B is empty, or year column in table A is NULL.

I'd probably tend to go with something like this:

 SELECT a.id
 , a.year
 FROM ( SELECT MAX(b.year) AS year
 FROM table_b b
 ) v
 JOIN table_a a
 ON a.year <=> IFNULL(v.year,a.year)

SQL Fiddle demonstration http://sqlfiddle.com/#!9/44c277/1

If the question is actually about using the WHERE clause, then feel free to replace the keyword ON with WHERE.

DOH! Reviewing the question, it's asking about SQL Server, not MySQL. The syntax above is for MySQL. For SQL Server, then something like this:

 SELECT a.id
 , a.year
 FROM ( SELECT MAX(b.year) AS year
 FROM table_b b
 ) v
 JOIN table_a a
 ON COALESCE(a.year,-1) = COALESCE(v.year,a.year,-1)

We are using -1 as a special placeholder value, as a replacement for NULL, so the equality comparison will be satisfied. Without a special placeholder value, we'll have to incorporate some IS NULL conditional tests.

Assuming your inner query return single row only.

You can do this like.

SELECT ID, Year
FROM TableA
WHERE Year = 
 (
 SELECT Year 
 FROM TableB
 )
OR Year IS NULL

As far as your line "If year from table B is null, I want to return all rows from table A else filter by value from table B." is concerns, use the following query

SELECT ID, Year
FROM TableA
WHERE NVL(Year, '0') = (CASE WHEN (SELECT Year FROM TableB) IS NOT NULL THEN (SELECT Year FROM TableB) ELSE NVL(Year, '0') END)

I did not test this query, but hopes that you will get the point here.

use ISNULL and set the NULL as 0 like

Select ID, Year
From Table A
Where Year = (Select ISNULL(Year,0) from table B)

Hope this works!