TSQL how to avoid if conditions?
I am based on programming programming, ending up writing TSQL with my new job. My mind is still thinking about writing conditional queries. How to avoid the next unconditional request.
DECLARE @NumAddress INT
SELECT
@NumAddress = COUNT(*)
FROM Address
WHERE UserID = 1001
IF @NumAddress > 0
BEGIN
SELECT
u.FullName, a.Address AS Address
FROM
Users u
JOIN Address a ON a.UserID = u.UserID
WHERE
u.UserId = 1000
END
ELSE
BEGIN
SELECT
u.FullName, NULL AS Address
FROM
Users u
WHERE
u.UserId = 1000
END
NOTE. My sample request is a simplified example of my actual request. So please ignore this and provide me with an example, as I could avoid IF conditions like this. Thanks in advance.
In this particular case, you are better off using left
join :
select
u.FullName,
a.Address
from
users u
left join address a on
a.userid = u.userid
where
u.userid = 1000
This returns everything null
for the columns address
if no match is found.
However, to answer your question more generally, you can use case
statement in your query to avoid having to eat a whole whoopla:
select
u.fullname,
case
when (select count(*) from address where userid = u.userid) > 0 then 1
else 0
end as AddressSupplied
from
users u
where
userid = 1000
case
is a statement switch
in SQL, so you can do this:
case col_name
when 'Val1' then 'Yup'
when 'Val2' then 'Well...'
when 'Val3' then 'Nope.'
else 'What now?'
end
This will check the column col_name
in every row and if it is one of the given values it will return the correct one then
. So the sample query and result set are:
select
col_name,
case col_name
when 'Val1' then 'Yup'
when 'Val2' then 'Well...'
when 'Val3' then 'Nope.'
else 'What now?'
end as some_col
from
tableA
--------------------
col_name some_val
--------------------
Val1 Yup
Val2 Well...
Val1 Yup
Val4 What now?
Val3 Nope.
This can also work in a sentence where
, which is great for semi-conditional queries:
where
userid = case when @somevar > 0 then 1000 else 1001 end
source to share
I'm guessing the difference between 1000/1001 is a typo. Outer join should solve your problem. If there are no addresses, you will still get a "FullName" with a zero address.
SELECT
u.FullName, a.Address AS Address
FROM
Users u
LEFT OUTER JOIN Address a ON a.UserID = u.UserID
WHERE
u.UserId = 1000
source to share
Of course, left join is the correct answer for this question, by the way, in TSQL you can use left join in a simpler way using = *, in your case
SELECT u.FullName, a.Address FROM Users u, Address a WHERE u.UserId = * a.UserId and u.UserId = 1000
People are fixing me: this syntax is depressive in MSSQL (I think) and should NEVER, NEVER, NEVER be used in production projects :)
BTW, FYI you can use subselect here, but it's a less recomeded way
SELECT u.FullName, (select a.Address FROM Address a where a.UserId = u.UserId) FROM Users u WHERE u.UserId = 1000
source to share