Blending Left and Right? What for?

Question

Performing some refactoring in some outdated code that I found in the project. This is for MSSQL. The fact is, I don’t understand why we use mixed left and right connections and match some connection conditions.

My question is this: does this create hidden internal connections in some places and imply a complete union in others?

I’m from school that almost everything can be written using only the left (and internal / full) or only the right (and internal / full), but this is because I like to keep things as possible.

As an aside, we are converting all this to work with oracle databases, so maybe there are some optimization rules that work differently with Ora?

For example, here is the FROM part of one of the queries:

        FROM    Table1
        RIGHT OUTER JOIN Table2
            ON Table1.T2FK = Table2.T2PK
        LEFT OUTER JOIN Table3
        RIGHT OUTER JOIN Table4
        LEFT OUTER JOIN Table5
            ON Table4.T3FK = Table5.T3FK
               AND Table4.T2FK = Table5.T2FK
        LEFT OUTER JOIN Table6
        RIGHT OUTER JOIN Table7
            ON Table6.T6PK = Table7.T6FK
        LEFT OUTER JOIN Table8
        RIGHT OUTER JOIN Table9
            ON Table8.T8PK= Table9.T8FK
            ON Table7.T9FK= Table9.T9PK
            ON Table4.T7FK= Table7.T7PK
            ON Table3.T3PK= Table4.T3PK
        RIGHT OUTER JOIN ( SELECT   *
                           FROM     TableA
                           WHERE    ( TableA.PK = @PK )
                                    AND ( TableA.Date BETWEEN @StartDate
                                                                    AND     @EndDate )
                         ) Table10
            ON Table4.T4PK= Table10.T4FK
            ON Table2.T2PK = Table4.T2PK

Answer 1

One thing I would like to do is make sure that you know what results you expect before messing with this. I would not like to “correct” it and return different results. Although honestly, with a query that is poorly designed, I'm not sure if you are really getting the right results right now.

For me, this is similar to what someone did over time, perhaps even initially, starting with internal joins, realizing that they will not work and change to external joins, but do not want to worry about changing the order referenced by the tables in the request.

, ON , , , . ON 4 3 9 , . , . , (, , 2), .

Answer 2

, LEFT-: , , . , FULL- - , , , SQL Server: , , , .

Oracle - , , . Oracle , , , , Oracle ( , )/ . , SQL Server , , , Oracle , , . - , Oracle, ().

. , , - , .

Answer 3

LEFT RIGHT join - .

LEFT JOIN RIGHT JOIN .

Pre- 9i Oracle :

WHERE  table1.col(+) = table2.col

(+), , LEFT RIGHT :

WHERE  table1.col = table2.col(+)

MySQL FULL OUTER JOIN, .

:

SELECT  *
FROM    table1
LEFT JOIN
        table2
ON      table1.col = table2.col
UNION ALL
SELECT  *
FROM    table1
RIGHT JOIN
        table2
ON      table1.col = table2.col
WHERE   table1.col IS NULL

JOIN LEFT RIGHT, .

, SQL Server Hash Left Semi Join Hash Right Semi Join - .

:

SELECT  *
FROM    table1
WHERE   table1.col IN
        (
        SELECT  col
        FROM    table2
        )

Hash Match (Left Semi Join) table1 - (, ).

Hash Match (Right Semi Join) table2 - .

Answer 4

- , LEFT RIGHT - , , LEFT- RIGHT- . FULL OUTERs LEFT/RIGHT, , n'est pas?

Answer 5

LEFT OUTER JOIN RIGHT OUTER JOIN. , , , , . , RIGHT OUTER JOIN , .

, SQL- LEFT OUTER JOIN s, , , FROM -.

, RIGHT OUTER JOIN , - ( ), INNER JOIN OUTER JOIN. , JOIN FROM, LEFT OUTER JOIN, RIGHT OUTER JOIN, . . LEFT OUTER JOIN, LEFT - RIGHT OUTER JOIN s, . .

, SQL-, Access, FULL OUTER JOIN, - UNION a LEFT OUTER JOIN RIGHT OUTER JOIN .

Answer 6

, SQL . ON JOIN, , SQL.

, LEFT JOINS ( ), using JOIN. , .

​​ ?

, , , ON , , RIGHT OUTER JOIN Table4, , ON Table4.T7FK= Table7.T7PK. , , WHERE, WHERE Table3.T3PK is not null.

, , .

Answer 7

, , , , , , , Query Designer SQL Management Studio. ON, , . , , , , , .