Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

USING IF WITH OUTER APPLY IN MSSQL

I want to write IF conditition in OUTER APPLY. I need to return more than one column so i don't want to write subqueries

SELECT name FROM v_users 
OUTER APPLY
(
    IF CAST(reg_date AS DATE)<CAST('03/25/2017' AS DATE)
   SELECT gender,email,age,class FROM tb_register_old WHERE id=v_users.user_id;
ELSE 
   SELECT gender,email,age,class FROM tb_register_new WHERE id=v_users.user_id;
)s
like image 375
Axmed Avatar asked Mar 31 '17 08:03

Axmed


People also ask

Can I use (+) for outer join in SQL Server?

The plus sign is Oracle syntax for an outer join. There isn't a minus operator for joins. An outer join means return all rows from one table. Also return the rows from the outer joined where there's a match on the join key.

How Outer apply works in SQL Server?

The OUTER APPLY operator returns all the rows from the left table expression irrespective of its match with the right table expression. For those rows for which there are no corresponding matches in the right table expression, it contains NULL values in columns of the right table expression.

What is difference between cross apply and outer?

Thus, the CROSS APPLY is similar to an INNER JOIN, or, more precisely, like a CROSS JOIN with a correlated sub-query with an implicit join condition of 1=1. The OUTER APPLY operator returns all the rows from the left table expression regardless of its match with the right table expression.

Why use outer apply instead of left join?

OUTER APPLY resembles LEFT JOIN, but has an ability to join table-evaluated functions with SQL Tables. OUTER APPLY's final output contains all records from the left-side table or table-evaluated function, even if they don't match with the records in the right-side table or table-valued function.


1 Answers

Maybe this?

SELECT name FROM v_users 
OUTER APPLY
(
 SELECT gender,email,age,class 
 FROM tb_register_old 
 WHERE id=v_users.user_id and CAST(reg_date AS DATE)<CAST('03/25/2017' AS DATE)

 UNION ALL  

 SELECT gender,email,age,class 
 FROM tb_register_new 
 WHERE id=v_users.user_id and CAST(reg_date AS DATE) >= CAST('03/25/2017' AS DATE)
)s
like image 160
TriV Avatar answered Oct 14 '22 06:10

TriV