Menu
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
375
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.
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.
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.
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
