I have a subject table that has subject_id column. In the table I have one row that has subject_id null other than that subject_id has a distinct value.

I am looking for single query I can fetch the data on basis of subject_id.

Select * from subject where subject_id = x;

If there is no data found w.r.t x than it should return the row with subject_id = null

CodePudding user response：

In general this is a terrible pattern for tables. NULL as a primary key value is only going to cause you pain and suffering in the long run. Using a NULL-keyed row as a default for when your query matches no other rows will lead to strange behavior somewhere unexpected.

The simplest way would be to simply include the NULL row as the last row of any query and then only fetch the first row. But that only works when your query can only return at most one valid result.

select *
from subject
where subject_id = ? or subject_id is null
order by subject_id asc nulls last

Possibly the biggest problem with a NULL PK for your default/placeholder row in subject is that anywhere else you have a NULL subject_id cannot simply join to that row using x.subject_id = y.subject_id.

If you really need such a row, I suggest using -1 instead of NULL as the "not exists" value. It will make your life much easier across the board, especially if you need to join to it.