i want a good way to improve my sql code, i have to use inner join when condition is met. I am currently replicates the code:
@SystemMerge bit
if (@SystemMerge=1)
BEGIN
SELECT
.......
FROM myTable
INNER JOIN table ON table.param1=myTable.param1
INNER JOIN systemTable on systemTable.param2=myTable.param2
END
ELSE
BEGIN
SELECT
.......
FROM myTable
INNER JOIN table ON table.param1=myTable.param1
END
and i would like to do it in a way like this:
@SystemMerge bit
BEGIN
SELECT
.......
FROM myTable
INNER JOIN table ON table.param1=myTable.param1
***//the next 4 lines is not working, but this pseudo of what i want:***
if (@SystemMerge=1)
begin
INNER JOIN systemTable on systemTable.param2=myTable.param2
end
edit: the solution (thanks to @Damien_The_Unbeliever):
LEFT JOIN systemTable ON systemTable.param2=myTable.param2
WHERE
((@SystemMerge=1 AND systemTable.param2 is not null)
OR
(@SystemMerge=0 OR @SystemMerge is null))
To use the WHERE clause to perform the same join as you perform using the INNER JOIN syntax, enter both the join condition and the additional selection condition in the WHERE clause. The tables to be joined are listed in the FROM clause, separated by commas. This query returns the same output as the previous example.
A conditional column join is a fancy way to let us join to a single column and to two (or more) columns in a single query. We can accomplish this by using a case statement in the on clause of our join. A case statement allows us to test multiple conditions (like an if/else if/else) to produce a single value.
When using join or inner join , the on condition is optional. This is different from the ANSI standard and different from almost any other database. The effect is a cross join . Similarly, you can use an on clause with cross join , which also differs from standard SQL.
How to create a join with the ON clause in Oracle? The join condition for the natural join is basically an equijoin of identical column names. ON clause can be used to join columns that have different names. Use the ON clause to specify conditions or specify columns to join.
This should (approxmately) do the same thing:
SELECT
.......
FROM myTable
INNER JOIN table ON table.param1=myTable.param1
LEFT JOIN systemTable on systemTable.param2=myTable.param2 and @SystemMerge = 1
WHERE (@SystemMerge = 0 OR systemTable.NonNullableColumn IS NOT NULL)
Of course, this also means that any other references to columns within systemTable
must be written to expect such columns to be NULL
.
How about dynamic sql?
declare @sel varchar(max)
set @sel = ' SELECT
.......
FROM myTable
INNER JOIN table ON table.param1=myTable.param1
'
if (@SystemMerge=1)
begin
set @sel = @sel+'INNER JOIN systemTable on systemTable.param2=myTable.param2'
end
exec(@sel)
Simple way if I am right -
SELECT
.......
FROM myTable
INNER JOIN table ON table.param1 = myTable.param1
INNER JOIN systemTable on @SystemMerge = 0 or systemTable.param2=myTable.param2
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