Reputation: 151
I have this basic SQL sentence:
SELECT p.s1,pr.s1 FROM `prop` p LEFT OUTER JOIN prom pr ON pr.id = p.prom
My intention is to have a column with the value of pr.s1
if it's not null, and p.s1
in other case. It's very simple, but i don't know how to do it correctly.
Any idea will be very appreciated, thanks.
Upvotes: 0
Views: 34
Reputation: 3008
You can use IFNULL
:
SELECT IFNULL(pr.s1, p.s1) AS s1
FROM `prop` p
LEFT OUTER JOIN prom pr
ON pr.id = p.prom
Upvotes: 2
Reputation: 522824
To round things out, you could also use COALESCE
:
SELECT COALESCE(pr.s1, p.s1)
FROM prop p
LEFT OUTER JOIN prom pr
ON pr.id = p.prom
One nice thing about using COALESCE()
over IFNULL()
is that the former can take more than two parameters. So if, for example, you had two backup columns in case of a NULL
value you could do:
SELECT COALESCE(pr.s1, p.s1, p.someOtherCol)
Upvotes: 2
Reputation: 40491
You can use COALESCE()
:
SELECT p.s1,COALESCE(pr.s1,p.s1)
FROM `prop` p
LEFT OUTER JOIN prom pr
ON pr.id = p.prom
Although you may have two identical columns, so I think you meant :
SELECT COALESCE(pr.s1,p.s1) as s1
FROM ...
Upvotes: 1
Reputation: 133410
You can use ifnull
SELECT p.s1, ifnull(pr.s1, p.s1)
FROM `prop` p
LEFT OUTER JOIN prom pr ON pr.id = p.prom
Upvotes: 1