Sql Server Left Join with 'Or' Operator

SQL Server Left Join With 'Or' Operator

Here is what I did in the end, which got the execution time down from 52 secs to 4 secs.

SELECT * 
FROM (
    SELECT tpl.*, a.MidParentAId as 'MidParentId', 1 as 'IsMidParentA' 
    FROM TopLevelParent tpl 
    INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentID
UNION
    SELECT tpl.*, b.MidParentBId as 'MidParentId', 0 as 'IsMidParentA'  
    FROM TopLevelParent tpl 
    INNER JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID
UNION
    SELECT tpl.*, 0 as 'MidParentId', 0 as 'IsMidParentA'  
    FROM TopLevelParent tpl 
    WHERE tpl.TopLevelParentID NOT IN (
       SELECT pa.TopLevelParentID 
       FROM TopLevelParent tpl
       INNER JOIN MidParentA  a ON a.TopLevelParentId = tpl.TopLevelParentID
    UNION
       SELECT pa.TopLevelParentID 
       FROM TopLevelParent tpl
       INNER JOIN MidParentB b ON h.TopLevelParentId = tpl.TopLevelParentID
    )
) tpl
LEFT JOIN MidParentA a ON a.TopLevelParentId = tpl.TopLevelParentID
LEFT JOIN MidParentB b ON b.TopLevelParentId = tpl.TopLevelParentID
LEFT JOIN 
(
        SELECT  [ChildId]
                ,[MidParentAId] as 'MidParentId'
                ,1 as 'IsMidParentA'
        FROM Child c
        WHERE c.MidParentAId IS NOT NULL
   UNION
        SELECT [ChildId]
               ,[MidParentBId] as 'MidParentId'
               ,0 as 'IsMidParentA'
        FROM Child c
        WHERE c.MidParentBId IS NOT NULL
) AS c
ON c.MidParentId = tpl.MidParentId  AND c.IsMidParentA = tpl.IsMidParentA

This eliminates the table scanning that was happening, as I have matched the top level record to its midlevel parent up front if it exists, and stamped it on that record.

I have also done the same with the child record meaning I can then just join the child record to the top level record on the MidParentId, and I use the IsMidParentA bit flag to differentiate where there are two identical MidParentIds (ie an Id of 1 for IsMidParentA and IsMidParentB).

Thanks to all who took the time to answer.

left join with OR clause

Try like this,

Select * from Table1 as t1  
LEFT JOIN Table2 as t2 on t1.checkcode = t2.checkcode1 OR t1.checkcode = t2.checkcode2

SQL - left join with OR operator (MySQL)

EDIT

Not really sure what do you need

• can you show some expected result
• can you tell us what you mean by "kills it, in terms of performance" (does it go to 20sec of execution time ?)

I don't believe its more efficient but try it.

select 
    *
from
    tableA as a
    left join tableB as b1
        on a.id=b1.id1 
        and b1.col2='Red'
    left join tableB as b2
        on a.id=b2.id2 
        and b2.col2='Blue'
where 
    (b1.id1 is not null or b2.id2 is not null)
    or (b1.id1 is null and b2.id2 is null)

You have to manage the result in the SELECT with CASE WHEN...

You can compare the performance and put indexes on appropriated columns (depends on what you have in full table and query but here it should be id, id1 and col2)

LEFT JOIN using OR

Seems you want a French description if it exists, otherwise fallback to English.

SELECT  item.id,
        COALESCE(
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'fr'
        ),
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'en'
        )
        ) AS description
FROM    item i

, or this:

SELECT  item.id,
        COALESCE(lfr.data, len.data)
FROM    item i
LEFT JOIN
        language lfr
ON      lfr.item = i.id
        AND lfr.language = 'fr'
LEFT JOIN
        language len
ON      len.item = i.id
        AND len.language = 'en'

The first query is more efficient if the probability of finding French description is high (it will not evaluate the second subquery if the first one succeeds).

In SQL Server, Oracle and PostgreSQL this one will probably more efficient if you have lots of French descriptions:

SELECT  item.id,
        COALESCE(
        lfr.data,
        (
        SELECT  lang.data
        FROM    language l
        WHERE   l.item = i.id
                AND l.language = 'en'
        )
        ) AS description
FROM    item i
LEFT JOIN
        language lfr
ON      lfr.item = i.id
        AND lfr.language = 'fr'

This query will use an efficient method (HASH JOIN or MERGE JOIN) to join the French descriptions, and will fallback to English one only if necessary.

For MySQL, the 1st and the 3rd queries make no difference.

In all systems, create a composite index on language (item, language)

T-SQL Left Join using or operator

When you use a field from the VW_MBR table in the where clause, it effectively turns the left join into an inner join. Put the condition in the join:

...
FROM #CHECK1 CH1
    LEFT JOIN AVRIL.DBO.VW_MBR AVM ON (CH1.HICN = AVM.HIC OR CH1.MEMBER_ID = AVM.MBR_ID)
      AND AVM.LOADDATE=(SELECT MAX(LOADDATE) FROM AVRIL.DBO.VW_MBR)
WHERE CH1.HEALTH_PLAN = 'AVRIL' 

Left Join with OR operator precedence

As a rule, OR does not short-circuit. It CAN but it is not guaranteed to.

Basically the query optimizer looks at the conditions, and determines the fastest way to execute taking into account the statistics available. If it looks like condition 2 will be quicker to test than condition 1 (say for instance the first condition will require a table scan but the second one can use an index seek then the optimizer is likely to check the second condition FIRST.

It may also run them concurrently if that looks quicker, then do a join behind the scenes and eliminate duplicates.

Converting deprecated outer join operators in SQL server, *= to left join

Answering my own question - after finding an old environment - thank you Gordon for the suggestion -

SELECT GivenName, Surname, OrderNumber
FROM Customers, SalesOrders
WHERE Customers.ID *= SalesOrders.CustomerID
and SalesOrders.OrderNumber = 1000 

is correct and equivalent with

SELECT GivenName, Surname, OrderNumber
FROM Customers LEFT JOIN SalesOrders
ON Customers.ID = SalesOrders.CustomerID 
and SalesOrders.OrderNumber = 1000

T-SQL Left Join with Like operator

I actually figured out my own solution.

There were two things:
1.) First I was using the LIKE on my LEFT JOIN incorrectly. In laymans terms, I was saying "Is 'Communication' inside of 'unica'?" Instead I wanted the opposite, so swapping my join criteria resolved issues 2 and 3.

2.) Once I made that change, there were duplicates now based on how many the search term applying to many tags. While I do my best to never use DISTINCT because it usually means that you've written your query wrong, there was no way around it this time. So adding DISTINCT got me back to displaying 100 rows as intended, resolving issue 1.

To sum up, my LEFT JOIN conditions were reversed and I needed to add DISTINCT to get my query to work.

I hope this helps someone else out there!

Select DISTINCT --#adding Distinct is change #2
    Top100Searches.SearchTerm, Top100Searches.SearchPhraseCount,CASE WHEN TagDim.name IS NOT NULL THEN 'Yes' ELSE 'No' END AS 'Is Tag?'
From
    Top100Searches
LEFT JOIN
    (
        Select TagDim.name
        From tag_dimension TagDim
    ) as TagDim on
    --This is change #1
    TagDim.name LIKE '%' + Top100Searches.SearchTerm + '%'
Order by
    Top100Searches.SearchPhraseCount DESC

---

Related Topics