TSQL, how to avoid if conditions?

Question

Hello SQL Gurus, I am from procedure programming background, end up writing TSQL recently with my new job. My mindset still thinking about writing queries with if conditions. How to avoid following query without if condition.

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 query is a simplified example of my actual query. So please ignore this and provide me an example, so that how could I avoid IF conditions like this one. Thanks in advance.

Answer 1

In this specific case, you're better off using a left join:

select
    u.FullName,
    a.Address
from
    users u
    left join address a on
        a.userid = u.userid
where
    u.userid = 1000

This will return all null for the address columns if no match is found.

However, to answer your question in a more general sense, you can use a case statement within your query to avoid having to have 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 switch statement 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 on each row, and if it's one of the values specified, it will return the correct then. Therefore, a sample query and result set is:

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 the where clause, which is awfully handy for semi-conditional queries:

where
    userid = case when @somevar > 0 then 1000 else 1001 end

Answer 2

you could possibly use a left join

SELECT 
   u.FullName, a.Address AS Address
FROM 
   Users u
   LEFT JOIN Address a ON a.UserID = u.UserID
WHERE 
   u.UserId = 1000

Answer 3

I'm assuming the difference between 1000/1001 is a typo. A an outer join should solve your problem. If there are no addresses, you still get the 'FullName` with a null 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

Answer 4

An outer join will do the trick.

SELECT u.FullName, a.Address
FROM Users u
LEFT OUTER JOIN Address a ON a.UserId = u.UserId
WHERE UserId = 1000

Answer 5

Of course left join is right answer for this question, btw in TSQL you can use left join in more simple way, using =* , in your case

SELECT u.FullName, a.Address FROM Users u, Address a 
WHERE u.UserId =* a.UserId and u.UserId = 1000

Peoples correct me: This syntax depredecated in MSSQL (i'm think) and should be NEVER, NEVER, NEVER be used in production pjects :)

BTW, FYI you can use subselect here but it less recomeded way

SELECT u.FullName, (select a.Address FROM Address a where a.UserId = u.UserId) 
FROM Users u
WHERE u.UserId = 1000