SQL Case statement to check for NULLS and Non-existent records_问答_开发者

I am doing a join between two tables and want to select the columns based on whether they have a record or not. I'm trying to avoid having multiple of the same field and am trying to condense them into single columns. Something like:

Select 
    id = (CASE WHEN a.id IS NULL THEN b.id ELSE a.id END),
    name = (CASE WHEN a.name IS NULL THEN b.name ELSE a.name END)
From Table1 a
Left Join Table2 b
On a.id = b.id
Where a.id = @id

I'd like id to populate from Table1 if a record exists, but if not pull from Table2. The previous code returns no records because there are no NULL values in Table1 so my question is how do I run a check to see if any records even exist? Also if anyone knows of a better way to accomplish what I am trying to do I appreciate guidance and constructive criticism.

EDIT

It looks like COALESCE will work for what I'm trying to accom开发者_如何学JAVAplish. I'd like to give a little more info on exactly what I am working with and get some advice on whether I am using the best method.

I have a bloated table Table2 and it is in production. I'm working on building new web applications for this system but can't justify a complete database redesign so I am trying to do one "on the fly". I've created a new table Table1 and I am writing stored procedures for the following methods Get(Select), Set(Update), Add(Insert), Remove(Delete). This way, to my code, it will seem that I am working with a single table that is not bloated. My code will simply call one of the SP methods and then the stored procedure will handle the data between the old table and the new. I am currently working on the Get method and I need to check the old table Table2 for a record if it doesn't exist in Table1. Thanks to the suggestions here my query currently looks like this:

Select
    id = coalesce(a.id, b.student_number),
    first_name = coalesce(a.first_name, b.first_name),
    last_name = coalesce(a.last_name, b.last_name),
    //etc
From Table1 a
Full Outer Join Table2 b
On a.id = b.student_number
Where (a.id = @id Or b.student_number = @id)

This works for what I'm trying to accomplish, I'd like to throw it out there to the experienced crowd for any tips or suggestions if there are better or more correct ways to go about this.

Thanks

I suspect your problem may come from doing a left join. Try again using a full outer join, like this:

Select 
    id = coalesce(a.id, b.id),
    name = coalesce(a.name, b.name)
From Table1 a
full outer Join Table2 b
On a.id = b.id
Where a.id = @id

Select id = coalesce(a.id, b.id), 
    name = coalesce(a.name, b.name)
From Table2 b 
Left Join Table1 a On a.id = b.id 
Where b.id = @id

You may need to use ISNULL or CASE instead of COALESCE depending on your database platform.

First, you don't need a case statement for that:

Select ISNULL(a.id,b.id) AS id, ISNULL(a.name,b.name) AS name,
From Table1 a
Left Join Table2 b
On a.id = b.id
Where a.id = @id

Second, if I get it right, the id field can contain nulls, and in that case you are screwed. I mean, the ID is a unique value that identify a row, if it can be null, you can't identify that row.

But if what you want is getting records from Table1 and Table2 and avoid duplicates, a simple UNION will work fine, since it discards duplicates:

select id, name
from Table1
where id = @id

union

select id, name
from Table2
where id = @id

You could do something like:

select id, name from Table1 a where a.id not in (select id from Table2) UNION select id, name from Table2 b

This would give you all the records from table1 that didn't have a corresponding match in table2 plus all of table2's records. The union would then combine the results.

In your first CASE statement, a.id and b.id will always be same value, except for instances in which a.id has a value and b.id generates a NULL value because of the LEFT JOIN. There will never be a row in the result set with a NULL a.id value and a non-NULL b.id value. You could just use a.id for this column.

For the second CASE statement, you may find the name column in either or both tables with a value (and, of course, the values may be different). You said you want to "condense" the these column values; the SQL function for that is COALESCE: